csv import

bidavies's Avatar

bidavies

13 Apr, 2018 07:43 PM

Hi,

I need some help importing csv files, the only file type my bank supports. If I go to File-Import.... I can select a csv file and import it, but many of the transaction are wrong.

I've manually installed the csv importer so if I go to Extensions - Import File I get a dialogue box and can make set preferences to import the csv file as this post discusses:

http://help.infinitekind.com/discussions/problems/36581-unable-to-import-csv-file-into-moneydance

However, I'm not sure how to what the File Encoding is for the csv file I'm importing so when I go to Maintain Custome Fil Readers and select UTF-8 and set up the column headers and save, then Preview Import, the screen is blank. Nothing happens when I click on Process.

Could someone tell me how I can import csv files in a way that does not produce transaction errors?

I've attached an example csv file.

Thanks for any help

  1. 1 Posted by -Kevin N. on 13 Apr, 2018 09:50 PM

    -Kevin N.'s Avatar

    Hi bidavies,

    Thank you for providing your CSV file.

    Take a look at the attached screenshot and let me know if it looks OK to you.

    If not, let me know what you would need to have changed.

    We can then go ahead and set up the CSV Importer for you.

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

  2. 2 Posted by bidavies on 14 Apr, 2018 09:48 AM

    bidavies's Avatar

    Hi Kevin,

    thanks for the response. I'd like the date format to be dd/mm/yyyy. It seems like some of the transactions have not imported. See below for the highlighted transactions that are not present in your screen shot.
    Regards,
    Ben

    05/04/2018 text PURCHASE 32.4
    04/04/2018 text TRANSFER 72.49
    04/04/2018 text PURCHASE 256.02
    04/04/2018 text PURCHASE 179.58
    03/04/2018 text TRANSFER 42.17

  3. 3 Posted by -Kevin N. on 14 Apr, 2018 02:31 PM

    -Kevin N.'s Avatar

    Hi Ben,

    The Date format is set by the user in 'Preferences' under Moneydance's 'File' menu. You can disregard the Date format that I use in my screenshots. The imported data will display in your register as per the Date format that you choose in Preferences.

    The missing transactions are actually there but they are not visible in the screenshot due to the number of transactions included in the import. If you look closely at my screenshot you can see the visible scroll bar on the right side of the register.

    Set up the custom reader per my screenshot.

    For the 'Reader Name' you can, of course, name the reader whatever is informative to you. I chose 'bidavies bank'. Enter the desired name and click the 'Add' button. The name that you just entered should now be listed in the 'List of Readers' section.

    Leave the 'Filename Matcher' field as is.
    Continue by making sure to enter the proper Date Format: dd/MM/yyyy
    Number of Header Lines: 1
    Number of Footer Lines: 0
    CSV Field Separator: ,
    File Encoding: UTF-8
    Decimal Sign: .
    Set Fields 1 ~ 5 as indicated in the screenshot.

    When you've completed setting up the custom reader, click 'Save' and 'Done'.

    (Back at the main screen of the CSV Importer)

    Click the [...] button to navigate to your CSV file.

    When it loads, make sure that the proper File Reader is selected (for when you have more than one Custom Reader)

    Make sure that the proper Account is selected to import the file into.

    For the 'Import Transaction As' option, I used the 'Regular' option. It has been my experience that the 'Regular' option imports Categories, the 'Online' option does not.

    Look things over, make sure everything is correct.

    Click, the 'Preview Import' button.

    A preview window will open. Provided there are no 'red' or 'yellow' flagged txns listed in the preview, you can go ahead and click the 'Process' button.

    The first time that you run the CSV Importer, there may be some informational message that pops up. I forget exactly what it reads but you should look that over. You'll then receive a message indicating the number of imported items as well as other information relevant to the imported txn(s).

    That should do it. Let me know how it worked out OK?

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

  4. 4 Posted by bidavies on 14 Apr, 2018 07:14 PM

    bidavies's Avatar

    Hi Kevin,
    thanks for the clarification.

    I've entered the settings as you described. The Preview box is showing errors and in yellow, the blank cells in the first three columns. These continue if I scroll down. See the screen shot below. How should I adjust the settings so that the importer ignores the blank cells?

    Ben

  5. 5 Posted by bidavies on 14 Apr, 2018 07:17 PM

    bidavies's Avatar

    see below....

  6. 6 Posted by -Kevin N. on 14 Apr, 2018 08:06 PM

    -Kevin N.'s Avatar

    Hi Ben,

    Thank you for the screenshots.

    I failed to mention that your original sample.csv file has a large number of empty rows. I had assumed that you deliberately cleared them for the purposes of posting the file to the forums.

    If you open your sample.csv file in a simple word processor, you will see these empty rows at the bottom of the data indicated by four commas , , , ,

    Using the word processor, I deleted all of the instances of four commas (or empty rows) before importing the file.

    See if you can import the attached sample_Edited.csv file.

    I'm still not sure why the Date column is shown in yellow on your screenshot but let's see if that clears up with the edited csv file.

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

  7. 7 Posted by bidavies on 15 Apr, 2018 03:12 PM

    bidavies's Avatar

    Hi Kevin,

    I got that to work, once. Thanks.

    Then I adjusted the 'maintain custom file readers' menu to change 'category name' to 'account name' as I wanted to keep the 'category name' column free so that I could categorise transactions myself. But then then the importer skipped the transactions, as if it detected the exact same transactions in the account, even though I had deleted them.

    I tried deleting the account and opening a new one.
    Deleting the account and opening a new one and setting the start date to the date of the first transaction.
    Changing 'account name' back to 'category name'.
    Download another csv file from bank and importing that: The preview worked showing no errors, but I got an error message on processing (see MD screen 5)

    None of these things worked.

    Any ideas?

  8. 8 Posted by bidavies on 15 Apr, 2018 03:14 PM

    bidavies's Avatar

    see below

  9. 9 Posted by bidavies on 15 Apr, 2018 03:15 PM

    bidavies's Avatar
  10. 10 Posted by bidavies on 15 Apr, 2018 03:17 PM

    bidavies's Avatar

    see below

  11. 11 Posted by -Kevin N. on 15 Apr, 2018 05:03 PM

    -Kevin N.'s Avatar

    Hi Ben,

    Changing the Reader option from 'category name' to 'account name' is probably not a good thing to do. I'm not even sure of what the 'account name' option is for, since the account to import into is selected in the main window of the CSV Importer.

    If you don't want the category field of the CSV file to import into Moneydance, then simply set the field selector in the reader from 'category name' to 'ignore'.

    If you're still having trouble with the new csv file from your bank, post a copy of it. As these are public forums, make sure that there is no sensitive information in the file before posting it.

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

  12. 12 Posted by bidavies on 15 Apr, 2018 05:26 PM

    bidavies's Avatar

    Kevin,

    thanks for the advice. I wanted to include the data in category name:
    ATM, Purchase etc. etc.

    but have it in a column other than 'category name'. Do you have any suggestions of which column to specify?

  13. 13 Posted by -Kevin N. on 15 Apr, 2018 05:37 PM

    -Kevin N.'s Avatar

    Hi Ben,

    Would putting that data (ATM, Purchases, etc.) in the Memo field work for you?

    If so, let me know and I can set up the reader for you to do that.

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

  14. 14 Posted by -Kevin N. on 15 Apr, 2018 05:53 PM

    -Kevin N.'s Avatar

    Hi Ben,

    I should've said... If so, just change the 'category name' field to 'memo'.

    Doing so will leave the Category field empty in Moneydance and place the 'ATM, Purchases, etc.' data into the Memo field in Moneydance.

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

  15. 15 Posted by bidavies on 18 Apr, 2018 11:13 AM

    bidavies's Avatar

    Hi Kevin,
    Thanks for that.
    I've tried narrowing down the problem in the new csv. I did an import of five transacitons without the below problem transactions and got a clean import with no errors.

    I think the problem lies in these transactions. See lines 2 and 8 (notepad++). The description column on these lines has an entry with a comma in it that is messing with the CSV import. Any ideas of how to deal with these going forward, without resorting to manual editing?

    Regards,

    Ben

  16. 16 Posted by bidavies on 18 Apr, 2018 11:14 AM

    bidavies's Avatar

    see below

  17. 17 Posted by -Kevin N. on 18 Apr, 2018 01:45 PM

    -Kevin N.'s Avatar

    Hi Ben,

    This is a serious flaw in the formatting of your bank's csv file. Normally, whenever there is a comma inserted between text strings in a csv file, the entire string is enclosed in double quotes. For example:
    2018-03-27,text,"text,PURCHASE",,24.99,64.44
    2018-03-26,text,"text,PURCHASE",,6.37,-2.54

    Doing so, instructs the importer to ignore the comma as a delimiter.

    Since they have a flawed format, you're going to have to manually manipulate the data in these new csv files prior to importing them. You'll need to either delete the included comma, change it to a dash (or some other character) or enclose the entire string in double quotes.

    This new csv file also requires that you create a new custom reader wherein the Date format is yyyy-MM-dd and you should also add a 6th field set to 'Ignore' so as to ignore the 'balance' column of the csv file. (see screenshot)

    I've included an edited version of your new sample18c.csv file wherein I enclosed the errant string in double quotes for you to try.

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

  18. 18 Posted by stantowianski on 05 May, 2018 06:38 PM

    stantowianski's Avatar

    Hi guys,
    Yes, you tell importer which column to put your data in to. So if the data is a "category" you can just tell it to put that data in to "memo", etc...

    do ignore columns you do not want.

    a comma with no " xx , yy" around it is wrong. how would it know it is not a delimiter? bank is giving bad file.

    for "account name", that will actually override the account the transaction goes to!
    You can if you really want create a column for account name and import into multiple acounts if you want. You probably will not use this.

    if you always have extra lines at the bottom you can set footer lines to be skipped like header.

    get newest mdcsvimporter-v22 so it saves "Can Be Blank" ok.

    Stan Towianski

Reply to this discussion

Internal reply

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

Attaching KB article:

»

Already uploaded files

  • sample.csv 3.68 KB

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

Recent Discussions

20 May, 2018 03:52 AM
20 May, 2018 01:58 AM
20 May, 2018 01:13 AM
20 May, 2018 12:45 AM
20 May, 2018 12:15 AM