.csv files seem difficult to import in Manager. What's up?

1

Importing bank statement (.csv file). will be difficult.

Even manual input in manager is NOT available (WHY NOT).
Importing financial data (in .csv format) from a regular bank account in Manager will result in the well known most known…
Error messages:

  1. The document “TESTFORMAT copy.csv” could not be opened. Manager cannot open files in the “comma-separated values” format.
    and:
  2. The file you are trying to import is invalid. What a…&#?!!#

I have complied with the manager program limitations and remarks:

But still it is not done yet!
Why is this program so critical and picky. This program chokes on a comma between the lines (which is generally applied). I have a program (Money-pro) and it has no problems at all. Time for a smart programmer to tinker with this. Maybe it can be fixed after a next update?

It starts with struggling after downloading a common bank (RABO) .csv file and then to transform it and try to import it in MANAGER.
and following the attentions and limitations MANAGER told me to do:

delete other columns and rename 5 columns in EXEL:

Date
Reference
Payee
Discription
Amount

Use all in US format such as decimal point instead of a komma.
Date will in the format: dd/mm/yy
and I have followed the next recommandations via diverse topics:

Quote:
"possible solution:

Hope you got it all sorted. I haven’t had any trouble downloading CSV file to Excel but in early days I had to get help with the date & column headings so that I could import. No trouble now - each time I download CSV to Excel I then put headings across the columns: DATE- PAYEE- DESCRIPTION- REFERENCE- AMOUNT (making sure the info is actually in description column which is what Manager uses for bank rules), highlight the Date column and change to dd/mm/yyyy and delete the last column which is the running bank balance column and not needed.

of

At the risk of repeating what you’ve read elsewhere in the Guides or on the forum, consider the following:
• If your bank offers any option for exporting statements besides Excel or .csv format, use it.
• You said you had the file in a spreadsheet. Be aware that while most spreadsheets will open and display .csv files, you are not looking at a .csv file, but at a file in the format of the spreadsheet program. You actually need a .csv file for your import to Manager.
• Be sure you have included column headings and that they match those listed in the Guide exactly.
• Eliminate all commas in the data. The .csv format interprets commas as delimiters.
• Search the forum for solutions other users have found to their troubles with .csv files.
• Try making sure everything is purely text.
• Understand that there is no standard for .csv bank files. Banks put whatever they want in the various fields. So there really is no way to troubleshoot except by trial and error.

Tips
1.1) system settings should be: comma is decimal point. Manager needs decimal point in the currency value, rather than comma (Netherlands). As the comma is the value separator (CSV), during import the intended currency value might otherwise be interpreted as two separate values.
1.2) within Manager, set the date etc settings too to UK or US.
2. don´t forget - as suggested earlier - to have the field names in row one, cell one (A1), separated by comma’s. Too, see 5), last line.
3. A value of € 92,23 (or any European currency, notated with a comma…) should be visible as 92.23 (with a decimal point and too without the currency sign. For that matter, check 1.1 and 1.2: temporarily reset Manager as well as system settings to UK or US and then (re-)start using Excell to compose the data (either by import or entry). Too see Lubos´ response a few messages up w/t to negative amounts (prefix payments with a minus sign in front of the value, e.g. -92,23).
4. consequently, as a result of 1.1 and 1.2 the date format of the imported or pasted input data might be wrong to start with too. Do some Excell magic to get the YYYYMMDD format for the date values. Paste this result ´as values´ into another column. And then delete the columns used for this manipulation.
5. if you were using multiple columns to compose the data, merge them all into a new column (Excell formula:“=A2&”,“&B2&”,“&C2&”,“&D2&”,“&E2). Select and paste the result column into the next column AS VALUES. Move or copy the header values in cell A1 (containing the coma separated field names) into the header of this column. NOTE: empty fields would show up as two commas.
6. save the file as an Excell file (xls or xlsx), as you might need it to recompile things if import into Manager fails.
7. delete all columns preceeding the result of 5) and save the file as CSV file (next to the Excell xls or xlsx version of 6).
8.1) prior to 7), DON´T have hidden or additional rows or columns. 8.2) don´t have multiple worksheets too, only one will work.
9. after saving the Excell as CSV file, check the contents by means of a regular text editor, like Notepad on Windows and TextEdit on Mac. This might show possible irregularities, like wrong date and currency formats. Correct things by means of the xls or xlsx file, generated in step 6). And proces with 8) onwards.
10. Too, count the number of coma´s in any line. This always should be one less the number of fields in the header: 4 (with 5 fields, if I recall correctly). If more commas, than see bullets 1) and 2) and restart by correcting, followed by 6) onwards.
Success & best regards !”

Poeh!

However It is still not possible for me to import my bank data in manager yet.
Import manually or import a certain file format, without much trouble should be no problem for MANAGER? There appear to be a lot of complaints about this, but they don’t do much about it, I think that the programmer(s) of Manager have a job to do. See it; as a challenge to accommodate the user of Manager.

So after this swan song: I am using only the Cash account option to do my business.

Greetings from Ralph

Greetz Ralph :kissing_closed_eyes:

2

It’s available by going to the Receipts and Payments tab and creating a new receipt or payment, or by creating a new inter account transfer from the Inter Accounts Transfers tab. Anything that appears on your bank statement will be one of these three things.

3

If you want more specific help you’ll need to give us more to work with. There are all sorts of things that could be causing your problems, and there’s no way the developer could anticipate every possible form of bad file formatting and build Manager to magically fix it and extract the necessary data in the right structure. No doubt many users of Manager (including myself) successfully import bank statements, but obviously we don’t post on the forum every time we do it.

If you can provide a sample file that a generous forum member can check, perhaps someone will be able to find the cause of your issues. Obviously bank statements contain sensitive information, so perhaps try creating a sample CSV with fictitious data.

Also let us know which version of Manager you are using (desktop, cloud, or server, and version number).

4

@R.M_Unknown Any chance you can look at your copy.cvs file in a text editor (note pad type not word processor). Pasting an example files text contents on the forum would be useful to enable more meaningful help.

5

For what it’s worth, I have also noticed Manager has trouble importing .csv files that I download from banks and financial aggregators.

It will not import them straightaway after download, but I discovered if I open them in Excel, then immediately choose “Save As…”, make no changes to the file name or type, overwrite the existing file when asked, then the “new” file imports flawlessly.

6

Hello everyone,

Thanks for many responses.
However, I have not become a step wiser

Anyway, .csv files from my RABO-bank account will easy import in other programs like for example Money-pro on my macbook-pro running on mac-osx.10.11.6.
Further, Manager v.20.2.83 (1 account usage) runs very well except the .csv input. could be more attention about it by the programmers.
With Manually input, I mean the possibility to do it in the TAB: “Bank Accounts” within there you’re forced to import a .csv-file. If not this function doesn’t work. And of course I mentioned it by myself that if you make an entry in the TAB; Receipts & Payments you can make changes on the amount, but that’s not the intention.
Or make a Cash Account.

I would also like to thank everyone for their contribution. I also see that there is someone else (4punger) who is not very happy too with how Manger handles this item. Above all, if a program does what it is supposed to do, then there is no comment.

Greeting to you all
Ralph

7

The difficulty with addressing your suggestion is that there are no standards for bank statements in CSV format. Banks can output anything they want. Many files supposedly in CSV format are not. They are TSV or Excel formats. And many users fail to understand formatting issues when they view a CSV file with a spreadsheet application. It was suggested in posts #3 and #4 that you provide a sample statement so others could look for problems. You have chosen not to do that, but instead to complain about programming.

No, you are not forced to import a CSV file. You have eight better options specifically designed for financial data exchange. There are so many problems with CSV bank statement imports the developer announced years ago his intent to remove the option entirely. It was only kept because so many people complained that their banks offered nothing else or offered only Excel formats (which can be converted to CSV). Your bank, however, offers several other formats, depending on the type of account you have. You should try them.

You also complain that you cannot make a manual bank transaction entry in the Bank Accounts tab. That is by design, not accident or neglect. Bank and cash transactions used to be recorded in separate Bank Accounts and Cash Accounts tabs as Spend Money or Receive Money transactions. Then they were combined into a single Cash Accounts tab with designations for accounts at a financial institution. For a while, there were separate Bank Transactions and Cash Transactions tabs with Spend Money and Receive Money functions. Then those functions were combined into a single Receipts & Payments tab, where you can enter any kind of monetary movement in either a cash or bank account. The program is not going to move backwards.

1 Like
8

@R.M_Unknown is correct in that it could be improved.
@Tut is probably correct that improving csv import functionality is not high on lubos priorities.

However looking at the problems others have had using csv the majority are

  • Local number format (what is used as the decimal and thousand separator)

  • Local date format

Manager uses CsvHelper which is capable of reading and writing cvs files with a variety of formats.
Manager could be improved by using the Managers number and date setting information to determine how numbers and dates are formated / interpreted.

However I personally can avoid using bank cvs importing as my bank provides other formats which Manager can import. So the program enhancement would be of no direct benefit to me.

9

Here I am again for .csv. Many banks seems to give only this option to import files.

I think that @lubos should create an intermediate screen after the importation which asks you the usual standard thing that also Excel and other programs do:

  1. the format in which the csv is coded, ie the separator (comma, tab etc etc)
  2. the matching between the csv columns and manager fields
  3. a preview of the imported data

and in the end the real importation.

10

TLDR, but I think I got your idea. It’s about date manipulation to correspond to MM/dd/yyyy format that is enforced for bank imports (which doesn’t make sense to me).

The main problem is if you have your system date set as dd/MM/yyyy then your system will read some (e.g. 2/3/2021) as date and flip d and M and others (e.g. 2/28/2021) as text and keep current order which makes the solutions complicated. And the worst part is, it has nothing to do with what you set your preferences.

If the system has to enforce one date format for imports and exports, that format should be ISO (yyyy-MM-dd) because:

  • It’s the default date for servers, databases and systems
  • regardless of set preferences, the OS will never confuse it with any other format, it will either take the whole thing as date or as text unlike what I described earlier, so the solutions is simpler.
  • To import into manager, you just have to change the cell formating of the date column in excel and that’s it.

I already proposed this here
The issue appeared to have been solved and then returned.

If I didn’t get the idea of the OP, please let me know.

11

In order to have a complete discussion, I think that, first of all, we should divide the issue in two differnt parts:

  1. batch operations
  2. import bank statament

About batch operations we have several issues:

  1. when you export the spreadsheet Manager produces dates in format “yyyy/mm/dd” and numbers in the format that you have, in my case “#,00” (but only the system’s ones, not the ones of custom fields which are always “#.00”)
  2. when you export the spreadsheet Manager wants dates in format “yyyy/mm/dd” and numbers in format “#.00”

Under Batch functionalities fortunatelly we have a full preview so that we can see what will happen.

About CSV the discussion is more complex and it applies only to bank statements. Given that many banks don’t use QIF or other standard formats, they export only in CSV or XLS which are not a standard… an excel file can contain anything (headers, empty colums etc etc) and in any format. I personally find out that it is much faster, and less error prone, using batch create to import a CSV rather than starting to manipulate the CSV to be compliant to Manager format, given also that you don’t have a preview. That’s why I’m asking a routine to do this manipulation automatically inside Manager, like it is done under Excel.

12

About dates I complete agree with you as I already stated many times.

@lubos should spend sometime to check around the software those inconsistencies and give, under settings, the possibility to set all these “formats”.

13

This is what worked for me
delete other columns and rename 5 columns in EXEL:

Date
Reference
Payee
Description
Amount

Thanks