CSV to QIF for Quicken

Topic

New Reply

I’m still using Quicken (2012) offline and trying to find a decent csv-to-qif converter.  Does anybody know of anything legit from a safe site?  ( I see a few out there but am always leery of sites I don’t know.)

Reply | Quote

Replies

Does Quicken have an import function for .csv files? I would think it should, as some banks download in that format.

1 user thanked author for this post.

ctRanger

Reply | Quote

No, it will only import Security Prices from a .csv file.

Transactions have to be imported from .qif (or via web connect).  None of the banks export .qif any more, but I can always get .csv files.

Reply | Quote

Transactions seems to do it, but it’s not free.

cheers, Paul

1 user thanked author for this post.

ctRanger

Reply | Quote

Thanks, new to me, I’ll check it out.

It seems like it should be so simple, but if it was, everybody would just load their spreadsheets and bypass Quicken.

Reply | Quote

Can you use QFX instead of CSV? If so, I have a small perl script I made years ago that converts QFX to QIF so I can import into the Quicken 2002 I still use. (Caveat: it only works for bank and credit card transactions, so it won’t help if you need it for other types of accounts. I never enhanced it beyond that.)

If you don’t find a better solution, let me know if you want to try that.

 

Reply | Quote

At this point my better solution is to manually enter the transactions, and there’s quite a bit backed up.

Credit card/bank types are all I need. I can get a lot of the files as QFX, but am clueless about perl.  I did a few searches but couldn’t figure out what I would need to run it.  What would I need?

 

 

Reply | Quote

Yeah, I realize that, unlike me, most people don’t have perl installed on their system, so I’ll bundle a tiny portable perl interpreter (less than 1 MB) with the download.

Download qfx-to-qif.zip from my website. Extract the folder from within, and place it on your desktop or someplace readily accessible. Download the transaction history from your financial institutions as .qfx files, and drop them into the folder. Then double-click the “CONVERT OFX-QFX FILES TO QIF.cmd” file to convert all the .qfx files in the folder to .qif files.

 

2 users thanked author for this post.

auridicyl, ctRanger

Reply | Quote

OMG, thank you!  I’ve been manually entering transactions for years, since my cc company stopped offering the qif downloads.  This just gave me back days of my life!  It was the second worst part of paying bills (after coming up with the money to pay them 🙂  The code works like a dream.

 

Reply | Quote

Thank You Thank You Thank You!!!!!

Reply | Quote

Can I have a copy of your qfx to qif converter

Thank you in advance

Reply | Quote

Click on the link in the post and it will download.

cheers, Paul

Reply | Quote

Thank you thank you thank you, I just imported the first QIF.   I’ve been buried in this and it will at least recover the last 3 months of madness.  I’ll probably write off the .csv conversion as a lost cause.

Have never heard of perl, but I quit the IT world 20 years ago and burned my pager (yeah).  Wish I could pick things up again just to deal with these kinds of problems.

Thanks again.

Reply | Quote

ctRanger wrote:

Have never heard of perl, but I quit the IT world 20 years ago and burned my pager (yeah).  Wish I could pick things up again just to deal with these kinds of problems.

Well, perl is definitely old school. When I was designing websites back in the late 90’s perl was the de facto standard in Apache web cgi-bin scripting, so that’s what I learned.

But it’s not just for the web, it can also be used for general purpose scripting, akin to VBScript in the Windows world. When perl was ported to the Windows platform, I jumped at it because I already knew how to use it, and to this day still use it regularly.

But you don’t see perl much anymore, and I wouldn’t recommend anyone bother learning it just to do some scripting. Learning PowerShell or VBScript for general scripting will probably get you farther, while PHP has become the new standard for web scripting.

Reply | Quote

ctRanger wrote:

I’ll probably write off the .csv conversion as a lost cause.

The problem with .csv files, as I see it, is that there is no agreed upon standard. I test-downloaded .csv files from several of my banks and credit card companies, and every one of them used a different presentation of columns and a variety of field names. Some even used different columns for debits and credits (and all positive numbers), while others used a single column with positive vs negative numbers designating credit vs debit. So I’m not sure I’d trust how accurate a purported csv-to-qif converter might be.

In contrast, .qfx files (which some of my banks call “Quicken 2010” format) will all use the same field names because they all have to conform to the Quicken standard. The Quicken-standard field names are what my script keys in on when converting to .qif.

Reply | Quote

For a one-time load, the formats of the .csv’s wouldn’t matter so much to me.  Each account (12) has about 2 years of transactions in the .csv, I could manually manipulate that data to fit a certain format.  But dealing with the amount of missing data and create .qfx/.qif structure just seems daunting to me.

I have the past 120 days loaded and can go forward with your script, it is a life-saver for this.

Reply | Quote

ctRanger wrote:

For a one-time load, the formats of the .csv’s wouldn’t matter so much to me. Each account (12) has about 2 years of transactions in the .csv, I could manually manipulate that data to fit a certain format.

It would be a daunting task to have to accommodate variable formats on an ongoing basis. But if you only need a one-off solution and can tweak your .csv files first, we should be able to handle that.

It sounds like you have a dozen .csv files, one per bank or credit card account. If you can edit each file to use a consistent format, I’ll work up a script to read each .csv and turn it into .qif format. For a one-off, I won’t waste my time with a lot of bounds- and error-checking overhead because the script will be working with a single, known format.

I’ll do that when I get back home later today. Meanwhile, you can start by rearranging each .csv file to use the following columns:

Date, Amount, Cleared, Chk Num, Payee, Category, Memo

The QIF format spec is pretty spartan and doesn’t really allow for much more than that, so if your .csv contains additional fields, they should be dropped because they can’t be imported anyway.

If your .csv has separate columns for credit and debit, merge them into a single Amount column, with negative numbers for debits. Positive numbers will be credits.

Format the Date field as mm/dd/yy. Format the Amount field as a number — no commas or “$” sign.

When you export everything back out as a new .csv file, open it in notepad (or other text editor) and scan to make sure there are no quotation marks. (Usually, that means making sure there are no commas in any of your entered data because the CSV format uses commas as field separators.)

 

Reply | Quote

Here’s the script for converting .csv to .qif, with instructions for how to use it.

Minor correction to earlier post: field name in your .csv should be “ChkNum” (no space), not “Chk Num”. It’s an optional field, and only of use if you wish to associate a check number with a checking account transaction.

 

3 users thanked author for this post.

auridicyl, ctRanger, Paul T

Reply | Quote

I was buried in the previous conversions…

I can easily modify the .csv files to match whatever you have put together.   It will take me a couple hours to get to it but I’ll start testing later today and let you know how it goes.

Thanks so much for putting your time into this.

Reply | Quote

This script is great.  Most of my time was determining how to modify the fields in each .csv to convert them correctly.   I found the commas and double quotes but the amount formats are pretty wild (remove leading  ‘$  and swap the -/+ ).   Got 2 years of some accounts loaded, working on the others.

Reply | Quote

Not sure how you are editing these csv files, but I found that opening the csv files with Excel allows you to import only the columns you want (getting rid of unwanted data) and you can easily rearrange the columns in the correct order. Save as csv. The script should convert without any more editing needed.

HTH, Dana:))

HTH, Dana:))

Reply | Quote

I’m using LibreOffice.  Every account so far uses a different format, even within a bank.   Most amount columns are just a signed numeric, some need the sign swapped, but a couple are text-format with $ embedded in the text value.  What were they thinking about when they wrote that one.

I’d go nuts if I had to do this every month for the 10 accounts, but for 2 years worth at a one time, its fine.  Making the bulk of the modifications (category, memo) is easier in a spreadsheet than in quicken.

Reply | Quote

If you have files you can only get in CSV we can probably modify the perl script to convert them for you.

cheers, Paul

1 user thanked author for this post.

ctRanger

Reply | Quote

Anything prior to the past 120 days is all .csv, a lot of transactions.  But unless I’m missing something, csv-to-qif is a lot more complicated than qfx-to-qif?

I’m still researching ProperSoft and looking for reviews.  It might be worth purchasing for a month to salvage 2 years.

Reply | Quote

https://listoffreeware.com/free-qfx-to-qif-converter-software-windows/

HomeBank is free personal accounting software that can also be used as a QFX to QIF converter. It works on Windows, macOS, and Ubuntu. In this software, you can directly import a QFX file to instantly create an imported account. However, it also lets you import QFX file financial data to an existing account if you want. Apart from QFX, you can also import and convert OFX and CSV files to QIF format.

1 user thanked author for this post.

ctRanger

Reply | Quote

I don’t use QFX, but that worked out for you I see.

But maybe somebody could help me with QIF.  I imported my scanned receipts from Epson ScanSmart. Their export to QIF doesn’t seem to work so well. So I saved to CSV, massaged it a bit in excel and used an online converter at csvconverter without incident although now I see it complains of certificate problem so I guess I can’t recommend it.  It worked ok after some fiddling, but I am looking for something a little better.

I was going to try at http://xl2qif.chez-alice.fr/xl2qif_en.php, that is an excel addin.  I would import the csv to excel and then export as a QIF.  Has anybody experience with that?

TIA,

Steve

 

 

 

Reply | Quote

How safe is that Transactions App?  I need to do a one-time only conversion for about a half dozen accounts and I hate to have that info all at risk yet I really don’t have the time to get Quicken updated.  I figure I could use the app one time for each and then cancel it as I plan to stay on top of entries better.  Thoughts?

~Lenora

Reply | Quote

I just wrote up this simple perl script to take CSV data (which I paste right into the script, rather than reading from a file), and output a QIF for Quicken 2012. Fortunately, QIF format is really easy to worth with.

#!/usr/bin/perl
my @fields = qw(Account ChkRef Debit Credit Balance Date Description);
my $input = "DATA";
my $data;
my @lines = split "\n", $input;
for ( my $i = 0; $lines[$i]; $i++ ) {
my @items = split ",", $lines[$i];
for ( my $j = 0; $fields[$j]; $j++ ) {
$data->[$i]{$fields[$j]} = $items[$j];
}
}
open (my $file, ">", "import.qif");
print $file "!Type:Bank\n";
for my $trans ( @$data ) {
print $file "D$trans->{'Date'}\n";
print $file "T-$trans->{'Debit'}\n" if $trans->{'Debit'};
print $file "T$trans->{'Credit'}\n" if $trans->{'Credit'};
print $file "N$trans->{'ChkRef'}\n" if $trans->{'ChkRef'};
print $file "P$trans->{'Description'}\n";
print $file "L[Sales]\n" if $trans->{'Description'} =~ /MERCHANT|CLOVER/;
print $file "Mmemo\n" if $trans->{'Description'} =~ /keyword/;
print $file "^\n";
}
close $file;
print "Data converted to import.qif\n";

[Moderator edit] formatted code to allow copy / paste (fixes quotes)

Reply | Quote

Do you use quotes in the CSV fields (“12.30″,”stamps for mail”)?
Do you use a header line?
What field order? Is it the format you have in line 2 of your code?
Can you post a sample of the CSV format?

cheers, Paul

Reply | Quote

My bank’s CSV does not use include quotes. The 2nd line of that code is where I copied the CSV header line into, and then I removed commas.

But here, I made a simpler version, which actually just reads the csv file when passed as input (“perl convert.pl data.csv”). You might need to update the field names in the print statements to match what’s in your bank’s CSV. And if your bank uses quotes, then just update the “split” line to wrap the existing double quotes with single quotes (to include the quotes in what your splitting on), and just before that line, remove the 1st and last characters (s/^.|.$//g;)

#!/usr/bin/perl
open OUT, ">", "import.qif" ;
print OUT "!Type:Bank\n";
my @fields;
while ( <> ) {
s/\n|\x0D//g;
@items = split ",";
if ( !@fields ) {
@fields = @items;
next;
}
my %trans;
for my $field ( @fields ) {
$trans{$field} = shift @items;
}
print OUT "D$trans{'Date'}\n";
print OUT "T-$trans{'Debit'}\n" if $trans{'Debit'};
print OUT "T$trans{'Credit'}\n" if $trans{'Credit'};
print OUT "N$trans{'ChkRef'}\n" if $trans{'ChkRef'};
print OUT "P$trans{'Description'}\n";
print OUT "L[Sales]\n" if $trans{'Description'} =~ /MERCHANT|CLOVER/;
print OUT "Mmemo\n" if $trans{'Description'} =~ /keyword/;
print OUT "^\n";
}
close OUT;
print "Data converted to import.qif\n";

Reply | Quote

Oh, I also wanted to mention I realized this actually super simple to do in Excel with some basic concatenation. Like, you’d put this stuff into a column, and then copy that column to a text file with .qif extension.

A8=”!Type:Bank”

B8=”D”%B6&”
T”%B4&”
P”%B7&”
^”

I did not check that, btw. And you may need to do something slightly fancier (an IF) to account for credit vs debit (which needs a minus sign added) depending on how your bank  provides it.

Reply | Quote