July 3, 2002

Perl scripts for importing data into Quickbooks for Linux users

Paul Tyutin writes: "Intuit's Quickbooks is a great product, many small and mid-sized companies use this software. Scores of accuntants and tax professionals get their client data in Intuit's proprietary format.

Many businesses start off whith personal computers loaded with Microsoft OS and various applications. For accounting purposes, the management installs a copy of Intuit's Quickbooks. The company then logs in their customer data, vendor lists, inventory, credit card transactions, sales receipts etc. The package is really great for any start-up or existing enterprise.

After some time in business an interesting pattern is detected based on companies expence reports: the company spends enormous amounts of money on software: each new workstation needs an upgrade and a new license. Each employee needs some form of expensive licensed software and an upgrade of sorts over and over again.

Another problem arises. Beginner computer users love the graphical interface, they are happy to toy around their desktop with a mouse. These employees point and click on the same buttons and dialogs for a majority portion of their workday. It looks more like a monkey experiment: find a pretty picture and click, good. Now click on a button, good. Now click on Start then Program Files then SomeProgram, excellent! Without focus on automation and intelligent computer use, the people on the companies payroll are engaged in wasting time while opening and closing silly gui dialogs.

Creative in-house programmers are unable to write custom applications to eliminate unproductive computer use due to closed program source, poor documentation, unwillingness of software vendors to cooperate.

This is the time to look into Linux and the vast oceans of free GNU licensed software written by various engusiasts and programmers the world over.

Once the company implements Linux on its workstations its software expenses will siese to exist, well almost. Remember, the company started off with proprietary software. Most of such software has analogs in the Linux world, however some packages are not yet available. In the case of Intuit's Quickbooks, several analogs exist, however, not a single vendor appart of Appgen has a finished competing package.

It is often the case where the company does not have the time or the resources to completely do away with its perfectly functional accounting software. It is even more difficult for companies that log in their day-to-day operations and have an outside accountant overlook their books.

In such a situation, a temporary solution is to have a virtual machine emulate Windows OS under Linux and run Intuit's Quickbooks under it. Netraverse's win4lin is a great solution. Easy to install, it greatly enhances the security of your applications and allows use of the Windows file system by programs running under Linux.

Next, your in-house programming staff writes the programs/scripts to automate your day-to-day accounting activities. Suppose your company has a web site and a relational database such as MySQL. Customer data and transactions are automatically logged in. Your staff generates internal data such as inventory and vendor transactions. At the end of each day (or week if you get really good at this) you import this data into Quickbooks. You then use the feature rich software package, make all the neecessary adjustments, reconcile your corporate credit cards, pay employees, calculate taxes etc. You can also give a copy of your company file to a professional accountant.

The only problem is that quickbooks import/export support is very criptic. Desciphering the byzantine data structure of an IIF file takes forever. Well this is what this article is all about. Here is a little perl script that you may use as a base for your system. It lets you generate an import-ready IIF-like file for an invoice. And here is another file that lets you generate a customer import file. In time we will publish more of these utilities for your convenience. These are not full-scale import utilities, rather a framework for you to design your own system. We used explanatory comments to help you identify all variables and corresponding values.

We wish everyone the best of luck!

Paul Tyutin
Manager/Sysadmin
Sunbirds.com
3780 Hancock Street Suite E
San Diego, CA 92110
Tel. 619-220-7172
Fax. 619-220-7175
http://www.sunbirds.com (our main product)
http://www.sunbirds.com/support (tech stuff, Linux etc.)
"

Category:

  • Open Source
Click Here!