importing csv

Quicken Community · April 2018

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.

Unknown · April 2018

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.

mshiggins · April 2018

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.

Unknown · December 2018

That's ridiculous. CSV format is standard. You're bamboozling your customers. Thanks but no thanks.

NotACPA · December 2018

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.

GeoffG · December 2018

May be standard to spreadsheet importing, but not to financial/banking transactions.

Tom Young · December 2018

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.

NotACPA · December 2018

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.

Unknown · January 2019

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_666 · March 2019

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.

splasher · March 2019

Nothing is keeping you from converting the CSV to a QIF and import it.

Edit QIF -manually

Jim_666 · March 2019

Looks like an option. Thanks for the pointer!

mshiggins · March 2019

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.

Jim_666 · March 2019

Could you tell me if Quicken publishes the QFX schema?

NotACPA · March 2019

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.

Jim_666 · March 2019

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.

splasher · March 2019

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

Jim_666 · March 2019

Thanks. I'll focus on the conversion from CSV to QIF.

Sherlock · March 2019

If you haven't already, you may want to consider: http://www.quicknperlwiz.com

Jim_666 · March 2019

Thank you.

Jim_666 · 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:

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

================

doug11 · December 2019

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.

splasher · December 2019

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.

bboyles · May 2020

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.

bboyles · May 2020

> "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.

WindowFinance · December 2020

> @ said:
> 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.

Thank you so much for this link https://csvconverter.biz/. I was able to convert my Amazon transaction report (CSV file) to QIF using the online tool, which simply renames the CSV columns to QIF keywords. I then import the QIF file into Quicken. For the money we pay, Quicken should have done this simple task instead of forcing us to use another tool.

Every December, I deposit money into my online Amazon gift card account using my Discover Card, which gives 5% rebate on such transactions. During the year, I buy on Amazon using that gift card balance. But I am unable to track those purchases. It's been a black hole. Thanks to the above tool, I can now import those transactions into a "cash account" in Quicken, which I created for this purpose. Thank you.

PS: Here is the link to download your Amazon transactions history reports: https://www.amazon.com/gp/b2b/reports/ref=b2b_aht_bao_dbor_ohr1

vladwil96 · June 2021

I think with the subscription model it is a must now that we have CSV imports directly without having to use workarounds to get the information. The more options to import data into Quicken, the more useful it will become.

My bank is dropping Quicken import and because of it I am now looking for other software that allow both online sync and also CSV imports. I think this should be a priority enhancement to the product.

NotACPA · June 2021

I have "voted with my feet" and closed my accounts at multiple financial institutions for that reason.

Q, and it's abilities are more important to me than the relationship with ANY particular bank/card/etc.

When you leave, be sure to tell them why.

GabrielSimon · August 2021

I figured out an easy hack for this. Quicken allows you to import Mint transactions in CSV file. As long as you use the exact same column tiles and formatting as Mint uses when it exports its transactions, you can import transactions into Quicken using a CSV.

See the attached screenshot with sample mapping. The main thing to be aware of is that you need to specify credit or debit in a separate column. Quicken won't recognize the +/- sign in the amount column.

I hope this is helpful!

GabrielSimon · August 2021

Oops, looks like some of the field names got cut off. Here are the full field names
Date Description Original Description Amount Transaction Type Category Account Name Labels

Perry Smith · September 2021

Adding in some commas to clarify:

Date, Description, Original Description, Amount, Transaction Type, Category, Account Name, Labels

importing csv

Not Planned · Last Updated August 2022

Comments

Categories