Import bank TRX's

1

I’m using a test account to import banktrx’s as I always get an error message.
I use a csv-file (Excell) tot test:

Date Payee Description Amount
20160101 user 1 activiteit 1 € 100,00
20170305 user 2 activiteit 2 € -200,00
20180408 user 3 activiteit 3 € 50,00
20180409 user 4 activiteit 4 € 50,00
20180410 user 5 activiteit 5 € 50,00

I have cheked everything but the message is always:
CSV file is missing “Date” column

Colum 1 is set as a date
Colum 2 & 3 as text
Colum 4 as financial amount in Euro’s
Setting Manager is also Euro’s

Any Idea what the cause of the problem is?

afbeelding

2

You may have the column set as a date, but your input in not in a date format. The format must match your date preference as set in Manager.

If you have any option besides CSV, use it. Not only do CSV files cause the most import problems, they are going to be removed as an option in Manager in the future.

3

Unfortunately, I have tried all the date setting there exist and the same fault occurs all the time. I tried every combination. I don’t have any other format available.

4

It isn’t clear which date setting you are referring to, in Manager, your operating system, or the spreadsheet. The import has to match Manager, so the manipulation must occur in the spreadsheet.

5

Sorry, it was indeed not clear. But the following was set:

Date format Manager is dd-mm-jjjj which also was set in Excel.

The amount I us it as an value without a curacy indicator as CSV does not support this.
afbeelding

6

The date in your csv file is 20160101 which is not in the format expected by Manager 01-01-2016

7

Your CSV data shows either YYYYMMDD or YYYYDDMM (I cannot tell which.) Also, no punctuation marks.

8

Below I show you the 2 different settings in Excel based upon Manager setting dd-mm-jjjj:

Manager:
afbeelding

Excel case 1:
afbeelding
Excel case 2:
afbeelding
I’m using Office 216 under win 7/64
afbeelding
Notwithstanding this all, which I believe is correct, the same error shows up:

CSV file is missing "Date" column.

By the way, My Dutch banks provide only PDF or CSV. All the others have been deletet. So, if CSV is not availbale anymore in the future, it will be a problem to use Manager.

9

You need to examine the contents of the CSV file using Notepad or another text editor to see the format of the date as stored in the CSV file.

The format displayed in the EXCEL spreadsheet is EXCEL’s interpretation of the data stored in the CSV and not necessarily the format saved in the CSV file.

10

If I open the Excel CSV file with wordpad, I see:
afbeelding
I do not see any problem

11

Indeed - I’ll leave this to those better placed to explain why this is not an acceptable CSV file as it looks fine to me, but then I don’t import bank files so I’ve little or no experience in this area

12

Anyway, thanks for your cooperation

13

Use your text editor to replace the semicolons with commas in the CSV file. See if that fixes the problem.

14

No, no difference.
It looks I have to do it line by line by hand

15

@G.Hermans is your test.csv file an actual bank downloaded file or is it a self created test file.
An actual bank download may have data/characters that aren’t in a self creation.

16

I don’t know the export formats of the Rabobank but i know for sure that the Regiobank and Knab-bank exports files that work in Manager (and they are cheaper than the Rabobank :wink:)

17

@G.Hermans
Nearly all Dutch banks offer the possibility to export the banktransactions in the csv format. I know by experience (ING, Rabo and SNS) that the actual contents of these files differ. E.g. date-format and the number-format and some show debit and credit with a minus sign and others show it in different fields. Only for business accounts Dutch banks offer possibilities to export the transactions in the MT940 format.
For the Rabobank, I have an Excel-sheet where you can import their csv-file and convert it into a format which makes it possible to export that as a qif-file which you can import in Manager.

You could send me by private mail a copy of the Rabo csv-file and I could test it for you.

Kind regards,

Hennie.

18

Thank you Hennie,

I was writing the info below of which you can take the heading and the 1ste line to test.

Ok, I made progress but it is not correct yet.

I have deleted all. Took the latest version of Manager. Setup new test company, imported file from the bank (csv file). Checked the file by a text editor. I renamed in the 1ste line (heading) the appropriate column names in English names for import and uploaded. That worked so far that the “Date” column was read!, hence no Excel involved.

Another error code came on. Date format was not correct. Changed it but manager is coming back that the date is not correct.: “Met de parameters voor jaar, maand en dag wordt een niet weer te geven DateTime beschreven”.( With the Year, Month and Day parameters the DateTime is not correctly described).

The CSV file shows jjjj-mm-dd, setting Manager too.

Example:

Header (Bold is what I translated)

"IBAN/BBAN","Munt","BIC","Volgnr"," Date ","Rentedatum"," Amount ","Saldo na trn","Tegenrekening IBAN/BBAN"," Payee "," Reference ","Naam initiërende partij","BIC tegenpartij","Code","Batch ID","Transactiereferentie","Machtigingskenmerk","Incassant ID","Betalingskenmerk"," Description ","Omschrijving-2","Omschrijving-3","Reden retour","Oorspr bedrag","Oorspr munt","Koers"

1st line: (private info deleted in this line)

"xxxxxx","EUR","RABONL2U","000000000000004499","2017-12-31","2017-12-31","xxxxx","xxxxxx","","ESSO EXPRESS ORANJELAA DORDRECHT","","","","ba","","","","","","Betaalautomaat 22:50 pasnr. 034"," ","","","","",""

Notwithstanding the same date format in the CSV file and Manager it seems to be that Manager expects another setting which is not in the file.

I use ING and RABO (ING I have not tested yet). Rabo has changed the possibility to download different formats and support only PDF or CSV, ING too.

I’m curious what Hennie’s solution brings.

19

@G. Hermans
I have a Rabo-account myself and I will test it myself one of the upcoming days and let you know the results.

20

Thanks I look forwards to it.