Import Security Prices - cannot select csv

mark's Avatar

mark

23 Sep, 2020 01:30 PM

I'm using the latest version of Money Dance on a Mac running Mojave. I have a csv file ready to load by selecting Import From File in the 'Import Price History' dialog box. When I click import the file selector dialog box has all files greyed out including csv files. Please advise what file extension is required to import security prices.

  1. 1 Posted by -Kevin N. on 23 Sep, 2020 01:47 PM

    -Kevin N.'s Avatar

    Hi Mark,

    I'm not a Mac user but this should apply regardless...

    Make sure that you have the proper permissions to access the folder housing the csv files. Try locating the csv files on your Desktop to see if this resolves the issue.

    -Kevin N. (not a member of MD support)

  2. 2 Posted by mark on 23 Sep, 2020 01:56 PM

    mark's Avatar

    Thanks Kevin. Turns out it was a weird bug which went away after I restarted MoneyDance.

    However, I'm having a problem importing the file now having selected it. I have two columns, with the headings Date and Close. I am using the date format DD/MM/YYYY which is the same as MoneyDance is setup to use. Example below:

    Date,Close
    22/09/2020,72.17
    21/09/2020,71.19
    18/09/2020,72.63

    MoneyDance gives the error: No Valid Data found in file. It does this regardless of where the file is loaded from.

  3. 3 Posted by -Kevin N. on 23 Sep, 2020 02:22 PM

    -Kevin N.'s Avatar

    Hi Mark,

    Try opening the csv file in a simple word processor. (not a spreadsheet)

    Check to see if maybe the Date is formatted other than DD/MM/YYYY. Also, make sure that forward slashes vs. periods as separators are consistent with Moneydance settings.

    -Kevin N. (not a member of MD support)

  4. 4 Posted by mark on 23 Sep, 2020 02:38 PM

    mark's Avatar

    Turns out loading into a text editor showed nothing untoward, but in response to your reply I checked some more using the command line. The command 'more vwrl.csv' showed there was a special character at the beginning U<+FEFF>. Really surprising! Here's the explanation, I'm using Excel on the Mac and Excel loves messing with the first byte of CSV files:

    https://www.freecodecamp.org/news/a-quick-tale-about-feff-the-invisible-character-cd25cd4630e7/

    This UTF character is used to flag byte order in the file. Would be great if MoneyDance could ignore it.

    I'm shaking my head at how Microsoft have managed to break such a simple file type... I've managed to import the file now.

    Thanks for your help!

  5. 5 Posted by -Kevin N. on 23 Sep, 2020 03:05 PM

    -Kevin N.'s Avatar

    Hi Mark,

    Thanks for passing along your findings. I'll have to keep that one in mind.

    I've seem some strange behavior before when importing csv files. One odd resolution that comes to mind is simply entering a dummy line in the first data row. e.g. 00/00/0000, 00.00 coinciding with Date, Amount. Doing so allowed the file to be imported into Moneydance. That was going to be my next suggestion. : )

    -Kevin N. (not a member of MD support)

  6. 6 Posted by mark on 23 Sep, 2020 10:27 PM

    mark's Avatar

    One final observation. In Excel for Mac Office 365 there are now TWO csv options when doing a 'save as'.

    1. Under 'Common Formats' there is CSV UTF-8 (Comma-delimited) (.csv)
    2. Under 'Speciality Formats' there is Comma-separated Values (.csv)

    The first is the type that has the bad character that Moneydance can't process. The second is what the whole world considers a 'common' .csv file to be, except Microsoft it seems - saving using this 'speciality' option gives a standard CSV file that Moneydance can read.

  7. 7 Posted by -Kevin N. on 23 Sep, 2020 11:31 PM

    -Kevin N.'s Avatar

    Hi Mark,

    Thanks for the additional information.
    I was weaned on Windows so this might be a Mac-Excel-specific issue. I haven't seen this particular issue in Excel for Windows but then again I'm still using Excel 2000. Microsoft may have 'improved' upon Excel to where it may now have such an issue. :\

    -Kevin N. (not a member of MD support)

Reply to this discussion

Internal reply

Formatting help / Preview (switch to plain text) No formatting (switch to Markdown)

Attaching KB article:

»

Attached Files

You can attach files up to 10MB

If you don't have an account yet, we need to confirm you're human and not a machine trying to post spam.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac