CSV file import issues

jdparker225
jdparker225 Member ✭✭✭✭
edited April 13 in Download, Add/Update Accounts (Windows)

I can't do OSU for my VISA card or download a QFX file but I can download the transactions to a CSV file. Unfortunately it is not in the format that Quicken requires. However it is possible to download the CSV file, open it as an Excel spread sheet and rearrange it into the proper format and then save-as CSV file. I can do that using Open Office Calc. I was able to import the transactions successfully, however….

  1. They were directly imported into the transaction register rather than imported into the downloaded transaction register as they would if I were importing a QFX file, and
  2. There was no attempt or ability to match the imported transactions to transactions already in the transaction register (uncleared). These already existing transactions are transfers from another account that can be downloaded via OSU and thus do show up in the VISA account. The result was duplicated transactions.

Is there any way to get the CSV file transactions imported into the downloaded transaction register, where I could match the transfers to the existing transactions and then accept them?

Without a way to do that, the CSV file import is useless.

Thanks

Jim

[Edited Readability]

Tagged:

Comments

  • Quicken Laura
    Quicken Laura Quicken Windows Subscription Moderator mod
    edited April 14

    Hi @jdparker225,

    Thank you for reaching out. I understand how this can be confusing.

    CSV imports in Quicken don’t work the same way as Web Connect (QFX) imports. When you import a CSV file, the transactions are added directly to the register and won’t match existing transactions, even if the details are identical.

    As a workaround, you can import the CSV into a separate account and then move only the transactions you want to keep into your main account.

    To do this:

    1.Create a new account

    1. Click the + Add Account button at the top of the sidebar.
    2. Scroll down and select Offline Account.
    3. Choose the same account type as the old account (e.g., Checking).
    4. Enter the Account Name and Start Date. Leave the Ending Balance blank.

    2.Import your CSV into the new account

    3. Move transactions to the main account

    1. Open the new account register.
    2. Highlight all transactions (click the first, hold Shift, click the last).
    3. Right-click and select Move transactions.
    4. Choose the main account and click OK.

    I hope this helps!

    Quicken Laura

    Make sure to sign up for the email digest to see a round up of your top posts.

  • jdparker225
    jdparker225 Member ✭✭✭✭

    @Quicken Laura Thanks for the info. But why wouldn’t step 3 be move transactions to old account (after reviewing transactions in new account)?

  • splasher
    splasher Quicken Windows Subscription SuperUser ✭✭✭✭✭
    edited April 13

    @jdparker225

    I use a free program called ImportQIF to to convert my .CSV file downloads into QIF files for import into Quicken. See this post on how to acquire it.

    https://community.quicken.com/discussion/comment/20545077#Comment_20545077

    It has several very handy features. It prevents duplicates of the transactions that it has imported. It will do renaming and assigning categories to name a couple.

    Yes, as you found out, Quicken auto enters transactions that are imported from a QIF or CSV file, there is no way around that process since Quicken crippled those imports in 2018 with the introduction of subscription Quicken.

    The way I have gotten around the issues of transfers that you described is this. In the transfer transaction that is downloaded by normal means (non CSV or QIF), I leave the category blank so that the matching transaction is not created in the CSV/QIF account. Then, when the CSV/QIF import is done, those transactions are created with the proper category and Quicken then asks if the transaction is a "match" for the transaction in the first account. Works for me every month with my Discover Card and Target RedCard.

    Since I use my CU's bill pay system to pay my credit cards, when I create the transaction at the CU, I manually create a transaction in the checking account with the category blank. When it is finally downloaded from the CU, it matches up to the manual transaction, but the category is still blank. It gets filled in when the CSV/QIF file import happens and I am asked if it is the matching transfer to the checking account transaction.

    -splasher using Q continuously since 1996
    - Subscription Quicken - Win11 and QW2013 - Win11
    -Questions? Check out the Quicken Windows FAQ list

  • jdparker225
    jdparker225 Member ✭✭✭✭

    @splasher interesting. There are also converters that convert CSV to QFX. If I were to use a converter, why would I use a CSV to QIF converter instead of a CSV to QFX converter? (I do know the financial institution information that I’d have to supply to convert to QFX.) E.g.: Does QIF have a way to prevent duplicates that the converted-to QFX would not have?

    What is a bit interesting is that my credit card FI also provides an OFX file download. It has the transaction ID that would prevent duplicates and it would be trivial to convert to QFX but it is missing a memo field that contains the last 4 digits of the credit card (mine or my wife’s) making the charge and I really want that. The CSV file has that info. What I really want is to smush the OFX and CSV into a QFX.

    Well, what I really want is to convince the FI to provide OSU for its credit card accounts like it does for its checking and savings accounts but so far I am not getting anywhere.

    Thanks

    Jim

  • splasher
    splasher Quicken Windows Subscription SuperUser ✭✭✭✭✭

    Quicken's QIF import is basically stupid, it just jams them into the register without any filtering. ImportQIF does all the filtering and editing for me.

    If the FI is not a participating Quicken partner in downloads for an account, it probably is not going to allow you to import your own fabricated QFX file otherwise users could create their own QFX files for any FI (partner or not). Quicken has to follow the rules of their aggregator (Intuit) and Intuit makes money from the FIs & Quicken by providing that service.

    -splasher using Q continuously since 1996
    - Subscription Quicken - Win11 and QW2013 - Win11
    -Questions? Check out the Quicken Windows FAQ list

  • jdparker225
    jdparker225 Member ✭✭✭✭

    @splasher My FI is a participating Quicken partner and has valid entries in fidir.txt. For some reason it stopped providing QFX files for its credit card accounts in Feb 2023 but continued to support QFX files for other accounts. Prior to that they stopped supporting OSU for the credit card accounts but for a while continued to supply QFX downloads. It still supports OSU for checking, savings, and money market accounts. So it is weird. I have converted the OFX files to QFX and successfully imported them into Quicken but, as I said, it is missing the memo I want. The point is I am not worried about Quicken not allowing the import of QFX…for now.

    I did check out ImportQIF and it does convert to QFX as well.

    What I am now looking into is converting the OFX files to XLS so I can combine it with the XLS from CSV data and save as CSV and import or save as XML and convert that to OFX/QFX for import.

    I’ll probably get it all working and the FI will fix its problems. But if I don’t get it working, they won’t fix their problems. 😊

    Thanks

    Jim

  • jdparker225
    jdparker225 Member ✭✭✭✭

    @splasher OK, I was able to do it: combine the CSV and OFX file that I can download into an OFX file that I can convert to QFX by adding the FI info. I was able to successfully import the QFX file into Quicken and it matched the transfers.

    It involved converting OFX (XML) to CSV, bringing up the CSV files as spreadsheets, inserting a memo column into the converted CSV and copying the last 4 digits of the credit card column in the other CSV into it, then converting the CSV back to OFX and then adding the FI info to make it a valid QFX file. I'll write up more detailed instructions and post back. May take a day or two. The conversions between CSV and XML were a bit tricky

    The FI has not fixed its issues yet😀

    Thanks

    Jim

  • jdparker225
    jdparker225 Member ✭✭✭✭

    @splasher OK, here's how I combined the CSV and OFX files that my financial institution offers for transaction downloads. It's a simple 17 step procedure 😂

    First I am using Notepad++ with plugins that support XML, Open Office (OO) Calc (MS Excel may be a better alternative but Calc is what I have) and the online XML conversion tools located at https://xmltools.github.io

    1. Download the CSV and OFX files close to the same time as possible using same date ranges for both. The idea is to get CSV and OFX covering the same transactions and in the same order.
    2. Copy/paste the <banklist> tag data (this contains all the transaction data) from the OFX file into the input panel of XML to CSV converter tool.
    3. Delete the <dtstart> and <dtend> tag data from the input panel.
    4. Convert and download in the XML to CSV tool.
    5. Bring up both CSV files (the one from the bank (bank csv) and the one from the XML to CSV converter (XML CSV) in OO Calc. Make sure space is not a separator - it is by default. This brings the two files up looking like a spread sheet.
    6. Insert a column in the XML CSV and label it STMTTRN.MEMO.
    7. Copy the data from the first column of bank CSV into STMTTRN.MEMO column of XML CSV. This is the data with the identity of the card making the charge that I want as memo data in my QFX file.
    8. Delete "STMTTRN." for all the column labels.
    9. Save XML CSV.
    10. Edit XML CSV in Notepad++ and change all ";" to "," (OO Calc makes the separators ";". I'd like to figure out how to prevent that but so far I haven't been able to.)
    11. Copy/paste the <STMTTRN> transactions from the XML CSV into the online CSV to XML converter.
    12. Set "Row elements" to STMTTRN.
    13. Convert and download XML (combined XML).
    14. Edit both combined XML and OFX files in Notepad++. Copy/paste the <STMTTRN> transactions from the combined XML on top of the <STMTTRN> transactions in the OFX file.
    15. Add the FI stuff to the OFX file.
    16. (Optional) Use Notepad++'s XML plugin to pretty print the file (it does not print the file; it formats it). This makes it look better but didn't work exactly right for the FI stuff.
    17. Save the OFX file as a QFX file.

    Then backup your Quicken data and import the QFX file.

    Have fun!

    Jim

  • splasher
    splasher Quicken Windows Subscription SuperUser ✭✭✭✭✭

    Glad you worked it out, but it seems like a lot of work to do on a routine basis.

    I prefer my conversion of CSV files to QIF using ImportQIF which only has two steps:

    Step 1 - download the CSV
    Step 2 - run ImportQIF to convert & import the QIF file into Quicken

    -splasher using Q continuously since 1996
    - Subscription Quicken - Win11 and QW2013 - Win11
    -Questions? Check out the Quicken Windows FAQ list

  • jdparker225
    jdparker225 Member ✭✭✭✭

    It is a lot of work but I want the download ID that's only in the OFX file (so I don't get duplicates) and the last 4 digits of the card making the charge that's only in the CSV file. I am not going to do it daily.

    I can guess what is going to happen: they'll fix the one step update issue and it will only provide what's in the OFX file.

Categories