nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

CSV file import issues

jdparker225

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

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
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]

Find more posts tagged with

Windows

File Import

CSV

In Progress

Comments

jdparker225

@splasher These instructions are nt right:

https://community.quicken.com/discussion/comment/20553446#Comment_20553446

Corrected:

First it applies to the CSV/OFX files available for download from Mid-Atlantic Federal Credit Union for credit card accouints.

Back up data file
Modify Register Columns in the credit card account to display download ID.
Download the CSV and OFX files. You want to specify start times based on previous download; end time will be current day. CSV start time will be in the past far enough to pick up any transactions that were pending when previous download was done. This will likely result in duplicates from previous download. The start time of the OFX file should be prior to the start time of the CSV file. How much prior is up to you (more on that later).
Rearrange the CSV file to conform to Quicken CSV file banking import rules. To get the last four digits of the card making the transaction, put the content of the first column into the memo column before dleting the first column.
Import the CSV file. There will likely be duplicates.
Update the OFX file to contain the FI information and save as QFX file.
Import the QFX file and, assuming nothing is amiss, accept transactions. Note that all QFX imported transactions should match some transaction in the register. (More on that later.)
Scan the transaction register in the range of the CSV file for transactions that do not have a download ID. These should be duplicates. Verify by inspection that they are duplicates and delete them.
(This is both of the more laters.) Scan the transaction register in the range of the OFX file for transactions that do not have the memo field containing the last four digits of the card making the transaction but does have a download ID. This indicates a transaction that was not imported from the CSV file but was in the OFX file and likely means you didn't go far enough in the past for CSV start time. In the download transaction register, it did not match an existing transaction. Research any such transactions and hand enter the memo data.

Note: in the complicated 17 step procedure that produced only a QFX file for import, there was not really adequate discussion of how to avoid missing transactions. Basically what is needed is to specify that the start time of both CSV and OFX files need to be the same and far enough in the past to avoid missing transactions that were pending (or not available) when the previous download was done.

Jim

jdparker225

@splasher I've simplified the process considerably with one minor problem to be resolved:

Back up data file.
Download the CSV and OFX files as 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.
Rearrange the CSV file so that it conforms to Quicken CSV file banking import rules.
Import the CSV file
Update the OFX file to contain the FI information and save as QFX file.
Import the QFX file.

The minor problem is that the account transfers from my checking to credit card account didn't "match" so each credit card pay off is duplicated and I had to delete one of the duplicates. I think I know what the issue is: I need to delete those transfers from the CSV file because they are already in the credit card account (from one step update of the checking account) when I do the CSV file import and that's when the duplicates are created. The QFX file import matches one of each those transfers leaving other as a duplicate.

One issue is that CSV file import is not very user friendly. My first attempt to rearrange the CSV file left out one the columns and the only message I got from Quicken was "No transactions found to import". Not really helpful. Dumb mistake but it took me too long to figure out.

Jim

jdparker225

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.

splasher

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

jdparker225

@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

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.
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.
Delete the <dtstart> and <dtend> tag data from the input panel.
Convert and download in the XML to CSV tool.
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.
Insert a column in the XML CSV and label it STMTTRN.MEMO.
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.
Delete "STMTTRN." for all the column labels.
Save XML CSV.
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.)
Copy/paste the <STMTTRN> transactions from the XML CSV into the online CSV to XML converter.
Set "Row elements" to STMTTRN.
Convert and download XML (combined XML).
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.
Add the FI stuff to the OFX file.
(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.
Save the OFX file as a QFX file.

Then backup your Quicken data and import the QFX file.

Have fun!

Jim

jdparker225

@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

@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

splasher

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.

jdparker225

@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

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

jdparker225

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

Quicken Laura

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

Click the + Add Account button at the top of the sidebar.
Scroll down and select Offline Account.
Choose the same account type as the old account (e.g., Checking).
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

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

I hope this helps!