Page 1 of 1

CSV import

Posted: Sat Mar 12, 2016 9:48 am
by 71MGB
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?

Re: CSV import

Posted: Sat Mar 12, 2016 11:37 am
by Nikolay
Please provide 1 line of the csv file.

Re: CSV import

Posted: Sat Mar 12, 2016 12:11 pm
by 71MGB
"15 Feb 2016","Direct debit xxxxxx","xxxxxx","£xx.00","","£xxxx.xx" - edited

Re: CSV import

Posted: Sat Mar 12, 2016 7:29 pm
by Nikolay
71MGB wrote:"15 Feb 2016","Direct debit xxxxxx","xxxxxx","£xx.00","","£xxxx.xx" - edited
Mmex can't parse this date format and amounts with currency symbol.

Re: CSV import

Posted: Sun Aug 14, 2016 4:35 am
by forumID
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

Re: CSV import

Posted: Sun Aug 14, 2016 4:56 am
by forumID
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,$******

Re: CSV import

Posted: Sat Aug 27, 2016 1:43 am
by forumID
From my experience, the CSV import fails under two conditions:
  1. 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
  2. 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

Re: CSV import

Posted: Tue Jul 17, 2018 1:54 pm
by latedev
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