Bank statement import *csv wrong value in ver.19.7.34

Hello,

Manager 19.7.34

It seems that there is a problem with the import *csv from bank to Manager

For example:
In csv value is 1234,56
After importing value changed to 123456.

Can someone confirm this issue ?

CVS is the file format of last resort. It is the least standardized and is the most likely to have importing problems. CVS stands for comma separated values yet you are using a coma as a decimal separator. Making that work is not done in a world wide uniform standard. Have you first tried all other formats from your bank?

To have any chance you also need to ensure the separator in you Manager settings is the same as those in the data you are importing. See Set number format | Manager

Import from *.csv to Manager worked fine before update.

Here is csv

Date,Description,Payee,Amount,Reference
29-07-2019,SOME NAME/A/B/06/2019/INFO/wyn,“SOME INFO, bd.”,“-1000,01”,1

Here is preferences

I still think that this is bug in application. Problem with multiplier.

If that is importing into Manager as “-100001” then I guess Manager is assuming the thousand separate is a comma “,” so is ignoring it.

Have you checked an old bank download to ensure your bank hasn’t changed something.
I don’t think your operating system setting effect the import & I assume you haven’t changed them.

Yes, I’ve checked it. No changes found.

Checked on two different computers. It’s not a problem of OS settings.

Yes, I think the same. I’m not sure that I can fix it without @lubos support.

Your number preference for Manager is set with a decimal separator. Your CSV file uses a comma separator. They need to match, as @Patch already told you.

@Tut sorry, but it’s not clear for me. Manager is configured to use comma separator

CSV file uses also a comma separator

In both cases comma is separate decimal part.
Where is my mistake ?

Sorry, your screen shot was so small, the comma looked like a period.

Another possibility to consider is that the file you exported from your bank is not truly a CSV-formatted file. Very likely, your computer is treating it as a spreadsheet file. You should make sure that the cells containing numbers are actually formatted as numbers, not text. Some Manager users have had trouble with this before.

The problem is in this record

“-1000,01”

I don’t know why, but Manager delete coma in numbers with quotes. For example this record

“-1000,01”

interpretate as

“-100001”

But the same number without quotes, but with dot

-1000.01

works fine for me.

The problem is that bank use quotes in their *csv files.
Also I must say that Manager worked fine before update.

It sounds like perhaps a global deletion of quotation marks in the CSV file (with a text editor or spreadsheet feature) would solve your issue.

As was mentioned by @Patch, if you have any options besides CSV for the format, use it.

The problem with the CSV file is indeed the comma. My banks only allow me to download bank records in excel format, so I had to transform tat in a csv (it was the easiest option…) I don’t use it anymore because I changed my process but what I used to do was to write the amount in the CSV with dot “.” to separate decimals and change the configuration in Manager accordingly. As you can imagine this was a nightmare to keep updated the bank records.

So, I finally end up changing my workflow to avoid that.

Your example has different quotes for the number compared to the preceding text, which may cause a problem.

Your number format appears to use a comma as a decimal separator and space for thousand separator (which is not used in the bank csv file). As a debugging test you could try other Manager number formats with a comma decimal separator.

Good observation, @patch. I hadn’t noticed that: “smart quotation marks” versus straight quotation marks.

Are you sure the csv example is exactly as contained in the original file? If you look carefully, you’ll notice that the Payee field is surrounded by smart quotes, while the Amount is surrounded by double quotes. I find it rather odd that different string delimiters are used for each of these fields. If in fact the fields are delimited in this way, the so called csv file is not strictly a csv file.

Sorry, this example is not exactly the same. I hide sensitive information and that’s why I wrongly put other type of quotes.

Here is exactly the same as in bank

I’m not sure Manager’s csv (comma separated value) import routine supports a comma as a decimal separator. The original csv standard only has text fields (without commas) with fields separated by commas. So does not support comas anywhere within numbers.

Various adaptions of “csv” have been made to some form of delimited separated values with some form of escape sequence to deal with values containing the delimiters. None of which is standard, which means no solution will uniformly work for the implementations chosen by different banks world wide.

Because of the lack of standardization in csv implementations (date format, number format, field delimiters, escape and quoting protocol), I believe Lubos is more interested in dropping it completely rather than having an inevitably partially incompatible implementation.

For a one off solution you can

  1. Open the bank statement in a spreadsheet program. This may involve customising the setting in the import graphical module to correctly parse you delimiter separated text file. LibreOffice delimiter separated values importer enables specification of date format and language / number setting.
  2. Format it as required. Within the spreadsheet the NUMBERVALUE() function enables converting text to numbers in a language independent fashion. Similarly the TEXT() converts a number to a language specific text string such as the format “#.##”.
  3. Export it as basic coma separated values (no commas in the numbers and “.” as a decimal separator).

A better solution for ongoing bank import is

  1. use a text processing utility which supports regular expression processing
  2. Write a regular expression to automate the conversion of your particular delimiter separated value file to a basic comma separated file compatible with Manager. This includes identifying number fields, removing the thousands separator (ie all character except digits, “-”, or your decimal separator), then replacing the decimal separator with “.”
  3. Use this tool to pre process your bank downloads prior to importing into Manager

Iff it really worked in earlier versions of Manager

Rerunning a test import in an older version may also help identify this a regression bug.

If it hasn’t worked in older versions, then you are suggesting a program enhancement. Probably to incorporate the RegEx functionality described above when importing numbers.