Mac version: The provided CSV header was invalid (for security update)

J-C Bailly
J-C Bailly Member ✭✭
edited February 2023 in Investing (Mac)
"File selected did not have the required format"
I am trying to update my securities with historical data that are downloaded into a "number" format and then converted into a CSV file. However I am getting these messages above. What exactly is expected by Quicken so that it will accept a security history file please?
The provided data include three columns: Date (Y.M.D), Close, Volume.
Note that these securities are listed on European markets (Germany, Switzerland) and therefore I cannot import it from the US markets where the download is automatic.
Thank you.

Best Answer

  • jacobs
    jacobs SuperUser, Mac Beta Beta
    edited January 2023 Answer ✓
    With the semicolons and your dates in day-month-year format, it looks like your Mac isn’t set to the US region. Have you tried changing all semicolons to commas in a text editor?

    EDITING TO ADD: I tried it with semi-colons, and it failed. I changed back to commas, and it worked.

    (In TextEdit, select Edit > Find > Find & Replace. In the top magnifying glass field, enter a semi-colon; in the field below it, enter a comma. Then press Replace All, then Done.)
    Quicken Mac Subscription • Quicken user since 1993

Answers

  • NotACPA
    NotACPA SuperUser ✭✭✭✭✭
    HELP in you Mac should be able to provide the necessary format and info for the CSV input of security values.
    But, off the top of my head I can see that your 3 columns don't include any security ID.

    Q user since February, 1990. DOS Version 4
    Now running Quicken Windows Subscription, Business & Personal
    Retired "Certified Information Systems Auditor" & Bank Audit VP

  • jacobs
    jacobs SuperUser, Mac Beta Beta
    edited December 2022
    Go to Window > Securities and select a security.
    Click on the Price History tab
    Click "Import History from CSV file"
    Make sure your file is set up in the format Quicken expects:



    Date and Close are required; the other fields are optional. It sounds like you may be missing the header line.
    Quicken Mac Subscription • Quicken user since 1993
  • J-C Bailly
    J-C Bailly Member ✭✭
    Thanks for your help gentlemen, but unfortunately no solution in sight. I redownloaded the CSV file for one security, made sure the name of the security was there, obviously I followed strictly what Quicken requires, but no joy, I end up with the same message again and again.
    Any assistance would be welcome.
    Happy New Year to all!!!
  • jacobs
    jacobs SuperUser, Mac Beta Beta
    The name of the security should not be in your CSV file; only the fields mentioned above.

    I created a test security and a test .csv file, and it successfully imported my test file as long as I had the correct column headers:


    Quicken Mac Subscription • Quicken user since 1993
  • J-C Bailly
    J-C Bailly Member ✭✭
    Many thanks Jacobs for your feedback especially today!
    I made sure that my CSV file was strictly similar to yours, added empty columns (open, high, low) just in case, respecting the order and the formatting, but again no joy. I get the same message.
    I tried to make the CSV file with Numbers first, it did not work, and then tried with OpenOffice, no luck.
    I really cannot think of anything that could prevent me from succeeding, but I still do not understand.
  • jacobs
    jacobs SuperUser, Mac Beta Beta
    Here's the plain text of the .csv file I generated by Exporting the .numbers file I showed above. Take a look in TextEdit and compare it to yours to see if you can notice what's different. 

    Date,Open,High,Low,Close,Volume,,,,
    12/01/2022,,,,10.5,,,,,
    12/02/2022,,,,10.7,,,,,
    12/03/2022,,,,10.9,,,,,

    Quicken Mac Subscription • Quicken user since 1993
  • J-C Bailly
    J-C Bailly Member ✭✭
    edited January 2023
    Thank you for your help. Here is what I get on TextEdit:

    Date;Open;High;Low;Close;Volume
    30/12/2022;;;;57.06;357702
    29/12/2022;;;;57.68;373311
    28/12/2022;;;;57.14;369756
    27/12/2022;;;;57.22;389742

    Where you have colons, I have semicolons, this is the only difference I can see. I tried to convert the CSV file in different Unicodes, to no avail, I am still getting semicolons. So I guess I won't be able to use this Quicken feature as I can't figure how to convert those files in a format that Quicken can use.
    I want to sincerely thank you for your kind and speedy assistance.
  • jacobs
    jacobs SuperUser, Mac Beta Beta
    edited January 2023 Answer ✓
    With the semicolons and your dates in day-month-year format, it looks like your Mac isn’t set to the US region. Have you tried changing all semicolons to commas in a text editor?

    EDITING TO ADD: I tried it with semi-colons, and it failed. I changed back to commas, and it worked.

    (In TextEdit, select Edit > Find > Find & Replace. In the top magnifying glass field, enter a semi-colon; in the field below it, enter a comma. Then press Replace All, then Done.)
    Quicken Mac Subscription • Quicken user since 1993
  • J-C Bailly
    J-C Bailly Member ✭✭
    Jacobs, I am extremely grateful for your time and your help which, finally, allowed me to download my CSV files into Quicken Mac.

    I had to make a number of "manipulations" like changing temporarily my settings to USA, convert the semicolons into commas in TextEdit, remove security name, realign columns, change the dates format in Numbers, reconvert into CSV.

    BUT, in the end all is well now and I have the solution to my problem thanks to your kind assistance during this festive season. My best wishes to you for this new year!
This discussion has been closed.