Thank you very much
that wont work if your amount format uses “,” or "."such in european formats. it will replace those as well.
so no luck here. i turn back to manual input. Had a quite enough each every time with this struggle trying to import
Comma separated values CSV is a loosely defined format which is often generalised to delimiter separated values. CSV in European countries normally has “;” (or tab) as the column separator and “,” as the decimal separator. In most other countries the column separator is “,” and the decimal separator is “.” Ideally this should be recognised by manager.io CSV import routines.
Otherwise use find and replace to
- Remove all thousand separators
- Replace the decimal separator with “.”
- Then replace the column separator with “,”
if you remove all the decimal separator you also remove them from the amounts
and how hard is it to adjust the import app to manage this? and also distinguish the various date formats.
as said, I had quite enough of it. If I have to go to such length to adjust the import files, it is of no use to me
Indeed you need to replace not remove the decimal separator, and do so in the correct sequence as listed above.
At a more general level, the issue is CVS (comma separated values) as originally conceived, does not support a comma within the data field. Clearly a problem if your data typically contains a comma (eg European decimal separator). Various work arounds are used which involve some form of escape sequence to enable special characters within the data field and using different characters as delimiters.
The issue for the program is none of this is standardised so no solution will reliably accommodate all the delimiter separated formats used by all banks (and exported with a “.csv” file extension). Similar issues occur with field definition / labelling, date formatting, extended character sets etc. That is why using any other data format is strongly recommended.
Does your bank provide another format you can export statements in, if so use that. If not then use a text processing utility to automate the character substitutions required to convert your banks statement to comma separated values.
Spreadsheet programs can also be used to leverage off the thousands of hours they have invested in simplifying data import / conversion, and manually editing the data if required. The disadvantage is as you have already identified, it is labour intensive as it requires special manipulation of every bank statement hence the above recommendations.