Home Quicken for Windows Product Ideas - Quicken for Windows Manage Accounts and Transactions (Windows)

importing csv

I have been using Quicken for more years than I care to admit... probably since it first came out... and I am just astonished at how unfriendly it can be sometimes. I just want to import an excel or csv document into a checking account and it is so difficult. I ended up having to pay for a 3rd party software to make it happen. It's silly. Build it into Quicken and stop making thing like this difficult.
5
5 votes

Not Planned · Last Updated

Comments

  • Well...there's a whole bunch of problems with what you propose.

    1 There is no security to importing a .csv file or .qif file.

    2 Quicken charges banks to download it's proprietary .qfx format file. .csv is a standardized format that everyone can use.

    3 Quicken terminates downloads and support about every three years for the previous year versions...and now every one year, two years or 27 months for the new Quicken subscription (2018).

    If Quicken accepted .csv files to import, they wouldn't be able to get people to upgrade when downloading transactions is terminated.  

    However, feel free to find other software that may (or may not) suit your needs better...and you may find will import .csv files. 
  • mshigginsmshiggins SuperUser ✭✭✭✭✭
    edited November 2019
    From C. D. Bales:

    "I ended up having to pay for a 3rd party software to make it happen. It's silly."

    There is nothing silly about it.

    "Build it into Quicken ...."

    This is not going to happen for a very good reason: .CSV files do not have a format that Quicken can be expected to recognize. CSV file have NO specific format.

    Quicken has to know, for example, where the Transaction Date is in the file it imports: there are no .CSV formatting rules that even require a "Transaction Date" to be present in the .CSV file ... much less specify where that data is in the .CSV records.

    The reason you need pay-to-use third party software is because your financial institution is too cheap to provide even the most inexpensive file format for downloading transactions to Quicken (that format being .QIF); so some other party must examine the .CSV file format supplied by your specific financial institution - then (knowing what Quicken expects), create a file with a format which Quicken can deal with.

    It's time to get familiar with how Quicken works, and why.

    You should also consider looking for financial institutions that are more Quicken friendly than one that only offers .CSV downloads for their transactions.
    Quicken user since Q1999. Currently using QW2017.
    Questions? Check out the  Quicken Windows FAQ list
  • ioana neo inkioana neo ink Member
    edited November 2019

    From C. D. Bales:

    "I ended up having to pay for a 3rd party software to make it happen. It's silly."

    There is nothing silly about it.

    "Build it into Quicken ...."

    This is not going to happen for a very good reason: .CSV files do not have a format that Quicken can be expected to recognize. CSV file have NO specific format.

    Quicken has to know, for example, where the Transaction Date is in the file it imports: there are no .CSV formatting rules that even require a "Transaction Date" to be present in the .CSV file ... much less specify where that data is in the .CSV records.

    The reason you need pay-to-use third party software is because your financial institution is too cheap to provide even the most inexpensive file format for downloading transactions to Quicken (that format being .QIF); so some other party must examine the .CSV file format supplied by your specific financial institution - then (knowing what Quicken expects), create a file with a format which Quicken can deal with.

    It's time to get familiar with how Quicken works, and why.

    You should also consider looking for financial institutions that are more Quicken friendly than one that only offers .CSV downloads for their transactions.

    That's ridiculous. CSV format is standard. You're bamboozling your customers. Thanks but no thanks. 
  • NotACPANotACPA SuperUser ✭✭✭✭✭
    edited November 2019

    From C. D. Bales:

    "I ended up having to pay for a 3rd party software to make it happen. It's silly."

    There is nothing silly about it.

    "Build it into Quicken ...."

    This is not going to happen for a very good reason: .CSV files do not have a format that Quicken can be expected to recognize. CSV file have NO specific format.

    Quicken has to know, for example, where the Transaction Date is in the file it imports: there are no .CSV formatting rules that even require a "Transaction Date" to be present in the .CSV file ... much less specify where that data is in the .CSV records.

    The reason you need pay-to-use third party software is because your financial institution is too cheap to provide even the most inexpensive file format for downloading transactions to Quicken (that format being .QIF); so some other party must examine the .CSV file format supplied by your specific financial institution - then (knowing what Quicken expects), create a file with a format which Quicken can deal with.

    It's time to get familiar with how Quicken works, and why.

    You should also consider looking for financial institutions that are more Quicken friendly than one that only offers .CSV downloads for their transactions.

    CSV is NOT a standard layout.  The fields can be in any order, with any column header and in any date/decimal format.

    CSV simply means "Comma Separated Values" ... NOT a specific layout.
    Q user since DOS version 5
    Now running Quicken Windows Subscription,  Home & Business
    Retired "Certified Information Systems Auditor" & Bank Audit VP
  • GeoffGGeoffG SuperUser ✭✭✭✭✭
    edited November 2019

    From C. D. Bales:

    "I ended up having to pay for a 3rd party software to make it happen. It's silly."

    There is nothing silly about it.

    "Build it into Quicken ...."

    This is not going to happen for a very good reason: .CSV files do not have a format that Quicken can be expected to recognize. CSV file have NO specific format.

    Quicken has to know, for example, where the Transaction Date is in the file it imports: there are no .CSV formatting rules that even require a "Transaction Date" to be present in the .CSV file ... much less specify where that data is in the .CSV records.

    The reason you need pay-to-use third party software is because your financial institution is too cheap to provide even the most inexpensive file format for downloading transactions to Quicken (that format being .QIF); so some other party must examine the .CSV file format supplied by your specific financial institution - then (knowing what Quicken expects), create a file with a format which Quicken can deal with.

    It's time to get familiar with how Quicken works, and why.

    You should also consider looking for financial institutions that are more Quicken friendly than one that only offers .CSV downloads for their transactions.

    May be standard to spreadsheet importing, but not to financial/banking transactions.
    user since '92 | Quicken Windows Premier - Subscription | Windows 10 Pro version 2004
  • Tom YoungTom Young SuperUser ✭✭✭✭✭
    edited November 2019

    From C. D. Bales:

    "I ended up having to pay for a 3rd party software to make it happen. It's silly."

    There is nothing silly about it.

    "Build it into Quicken ...."

    This is not going to happen for a very good reason: .CSV files do not have a format that Quicken can be expected to recognize. CSV file have NO specific format.

    Quicken has to know, for example, where the Transaction Date is in the file it imports: there are no .CSV formatting rules that even require a "Transaction Date" to be present in the .CSV file ... much less specify where that data is in the .CSV records.

    The reason you need pay-to-use third party software is because your financial institution is too cheap to provide even the most inexpensive file format for downloading transactions to Quicken (that format being .QIF); so some other party must examine the .CSV file format supplied by your specific financial institution - then (knowing what Quicken expects), create a file with a format which Quicken can deal with.

    It's time to get familiar with how Quicken works, and why.

    You should also consider looking for financial institutions that are more Quicken friendly than one that only offers .CSV downloads for their transactions.

    No, the only "standard" in a CSV file is that "data" is separated by commas.  What each data element represents is a complete mystery, and can be anything and everything.

    A human being looking at a string of unidentified data elements, each separated by a comma, might make a guess as to what the CSV file it trying to "say", but a computer can't do that; it needs input that is rigorously defined so that it knows what each data element "is" and where it goes in a data file it's maintaining. 

    Here's a CSV file:

    12/19/2018, 352.769, 89.154, ABC, "AR"

    How do you put that Into Quicken?

    The reason OFX files work as well as they do is that every data element is identified and each element has a specific format that must be followed.  That allows a dumb program like Quicken to do nothing more than take information out of a "bucket" from the import file and put it in a specific "bucket" in it's data base.  Something that's easily programmed.
  • NotACPANotACPA SuperUser ✭✭✭✭✭
    edited November 2019

    From C. D. Bales:

    "I ended up having to pay for a 3rd party software to make it happen. It's silly."

    There is nothing silly about it.

    "Build it into Quicken ...."

    This is not going to happen for a very good reason: .CSV files do not have a format that Quicken can be expected to recognize. CSV file have NO specific format.

    Quicken has to know, for example, where the Transaction Date is in the file it imports: there are no .CSV formatting rules that even require a "Transaction Date" to be present in the .CSV file ... much less specify where that data is in the .CSV records.

    The reason you need pay-to-use third party software is because your financial institution is too cheap to provide even the most inexpensive file format for downloading transactions to Quicken (that format being .QIF); so some other party must examine the .CSV file format supplied by your specific financial institution - then (knowing what Quicken expects), create a file with a format which Quicken can deal with.

    It's time to get familiar with how Quicken works, and why.

    You should also consider looking for financial institutions that are more Quicken friendly than one that only offers .CSV downloads for their transactions.

    BUT, even with spreadsheet importing, the layout is  not standardized. 

    Is the Title/Honorific in the 1st column, or is the membership number?  Does the lastname come before or after the first name(s)?  How are dual memberships of married persons handled?  How are dates formatted (US fashion, or European fashion)?  Are dates represented in MM/DD/YY, or DD/MM/YY,  or YY/MM/DD.  OR do any of those dates use 4 digit years.

    There is simply NO standardization of CSV files.  They're pretty much freeform.
    Q user since DOS version 5
    Now running Quicken Windows Subscription,  Home & Business
    Retired "Certified Information Systems Auditor" & Bank Audit VP
  • ioana neo inkioana neo ink Member
    edited November 2019

    From C. D. Bales:

    "I ended up having to pay for a 3rd party software to make it happen. It's silly."

    There is nothing silly about it.

    "Build it into Quicken ...."

    This is not going to happen for a very good reason: .CSV files do not have a format that Quicken can be expected to recognize. CSV file have NO specific format.

    Quicken has to know, for example, where the Transaction Date is in the file it imports: there are no .CSV formatting rules that even require a "Transaction Date" to be present in the .CSV file ... much less specify where that data is in the .CSV records.

    The reason you need pay-to-use third party software is because your financial institution is too cheap to provide even the most inexpensive file format for downloading transactions to Quicken (that format being .QIF); so some other party must examine the .CSV file format supplied by your specific financial institution - then (knowing what Quicken expects), create a file with a format which Quicken can deal with.

    It's time to get familiar with how Quicken works, and why.

    You should also consider looking for financial institutions that are more Quicken friendly than one that only offers .CSV downloads for their transactions.

    Yes, as you noted (all of you) you can have predefined CSV formats, date formats, etc . 

    That's why it's possible to have a converter. Here is one, online: 

    https://csvconverter.biz/ 

    There are other ones, written in perl, written in python, etc. 

    This converter could, presumably, be incorporated into quicken itself. 
  • Just returned to Quicken this year. Bought Quicken Premier. I was looking forward to getting my accounts in order. Then ran into not being able to import a csv file with the very first account I tried to create. How frustrating.
    Quicken could open source the QFX file format and I'd be able to write my own conversion routine. This isn't rocket science. Or support a generic import schema in some standard file format (csv, xml, pick one).
    I never thought I'd encounter a company still employing proprietary formats to hold customers data hostage in 2019. 
  • splashersplasher SuperUser ✭✭✭✭✭
    Jim_666 said:
    Just returned to Quicken this year. Bought Quicken Premier. I was looking forward to getting my accounts in order. Then ran into not being able to import a csv file with the very first account I tried to create. How frustrating.
    Quicken could open source the QFX file format and I'd be able to write my own conversion routine. This isn't rocket science. Or support a generic import schema in some standard file format (csv, xml, pick one).
    I never thought I'd encounter a company still employing proprietary formats to hold customers data hostage in 2019. 
    Nothing is keeping you from converting the CSV to a QIF and import it.

    -splasher  using Q since 1996 -  Subscription  -  Win10
    -also older versions as needed for testing
    -Questions? Check out the  Quicken Windows FAQ list

  • Looks like an option. Thanks for the pointer!
  • mshigginsmshiggins SuperUser ✭✭✭✭✭
    From C. D. Bales:
    @Jim_666 said:

    "Quicken could open source the QFX file format and I'd be able to write my own conversion routine."

    That comment makes it clear that you do not know what you are talking about, and are not here to provide any help to the original poster.


    "This isn't rocket science."

    No it certainly is not: but the QFX format is most definitely "economic science". The QFX format exists because Quicken wants to be paid for supporting downloads - which they deserve, since it costs them to provide such service.


    "I never thought I'd encounter a company still employing proprietary formats to hold customers data hostage in 2019".

    And you are not encountering one here.

    QFX is not a "proprietary format" (see below); and you're not a "hostage" (you can leave anytime and take your data with you, to many other personal financial software products). Try sticking to the facts.


    "A proprietary format is a file format of a company, organization, or individual that contains data that is ordered and stored according to a particular encoding-scheme, designed by the company or organization to be SECRET." [Emphasis added]

    There is nothing SECRET about the QFX format; which is proven by the fact that there is software out there (software not owned or controlled by Intuit or Quicken) that will convert non-QFX data to QFX data. 

    In fact, QFX data is 100% compatible with the OFX specs; which can be easily proven by importing a QFX file (with its Windows extension changed to .OFX) into MS Money or any other product that "only" imports .OFX data.


    Quicken user since Q1999. Currently using QW2017.
    Questions? Check out the  Quicken Windows FAQ list
  • Could you tell me if Quicken publishes the QFX schema?
  • NotACPANotACPA SuperUser ✭✭✭✭✭
    The OFX schema is public information.  Google it. Then read here about what the differences are.
    As already stated, MS Money, and other products, can read the QFX files without issue.
    Q user since DOS version 5
    Now running Quicken Windows Subscription,  Home & Business
    Retired "Certified Information Systems Auditor" & Bank Audit VP
  • I seem to have gotten off on the wrong foot here.
    I simply want to import my Ally Demand Notes into Quicken.
    Ally provides a CSV export.
    This Quicken page states it can import either a WebConnect file or QIF file.
    CSV and OFX are not supported:
    https://www.quicken.com/support/how-do-i-import-data-quicken-windows
    This page states the WebConnect feature uses the QFX format which is the standard OFX format with additional Quicken fields:
    https://en.wikipedia.org/wiki/QFX_(file_format)
    So it looks like I need either a CSV -> QFX converter or a CSV -> QIF converter.

  • splashersplasher SuperUser ✭✭✭✭✭
    If the financial institution that the account is associated with is NOT a participating Quicken financial partner for your OS (some support Win and not Mac), Quicken will not import a QFX file because it checks on the validity of that relationship at every import.
    QIF file imports are not so restricted.  Edit QIF -manually
    -splasher  using Q since 1996 -  Subscription  -  Win10
    -also older versions as needed for testing
    -Questions? Check out the  Quicken Windows FAQ list

  • Thanks. I'll focus on the conversion from CSV to QIF.
  • SherlockSherlock SuperUser ✭✭✭✭✭
    Jim_666 said:
    Thanks. I'll focus on the conversion from CSV to QIF.
    If you haven't already, you may want to consider: http://www.quicknperlwiz.com
    Quicken user since 1997
    Premier on Windows 10
  • Thank you. 
  • Jim_666Jim_666 Member
    edited March 2019
    [previous comment was removed]
    Here's the correct instructions and script. Tested and verified on Excel 2016 Professional Plus.
    ............
    Here's an Excel Power Query script you can use to convert an Ally Demand Notes csv export into a QIF file for import.
    The Ally Demand Notes csv format is:
    <Date>,<CheckNum>,<Description>,<Withdrawal Amount>,<Deposit Amount>,<Additional Info>
    The technique demonstrated can be modified to accommodate csv exports with additional/different fields.
    The script merges all the csv files from a dedicated folder of your choice and translates them into one QIF-formatted Excel Table.

    Starting with Excel 2010 Power Query exists as an addin. Search for "download excel power query", download from Microsoft, and install.
    As of Excel 2016 Power Query was integrated into the product but rebranded as "Get & Tranform". It's available on the Data|Get & Transform ribbon.

    How To:
    - Add your csv files to a dedicated folder.
    - Open a New Query | From Folder 
    - Folder dialog: Browse to and select your dedicated csv folder | OK
    - You should see a dialog listing the files in your folder
    - Select Combine | Combine & Edit 
    - You're now faced with the Combine Files dialog that previews the import. If the preview appears correct then click OK. Otherwise, adjust the options until the preview is correct then click OK
    - Now you're in the Power Query Editor. A number of support queries have been created for you including a function: "Transform File from Input". This is what sets up the rest of the query for us. I suspect there may be a more direct way to generate these support functions - let me know if there is.
    - Go to Query | Advanced Editor
    - Select all the lines then paste in the script
    - Modify the Source line portion of "C:\YourPath\FolderNameOfCSVFiles" of the script with YOUR dedicated csv folder path and name.
    - Click Done. The last step in the APPLIED STEPS window should be selected and you should see your data in qif format in the data window. 
    - Rename the query if you like then click Close and Load. 
    - You should now have a new worksheet with your csv data in the QIF format in a Table. 
    - With this worksheet selected save the file as an MS-DOS text file, change the extension to qif, and import into your account.

    Here's the script to paste (between the === lines)
    =================
    let
        Source = Folder.Files("C:\YourPath\FolderNameOfCSVFiles"),
        Filter_csv = Table.SelectRows(Source, each true),
        #"Removed Other Columns" = Table.SelectColumns(Filter_csv,{"Content"}),
        #"Filtered Hidden Files" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
        #"Transform Files" = Table.AddColumn(#"Filtered Hidden Files", "Transform File from Input", each #"Transform File from Input"([Content])),
        #"Removed Other Columns1" = Table.SelectColumns(#"Transform Files", {"Transform File from Input"}),
        #"Expanded Table Column" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Input", Table.ColumnNames(#"Transform File from Input"(#"Sample File"))),
        #"Changed Type Text" = Table.TransformColumnTypes(#"Expanded Table Column",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
        Filter_nonBlankRows = Table.SelectRows(#"Changed Type Text", each [Column1] <> null and [Column1] <> ""),
        #"Promoted Headers" = Table.PromoteHeaders(Filter_nonBlankRows, [PromoteAllScalars=true]),
        Filter_Headers = Table.SelectRows(#"Promoted Headers", each not Text.StartsWith([#"<Date>"], "<")),
        Sort_Date_Asc = Table.Sort(Filter_Headers,{{"<Date>", Order.Ascending}}),
        Create_Transaction = Table.CombineColumns(Table.TransformColumnTypes(Sort_Date_Asc, {{"<Withdrawal Amount>", type text}, {"<Deposit Amount>", type text}}, "en-US"),{"<Withdrawal Amount>", "<Deposit Amount>"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Transaction"),
        Create_Date = Table.AddColumn(Create_Transaction, "Date", each "D"&[#"<Date>"]),
        Replace_blank_null = Table.ReplaceValue(Create_Date,"",null,Replacer.ReplaceValue,{"<CheckNum>", "<Additional Info>"}),
        Create_Check = Table.AddColumn(Replace_blank_null, "Check", each "N"&[#"<CheckNum>"]),
        Created_Category = Table.AddColumn(Create_Check, "Category", each "L"&[#"<Description>"]),
        Create_Amount = Table.AddColumn(Created_Category, "Amount", each "T"&[Transaction]),
        Create_Memo = Table.AddColumn(Create_Amount, "Memo", each "M"&[#"<Additional Info>"]),
        #"Removed Other Columns2" = Table.SelectColumns(Create_Memo,{"Date", "Check", "Category", "Amount", "Memo"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns2",{"Date", "Category", "Amount", "Memo", "Check"}),
        Created_EOT = Table.AddColumn(#"Reordered Columns", "EOT", each "^"),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(Created_EOT, {}, "Attribute", "Value"),
        #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
        RenameHeaderToQHeaderType = Table.RenameColumns(#"Removed Columns",{{"Value", "!Type:Bank"}})
    in
        RenameHeaderToQHeaderType

    ================
  • dougdoug Member
    I understand that Quicken makes money from banks for the privilege of supplying QFX file downloads. I am still using the last version of standalone quicken software. At some point I will be required to go to the subscription model. It will be significantly more expensive as I only upgraded to continue the banking downloads.

    Under that subscription model, I would expect a higher service level. I have several specialty credit cards. These are cards issued by retailers, such as Brooks Brothers and BJ’s. Typically the banks behind these cards, even Citibank, don’t support quicken downloads. I think that if we are paying an annual subscription, quicken needs to think about imports of other formats. They could address the security and provide for the user to be able to map fields.
  • splashersplasher SuperUser ✭✭✭✭✭
    doug said:
    … These are cards issued by retailers, such as Brooks Brothers and BJ’s. …
    Quicken already supports BJs, it is listed under "My BJs" and Brooks Brothers listed just that way.  Both are Express Web Connect because their financial institutions do not want to support Quicken via Direct Connect or Web Connect, so I believe both of your cited CC are already dealt with.
    -splasher  using Q since 1996 -  Subscription  -  Win10
    -also older versions as needed for testing
    -Questions? Check out the  Quicken Windows FAQ list

  • bboylesbboyles Member
    I'm paying a subscription for Quicken and it is ridiculous that Quicken does not support a csv or any other delimited file format for importing transactions. I was using Banktivity, but switched to Quicken because of the lower subscription fee, but then found that Quicken does not download from my Canadian investment bank (a large bank by Canadian standards), and does not support a standard download format (csv) through a simple mapping mechanism.
    I guess thousands of customers can write Power Query M solutions, manually convert to QIF files, or just take their business elsewhere, or...
    Quicken could be a customer-focused organization and fix this problem.

    I have a finance background and at work we use CSV files with our ERP system to download and upload data - it is very common and the ERP system simply provides a mapping table for the upload files.

    Personally, I use Quicken on a Mac - I have it set up for Windows as well, so I could boot in Windows, download my files, run Power Query M (not available in Mac excel). save the files, reboot as a mac and upload the files.
    Or I could manually create a QIF file from CSV or may be able to use Automator to do this.
    I can likely figure something out, but why should I have to - this is an easy fix for Quicken - hopefully I will see this in one of the updates that are always available as part of the subscription service. But if I get tired of doing the work that should be done by the service provider, I may switch to another service.
  • bboylesbboyles Member
    > "A proprietary format is a file format of a company, organization, or individual that contains data that is ordered and stored according to a particular encoding-scheme, designed by the company or organization to be SECRET." [Emphasis added]
    >
    > There is nothing SECRET about the QFX format; which is proven by the fact that there is software out there (software not owned or controlled by Intuit or Quicken) that will convert non-QFX data to QFX data. 
    >
    > In fact, QFX data is 100% compatible with the OFX specs; which can be easily proven by importing a QFX file (with its Windows extension changed to .OFX) into MS Money or any other product that "only" imports .OFX data.

    The definition of proprietary quoted above (as SECRET) is taken from Wikipedia - later in the Wikipedia article it states:
    A proprietary format can also be a file format whose encoding is in fact published, but is restricted through licences such that only the company itself or licencees may use it. Since the QFX format is used by Banks, with a licensing fee, this second definition is obviously the one that is applicable to QFX.

    The point is that it is not a standard format that is interchangeable between a diverse range of financial software products and Jim_666 is correct in referring to it as proprietary, with all of the backward protectionist thinking that the word connotes. If you don't believe this, and Wikipedia is deemed to be a reputable source vis-a-vis its use in defining proprietary, see the Wikipedia definition of QFX.

    So the real truth is that Quicken, doesn't support the CSV file format because they want licensing money from the banks. It is not because it can't be done - users are doing the conversion themselves.
Sign In or Register to comment.