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.
3
3 votes

No Status · 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 January 27
    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
  • Quicken HaroldQuicken Harold Alumni ✭✭✭✭
    edited April 2018
    Hello stephenayates,

    Are you still experiencing issues?

    Please let us know. If we don’t hear from you within 48 hours, the thread will be closed.

    Respectfully,
    ~ Quicken Harold.
    Quicken Harold
    Community Moderator
  • ioana neo inkioana neo ink Member
    edited December 2018
    mshiggins said:

    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 December 2018
    mshiggins said:

    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 December 2018
    mshiggins said:

    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 1903
  • Tom YoungTom Young SuperUser ✭✭✭✭✭
    edited December 2018
    mshiggins said:

    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 December 2018
    mshiggins said:

    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 January 2
    mshiggins said:

    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. 
  • Jim_666Jim_666 Member
    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 -  QW 2015, 2016, 2017 & Subscription  -  Win10
    -Questions? Check out the  Quicken Windows FAQ list

  • Jim_666Jim_666 Member
    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
  • Jim_666Jim_666 Member
    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
  • Jim_666Jim_666 Member

    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 -  QW 2015, 2016, 2017 & Subscription  -  Win10
    -Questions? Check out the  Quicken Windows FAQ list

  • Jim_666Jim_666 Member
    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 7 
  • Jim_666Jim_666 Member
    Thank you. 
  • Jim_666Jim_666 Member
    edited March 11
    [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

    ================
Sign In or Register to comment.