Why isn’t my Import Online Banking Data CSV loading??
Posting this question so I can post a helpful solution I found.
In general the Import Online Banking Data process is very strict and unforgiving as of 2019.1. There are changes coming in 2019.2 so perhaps they’ve made improvements. (let’s hope!)
The basics are covered in SuiteAnswers ID 70182
In a situation with one of my clients, they were using a known good import template with all of the correct column headers, etc. But for some reason it was failing. This import module does not provide helpful messages on the fail just:
We all know how frustrating it can be to go through that process! See my posted answer for the culprit. (spoiler alert – commas and quotes!)
Here’s what the problem was, easy to solve once you know!
- Your banking data is likely to have an occasional comma in the memo/description
- In general you should just adjust your process to ensure that commas are removed from the online banking data you wish to import. Obviously not the commas that delimit the columns in the CSV, but in your spreadsheet before saving as CSV.
- I had specified a process to use “Find and Replace” to change the “memo commas” to spaces, but in this case the person forgot. So maybe a safer way is to build a formula driven spreadsheet that automatically replaces the commas.
- Here’s what happened when the comma was not replaced:
- When they saved the sheet as CSV, Google Sheets or Excel will notice that the memo has a comma in it and so wraps the entire memo in double quotes so that the comma is not treated as a column delimiter.
- This is an accepted standard practice for the CSV file format. And notice when you open the CSV back again in Sheets or Excel then the columns work fine and the memo still has a comma in it.
- However it appears that the NS Online Banking Data import does not support values being wrapped in quotes.
- See the screenshot of the text editor view of a CSV.
- I tried to upload the CSV after just removing the comma (step 1) and it failed
- When I also removed the double quotes (step 2) and tried again, the upload succeeded.
So in summary:
- Make sure your data does not have any commas before you save as CSV.
- If a file that you expect to work is continuing to fail, open it with a text editor and look for single or double quotes. Those could also be your problem.
Please let me know if this helped you out!