Quicken Community is moving to Single Sign On! Starting 1/22/21, you'll sign in to the community with your Quicken ID. For more information: http://bit.ly/CommunitySSO

CSV Import Bug

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.

Comments

  • Unknown
    Unknown Member
    edited December 2016
    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
  • John_in_NC
    John_in_NC SuperUser, Mac Beta Beta
    edited March 2017
    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.
  • Unknown
    Unknown Member
    edited October 2018
    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.

    image
  • Unknown
    Unknown Member
    edited October 2018
    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.)
  • Unknown
    Unknown Member
    edited September 2016
    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?
  • Unknown
    Unknown Member
    edited October 2018
    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.

    image
    image
  • Unknown
    Unknown Member
    edited October 2018
    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.
  • Unknown
    Unknown Member
    edited December 2016
    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.

    image

    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.
  • Unknown
    Unknown Member
    edited December 2016
    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

    When I try importing a CSV, Quicken ONLY lets me import:
    Date, Payee, Category, Attachment, Amount, and Balance. And it adds all sorts of unrelated data to the Categories list of you mess up. FYI.
  • Sergiy Tytarenko
    Sergiy Tytarenko Member ✭✭
    edited May 2017
    CSV2CSV formats your CSV file to follow CSV Mint layout.
  • Unknown
    Unknown Member
    edited November 2017
    This bug still exists in Quicken 2018 for Mac Premier. :(
  • John Morrow
    John Morrow Member ✭✭
    edited March 2018
    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,,,Payroll
  • odysseus
    odysseus Member, Mac Beta Beta
    edited May 2019
    @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.
This discussion has been closed.