CSV Import Bug
Options
Unknown
Member
I'm trying to import transactions from a relatively small third party application to Quicken. The best method I've found so far is CSV.
However, the CSV format in Quicken for Mac 2016 appears to have no documentation. It mentions being a "Mint.com CSV" but I do not have a Mint.com account to determine what this looks like, so I've been guessing.
In the course of guessing, I believe I've discovered a bug in the latest version of Quicken for Mac 2016, v3.5.2 (Build 35.13202.100): when importing a CSV, if the year date is given as two digits instead of four (e.g. 1/1/12 instead of 1/1/2012), Quicken will consider this value to be the year 12, as in 0012. Curiously enough, clicking the date to change it reveals a dialog that implies Quicken thinks it is 2012, however, running a report on transactions in 2012 will reveal nothing.
However, the CSV format in Quicken for Mac 2016 appears to have no documentation. It mentions being a "Mint.com CSV" but I do not have a Mint.com account to determine what this looks like, so I've been guessing.
In the course of guessing, I believe I've discovered a bug in the latest version of Quicken for Mac 2016, v3.5.2 (Build 35.13202.100): when importing a CSV, if the year date is given as two digits instead of four (e.g. 1/1/12 instead of 1/1/2012), Quicken will consider this value to be the year 12, as in 0012. Curiously enough, clicking the date to change it reveals a dialog that implies Quicken thinks it is 2012, however, running a report on transactions in 2012 will reveal nothing.
1
Comments
-
By the way, for anyone wondering what the CSV format looks like, here's what I've figured out so far:
Date,Payee,FI Payee,Amount,Memo?,Category
e.g.
10/01/2010,Target,POS PURCHASE(TARGET CITY),-19.07,MEMO?,Household Supplies1 -
Thanks for mapping that out-few people realize that CSV doesn't have a defined structure.
Importing via CSV only came about a few years ago to support the Mint crowd, and I see few questions about it. But i do recall some year issues and the might be good info. Perhaps he users might have to do some batch changing in Excel to resolve.0 -
Why are you working so hard?
Mint is free. Create an account, setup one banking account, and download the CSV file. And then you don't have to guess at the columns.
But here I have an account that I use just for this kind of testing.
0 -
Note the hardest part about Mint data is the Transaction Type column. As in you have to change the sign of the Amount based on if it says credit or debit. (Well actually change the sign if it says debit, credit is of course already positive.)0
-
Thank you both! You're right to point out a throwaway Mint.com account is a good option.
Your information backs up what I've found since my first post. Here's the (updated) CSV structure as best I can tell:
Date (format 10/01/2010),Payee,FI Payee,Amount,Unknown/Not Used,Category,Reference,Tags,Notes
The only field I cannot seem to get working/find is a check # field. Do check numbers appear in the mint.com CSV exports?0 -
You know I never thought about it before since I use checks so rarely.
I know from the CSV file that it didn't export, but I remembered I wrote a check in one of my accounts recently so I checked if there is anything in Mint that holds the check number.
And the answer is no. Not in a column or even in the details for that transaction.
BTW I said that the Transaction Type was hard to handle because it changes the sign of the debit transactions, but based on your post I now think Quicken Mac might have made it easy for you.
As in it will accept a negative or positive value, and won't change the sign as long as you don't put debit in the Transaction Type column.
I guess also it allows you to import one account at a time, and therefore not have to deal with having the Account column filled in.
0 -
BTW If you are using Quicken Mac you already have a Mint.com user name. The Intuit Id can be used in Quicken, Mint, TurboTax, QuickBooks, and the Quicken online portfolio. And I'm probably missing some places it can be used.0
-
The problem is you can't import any information into Mint, so you are limited to available bank data (something like 60-90 days max). I need to import a lot of old transactions from an Excel or Numbers file, and am having the darndest time. Thanks for the map. It helped me get started. I ended up using a brute force method.QPW said:Why are you working so hard?
Mint is free. Create an account, setup one banking account, and download the CSV file. And then you don't have to guess at the columns.
But here I have an account that I use just for this kind of testing.0 -
When I try importing a CSV, Quicken ONLY lets me import:cthielen said:By the way, for anyone wondering what the CSV format looks like, here's what I've figured out so far:
Date,Payee,FI Payee,Amount,Memo?,Category
e.g.
10/01/2010,Target,POS PURCHASE(TARGET CITY),-19.07,MEMO?,Household Supplies
Date, Payee, Category, Attachment, Amount, and Balance. And it adds all sorts of unrelated data to the Categories list of you mess up. FYI.0 -
CSV2CSV formats your CSV file to follow CSV Mint layout.This is my website <a href="https://www.propersoft.net/">https://www.propersoft.net/</a>;0
-
This bug still exists in Quicken 2018 for Mac Premier.
0 -
Here is an example mint.csv file that will import into Quicken Mac 2018.
Date,Description,Original Description,Amount,Transaction Type,Category,Account Name,Labels,Notes
1/23/2015,Bookstore Invoice,Bookstore Invoice,12.34,debit,Office Supplies,,,
1/23/2015,Gas Company,Gas Company,60.00,debit,Utilities,,,August bill
1/23/2015,Hydro,Hydro,234.83,debit,Utilities,,,
1/24/2015,Microsoft,Microsoft,1220.00,credit,Payroll,,,Payroll4 -
@John Morrow I've followed your helpful format -- thanks! I thought that "Transaction Type" was the equivalent of "Action", but it looks like it just serves to indicate whether the amount is a positive (credit) or negative (debit) number. I tested this with an authentic Mint.com file, and obtained the same results.
0
This discussion has been closed.