CSV import

MMEX4Desktop, incl. Windows, macOS (10.10 or newer), and Most Unix variants using the GTK+ toolkit

Moderator: Renato

Post Reply
71MGB
New MMEX User
Posts: 13
Joined: Fri Feb 01, 2013 10:30 am
Are you a spam bot?: No

CSV import

Post 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?
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: CSV import

Post by Nikolay »

Please provide 1 line of the csv file.
71MGB
New MMEX User
Posts: 13
Joined: Fri Feb 01, 2013 10:30 am
Are you a spam bot?: No

Re: CSV import

Post by 71MGB »

"15 Feb 2016","Direct debit xxxxxx","xxxxxx","£xx.00","","£xxxx.xx" - edited
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: CSV import

Post 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.
forumID
New MMEX User
Posts: 3
Joined: Sun Aug 14, 2016 4:18 am
Are you a spam bot?: No

Re: CSV import

Post 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
forumID
New MMEX User
Posts: 3
Joined: Sun Aug 14, 2016 4:18 am
Are you a spam bot?: No

Re: CSV import

Post 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,$******
forumID
New MMEX User
Posts: 3
Joined: Sun Aug 14, 2016 4:18 am
Are you a spam bot?: No

Re: CSV import

Post 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
latedev
Senior MMEX User
Posts: 23
Joined: Mon Mar 06, 2017 12:53 am
Are you a spam bot?: No

Re: CSV import

Post 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
Post Reply