I have read lots of posts on here about CSV import problems and quite a bit on other web sites but can't find a solution to the error message "Line: xxx One of the following fields: Date, Amount, Type is missing, skipping" .
I got a CSV file from a bank, as it does not do QIF, which came with the following headings, "Date","Transaction type","Description","Paid out","Paid in","Balance" and I selected these fields in this order in MMEX, Date, Payee, Category, Withdrawal, Deposit and Balance" when I got the error above I tried changing the headings in the CSV file to, "Date","Payee","Category","Withdrawal","Deposit","Balance" to match MMEX but no difference.
Date format seems OK, 22/11/2015 in CSV and DD/MM/YYYY selected in importer.
I wondered if it was columns like 'number' in the MMEX account display that were causing the problem so added 'don't care' to match them in the importer but then get a 'missing tokens' error.
Every entry in the CSV file is enclosed in quotes (") could that be the problem?
CSV import
Moderator: Renato
-
- Developer
- Posts: 1535
- Joined: Sat Dec 06, 2008 2:27 pm
- Are you a spam bot?: No
- Location: Sankt-Petersburg, Russia
Re: CSV import
Please provide 1 line of the csv file.
-
- New User
- Posts: 13
- Joined: Fri Feb 01, 2013 10:30 am
- Are you a spam bot?: No
Re: CSV import
"15 Feb 2016","Direct debit xxxxxx","xxxxxx","£xx.00","","£xxxx.xx" - edited
-
- Developer
- Posts: 1535
- Joined: Sat Dec 06, 2008 2:27 pm
- Are you a spam bot?: No
- Location: Sankt-Petersburg, Russia
Re: CSV import
Mmex can't parse this date format and amounts with currency symbol.71MGB wrote:"15 Feb 2016","Direct debit xxxxxx","xxxxxx","£xx.00","","£xxxx.xx" - edited
-
- New User
- Posts: 3
- Joined: Sun Aug 14, 2016 4:18 am
- Are you a spam bot?: No
Re: CSV import
I've also run into this problem. In my case, the rows that are being excluded are the ones with ( ) - negative values or Zeros.
My bank provides just one column for amount with parentheses for negative values. The $ is present but that does not seem to create issues
Sample: (only the first was imported)
Date,Transaction Type,Check Number,Description,Amount,Daily Posted Balance
06/25/2015,Check,0,TELLER CASHED CHECK,$****.00,$***
11/06/2015,Debit,,TRANSFER TO SAVINGS *********6684 ,($1000.00),$***.**
12/21/2015,Debit,,NOTICE-BOX RENT TO DRAFT 02/16 $30.00 SAFE DEPOSIT BOX FEE,$0.00,$******
I was importing just the date, description and amount fields (which contained negative values)
Running: Windows 10,
MMEX - 1.2.7
My bank provides just one column for amount with parentheses for negative values. The $ is present but that does not seem to create issues
Sample: (only the first was imported)
Date,Transaction Type,Check Number,Description,Amount,Daily Posted Balance
06/25/2015,Check,0,TELLER CASHED CHECK,$****.00,$***
11/06/2015,Debit,,TRANSFER TO SAVINGS *********6684 ,($1000.00),$***.**
12/21/2015,Debit,,NOTICE-BOX RENT TO DRAFT 02/16 $30.00 SAFE DEPOSIT BOX FEE,$0.00,$******
I was importing just the date, description and amount fields (which contained negative values)
Running: Windows 10,
MMEX - 1.2.7
-
- New User
- Posts: 3
- Joined: Sun Aug 14, 2016 4:18 am
- Are you a spam bot?: No
Re: CSV import
My bank exports to CSV format.
The problem occurs when the negative amount is enclosed in parentheses. It skips the record. When replaced with minus sign the import works as expected
The $ sign in the amount is not creating any problems.
Sample Data:
Imports OK... data edited to replace ( ) with -
Date,Transaction Type,Check Number,Description,Amount,Daily Posted Balance
06/25/2015,Check,0,TELLER CASHED CHECK,$****.00,$****
06/30/2015,Debit,,ACCOUNT NUMBER xxxxx PREAUTHORIZED TRANSFER,-$75.00,$***
Fails ($0 amount fails too)
06/30/2015,Debit,,ACCOUNT NUMBER xxxxx PREAUTHORIZED TRANSFER,($75.00),$***
12/21/2015,Debit,,NOTICE-BOX RENT TO DRAFT 02/16 SAFE DEPOSIT BOX FEE,$0.00,$******
The problem occurs when the negative amount is enclosed in parentheses. It skips the record. When replaced with minus sign the import works as expected
The $ sign in the amount is not creating any problems.
Sample Data:
Imports OK... data edited to replace ( ) with -
Date,Transaction Type,Check Number,Description,Amount,Daily Posted Balance
06/25/2015,Check,0,TELLER CASHED CHECK,$****.00,$****
06/30/2015,Debit,,ACCOUNT NUMBER xxxxx PREAUTHORIZED TRANSFER,-$75.00,$***
Fails ($0 amount fails too)
06/30/2015,Debit,,ACCOUNT NUMBER xxxxx PREAUTHORIZED TRANSFER,($75.00),$***
12/21/2015,Debit,,NOTICE-BOX RENT TO DRAFT 02/16 SAFE DEPOSIT BOX FEE,$0.00,$******
-
- New User
- Posts: 3
- Joined: Sun Aug 14, 2016 4:18 am
- Are you a spam bot?: No
Re: CSV import
From my experience, the CSV import fails under two conditions:
- Headers have spaces and are not enclosed in quotes (I had Transaction type, Check number, Daily Posted balance). Fixed when I got rid of the spaces
- Amount had parentheses for negative numbers. Fixed when I replaced those with the negative sign. Although the $ sign was present, it did not affect the import
-
- Senior User
- Posts: 23
- Joined: Mon Mar 06, 2017 12:53 am
- Are you a spam bot?: No
Re: CSV import
cdate postedDate amount description recurringPayment originalCurrencyAmount conversionRate type currency merchant.name merchant.town merchant.postcode
2018-07-13T00:00:00Z 2018-07-13T21:37:39Z 200 PAYMENT RECEIVED -- THANK YOU FALSE 200 1 Current GBP PAYMENT RECEIVED -- THANK YOU
First 2 lines of the csv file imported into libre office calc
Same 2 lines opened with textpad
"date","postedDate","amount","description","recurringPayment","originalCurrencyAmount","conversionRate","type","currency","merchant.name","merchant.town","merchant.postcode"
"2018-07-13T00:00:00Z","2018-07-13T21:37:39Z",200,"PAYMENT RECEIVED -- THANK YOU",false,200,1,"Current","GBP","PAYMENT RECEIVED -- THANK","YOU",
I have tried different combinations of date formats, even going to the extent of stripping the time format out of the date and re-saving. I have even tried stripping out the " with still no success.
I did date,Don't Care,Amount, description and the rest don't care, but no matter which framework I choose nothing happens, I just get the same old message
Line: xOne of the following fields: Date, Amount, Type is missing, skipping
insert all line numbers for x
2018-07-13T00:00:00Z 2018-07-13T21:37:39Z 200 PAYMENT RECEIVED -- THANK YOU FALSE 200 1 Current GBP PAYMENT RECEIVED -- THANK YOU
First 2 lines of the csv file imported into libre office calc
Same 2 lines opened with textpad
"date","postedDate","amount","description","recurringPayment","originalCurrencyAmount","conversionRate","type","currency","merchant.name","merchant.town","merchant.postcode"
"2018-07-13T00:00:00Z","2018-07-13T21:37:39Z",200,"PAYMENT RECEIVED -- THANK YOU",false,200,1,"Current","GBP","PAYMENT RECEIVED -- THANK","YOU",
I have tried different combinations of date formats, even going to the extent of stripping the time format out of the date and re-saving. I have even tried stripping out the " with still no success.
I did date,Don't Care,Amount, description and the rest don't care, but no matter which framework I choose nothing happens, I just get the same old message
Line: xOne of the following fields: Date, Amount, Type is missing, skipping
insert all line numbers for x