QIF imports

[David Reiser]

> I am aware that QIF is a text file and I can open it in text editor. 
> 
> I have a 235000 lines QIF file created by quicken. Most of the transactions
> are being imported properly but there are thousands failing as well. I have
> no way to identify those failed transactions other than going line by line
> in each account. What shall I "edit in quicken first" to insure a successful
> import?

The answer to your last question is that no one knows authoritatively, not even Quicken or Intuit.

The QIF data definition has a number of inconsistencies that were never resolved. Intuit undoubtedly made some internal accommodations on import for some common unusual export decisions made by powerful, large financial institutions. But since that occurred outside of the data stream, the qif definition didn’t have to change. And those “unusual decisions” might not have even violated the qif spec.

There is no guarantee that you will be able to see whatever is causing the qif exporter to create a transaction that trips up gnucash. What happens if you somehow have non-printing characters in your transactions? Quicken may have ignored them internally, but included them in the export. 

Dates are possible source of problems. Several date formats were permitted by qif, but I don’t think you could change format in a single qif file. If you imported data from more than one financial institution and there was more than one date format used, there’s no guarantee that quicken didn’t store the data as it was received and then exported it that way too. Internally, they’d have had to have a way to mark date format within a transaction, but that’s easy if they felt it was necessary. Since the qif definition doesn’t have a way to declare date format per transaction, you might be in trouble that way.

Transfers between accounts have been problematic, but I’ve rarely had to deal with them, so I can’t offer advice on those.

Because you have no way of knowing if your problems result from data irregularities that don’t bother Quicken, limitations of the Quicken qif exporter, or shortcomings in the gnucash import parser, I think you’re better off trying to identify the cause in the qif file and fixing it there.  You’d have to find all your failing transactions in Quicken in order  to fix them, why not search the qif text? 

Are all your problem transactions of one type? Investments? reinvested dividends? transfers between accounts? Anything you’ve noticed in common among the failing transactions can help you figure out a few substitutions that fix hundreds to thousands of problems.

At least open the qif file, find a known good transaction of a similar type to ones that fail, and another transaction that fails the trip to gnucash. Copy them to a blank file and see if there is anything obviously wrong. It seems unlikely that a single problem will account for all your failed import transactions, but if you can get it down to a couple hundred, it would be worth a bit of investigating.

--
Dave Reiser
dbreiser at icloud.com