CSV to QIF Using Batch?

[Samir]

I did a bunch of searches for this, but it seems like like nobody has really thought of putting these two together. I've been doing some scouring on the Internet and ran into the wonderful code on dostips to help me with some of the conversions details.

So has no one really tried doing this before?

[penpen]

Do you mean with CSV and QIF the Comma-Separated Values format and the Quicken Interchange Format?
If yes, i assume this conversion is possible IF you have a specification for the comma-separated values format.

The problem would be to define a clean specification to hold the same data as the QIF, as a QIF can contain sessions with multiple transactions in a specific sequence, where the same transaction type can occur multiple times.
I thinks this may be possible but then the CSV file then is sparse populated with data. So it may not be useful to store data from the same data space the QIF supports within a CSV. This may be the reason why

it seems like like nobody has really thought of putting these two together

penpen

Edit: Maybe you only want to store data from a QIF-data-subspace, then you should specify it.

[Samir]

It's actually a much simpler task--ADP provides payroll data in a csv format and Quickbooks for DOS allows importing transactions via a QIF file. It has limitations, but will still save me about 50% of the time of entering the payroll manually like I've been doing.

I've actually just completed a script that will do it. It's just using some basic stuff in xp command line like for and findstr, so it should work pretty consistently. That's what I've always loved about batch files--once you get them working, they just work.

[Squashman]

So this is because you are using a very very very very very old version of Quickbooks? Because as far as I know my wife does this very easily every month at her work and she uses ADP and Quickbooks.

[Samir]

So this is because you are using a very very very very very old version of Quickbooks? Because as far as I know my wife does this very easily every month at her work and she uses ADP and Quickbooks.

The newer versions of Quickbooks are bloated and nothing as simple and efficient as the original DOS version. Analogous to batch files vs vbscript/other-bloated-interpreted-scripting-language-of-your-choice.

The QIF format was actually still used up until a few years ago, and depending on the service from ADP (there are many), there is probably a direct download into the newer versions of QB. Our services are just a CSV. One of the other company's ADP service has some sort of html, but for 3 checks, I'll just enter those.

[Samir]

I actually spent a couple of hours and created a batch file that does this conversion for me. It's doesn't do all the work as I still have to go back and manually enter all the deductions, but it does save me at least 50% of the time I was spending.

Because of the age of Quickbooks DOS, it recognizes years after 2000 using ' vs /. For example, 12-31-1999 is 12/31/99, and 1-1-2000 is 01/01'00.

This didn't cause a problem because I was using replace.bat (from here on dostips ) to parse the resulting file from my batch conversions for /13 and change them to '13 using:

Code: Select all

replace /13 '13

All was good until today...but this week the paycheck date is also the 13th, resulting in 9'13'13 after the above command.

The solution I want to use is something in between my main batch and the replace.bat step. I want to search for /13/13 in my batch converted file, and based on if something is found or not, run replace.bat with different parameters like:

Code: Select all

replace /13/13 /13'13

I've used find and findstr (and prefer the former), but I can't remember/figure out how to branch a batch based on the search results. FIND /C does a great job of narrowing the difference down to a number, and if I can place that output into a variable, then I've got a quick boolean check I can do using IF. At least this is my thinking so far. There's probably better ways as well, and I'm definitely open to hearing them.

I know all this is hard-coded for the year 2013, but I plan to just change the file each year. Any help appreciated.

[Samir]

I figured out how to use an if else to solve the issue of the paycheck date two digit being the same as the year two digit. And all has been fine until today...

So all of a sudden, the batch file is no longer reading data from the correct file. I've even tried reverting to an earlier version of the batch file. I use an XP embedded thin client, so nothing can change on the computer and I've rebooted with the same results.

I'm completely dumbfounded here. How can a set of files that has the same naming nomenclature as in previous sets simply not be loaded like before? I can't figure this out. I've double and triple-checked everything.

Any ideas?

[Squashman]

Kind of hard to give any advice without seeing the batch file you are using and maybe seeing an example of the input and output you need.

[foxidrive]

Need more info, as Squashman says.

If the script is the one that was working, and the result is different - then the format of the data has changed - maybe just one item that is different but it breaks the script.

[Samir]

Kind of hard to give any advice without seeing the batch file you are using and maybe seeing an example of the input and output you need.

Need more info, as Squashman says.

If the script is the one that was working, and the result is different - then the format of the data has changed - maybe just one item that is different but it breaks the script.

Yeah, I didn't think there was enough information to diagnose it properly.

I didn't think about the data changing. That's probably the culprit.

In the end, I redid the section of the batch that dealt with dates and all seems to be working again.