Data Transfer Utility By Karl Irvin www.q2q.us
The Data Transfer Utility was introduced in 2002 and has had many improvements since its introduction. The transfer utility provides a quick and easy way to transfer lists and transactions from one QuickBooks file to another QuickBooks file. This utility can be used to reduce the time and effort needed to solve several critical problems advisors encounter and also to enhance the usefulness of QuickBooks. Listed below are some of the ways you can use the transfer utility.
Data Entered in the Wrong File/Multiple Files for the Same Company.
I've received many calls from users who copied their company file to a server but didn’t remove the company file on the local computer. Sooner or later, someone opens the local company file to look at something and it becomes the default file on that computer. Sometimes its weeks before anyone notices that data is being entered into the wrong file. With the Data Transfer Utility, you can easily correct this problem by transferring all of the invoices, payments, bills, deposits etc. entered in the local company file to the server file. After the transfer the data in the server file will be exactly like it was typed in the local file and the links between invoices and payments and bills and bill payment checks will be maintained.
A similar situation is when users set up multiple QuickBooks files for the same company. One file may have receivables in it while another file has payables and everything else in it. You can merge the data in these files using the Data Transfer Utility. Start by making a copy of the file with the most data in it and then transfer the data from each of the other files into the copy using the Data Transfer Utility. Merging files can be a tricky process because summaries of the transactions entered in one file may already have been entered into the other files so check your work carefully if you are working on one of these assignments.
File Too Large
When QuickBooks files get large, the time it takes to open the program, record transactions and run reports can increase significantly. This reduces productivity and increases the chance of file corruption. The QuickBooks Archive & Condense feature can help some but it usually doesn't reduce the file size significantly. With the Data Transfer Utility and another program call the Beginning Balance Transfer Utility (from the same developer); you can easily create a new file with the minimum number of transactions in it. This new file will be much smaller than your current file and you will gain back the speed and performance that you lost as the file size grew.
One way to start the new file is to use Windows Explorer and make a copy of your existing company file. Then open this new file in QuickBooks and use the Archive & Condense feature and the "Remove ALL transactions" option. This option removes all of the transactions from the new file but leaves all of the lists (Customers, Vendors, Employees, Items, etc.) in place. This new file will still have all of your memorized transactions, memorized reports, and customized templates in it. The "Remove ALL transactions" option will not be available if you use online banking or have payroll transactions in the current year. However, you can defeat the payroll limitation by temporarily setting the system date to a date in the next calendar year. This method doesn't remove any customers, vendors or items so if you have reached, or are close to reaching QuickBooks list limits, you may want to use the approach described in the next paragraph to start the new file. See The Sleeter Group’s Consultant’s Reference Guide for the maximum number of list entries allowed in each list. Press F2 in QuickBooks to see how many list entries your company file has in it.
The second approach to starting the new file is to create a new company in QuickBooks. To use this approach click, File>New Company, choose the option to follow step-by-step instructions and click OK (don’t include one of the pre-defined charts of account in the new file). Open this new file, and then open and close the Receive Payment screen. This creates the special accounts needed for Accounts Receivable and Undeposited Funds. Next, transfer all of your lists to this new company file using either the QuickBooks built-in export/import feature or using the Data Transfer Utility. If you use the Data Transfer Utility, you can choose to transfer all customers etc. or just the ones that have not been marked as inactive or just the ones created or modified after a particular date. This allows you to transfer only your active customers, vendors, items etc., into the new file. This removes the inactive ones and gives you some breathing room before you reach the QuickBooks list limits. After you have imported your lists, edit preferences and turn on sales tax if your company charges sales tax.
After you created the file using either method described above, you can transfer the balances as a specific date (normally the last preceding year-end such as 12/31/03) from the old file to the new file manually or using the developers Beginning Balance Transfer Utility. The Beginning Balance Transfer Utility can transfer the Open Accounts Receivable, Unpaid Bills, Inventory, and the Trial Balance. You will need to manually enter outstanding checks and deposits. Next, transfer the transactions from the first day of the year (01/01/04 if you used 12/31/03 as your beginning balance date) to the current date from the old file to the new file using the Data Transfer Utility. When you finish, you will have a leaner, trimmer file that will be much smaller than the old one.
Offsite/Offline Data Entry
If you have an office QuickBooks file and have someone work at home, in the field or at a remote site, you can use the Data Transfer Utility to avoid overwriting your data and to eliminate duplicate date entry. Normally if you are the only QuickBooks user and if you want to work at home or at some other offsite location, you make a backup of your company file and restore it on another computer. When you are finished on the other computer, you back up the file on that computer and restore it on your office computer. This works fine as long as you are the only one working in the file. If someone is working in the file in the office while you are away working on another computer, you can't use the backup and restore process because it will overwrite (erase) the work done by the other person when you restore the file. With the Data Transfer Utility, you can transfer the work done on the offsite computer to the office computer without overwriting any data on the office computer.
If you are filling out paper forms or using some other software to create invoices, estimates etc. at remote sales and service locations and then re-keying the data into QuickBooks at the home office, you may want to equip your field people with QuickBooks and have them key the data directly into QuickBooks and then transfer their data to the main office computer using the Data Transfer Utility. This will eliminate the duplicate data entry and improve the accuracy of this process. The remote personnel can key the data in a QuickBooks file that doesn't have any transactions in it other than the ones they have entered. This keeps them from being able to see your private financial data.
Other Uses of the Utility
The Data Transfer Utility was created using Intuit’s Software Development Kit (SDK). The SDK provides data validation and error checking to insure that only complete and properly formatted data is passed to QuickBooks. In addition, QuickBooks normal business validation rules are applied to the data and any data not meeting the rules is rejected.
The Data Transfer Utility provides many reports that show you what data was exported and imported. Exported data is held in temporary files until you tell the software to import the data. If you exported data that you don't want to transfer you can change the export parameters and export again until you get the exact data that you want to transfer. In addition, if you decide not to import any data, you can just exit the software and the temporary files are erased. The import reports tell you what data was accepted, what data was rejected and if it was rejected it provides error messages that tell you why the data was rejected.
There are versions of the Data Transfer Utility that work with QuickBooks 2002 through 2004. We recently spoke to the developer and he will have a version for QuickBooks 2005 as soon as it is released. The utility works with all editions and versions of QuickBooks except QuickBooks Basic. In addition, the utility will work with the Canadian version of QuickBooks 2003 and 2004 and the 2003 Australian QuickBooks. The utility requires that you have Microsoft Access 2000, 2002 or 2003 or the Microsoft Access Runtime Engine, which can be downloaded at no cost from the developer’s website. The utility must be installed on a computer that has QuickBooks on it but it can work with any QuickBooks file that can be opened by the QuickBooks program on that computer.
The transfer utility can transfer most but not all of the QuickBooks lists and transaction types so you may have to transfer some data manually. The types of data that can and cannot be transferred are listed under datatypes on the developer’s website. Most of the high volume transaction types (invoices, sales receipts, customer payments, bills, bill payment checks, journal entries etc.) can be transferred. Due to some limitations of the QuickBooks SDK, payroll checks, tax liability payment checks and bank transfers cannot be transferred.
You can download and purchase the software from the developer’s website www.q2q.us. The software that you download is the full version of the program but it limits you to transferring two transactions of each type per transfer pass. After you purchase the software, you are emailed an Owner ID, which you enter into the software. Entering the Owner ID removes the demo limitations, giving you full use of the software.
After downloading and installing the software, you will need to go through the standard one-time setup process for QuickBooks Integrated Applications in which the QuickBooks administrator grants permission for the program to read and write to data to your QuickBooks files. In addition, you will need to edit the QuickBooks Integrated Application Preferences and check the boxes that allow this program to automatically login to your files. After the one time setup process you start QuickBooks and click File>Close Company. This leaves QuickBooks running with no company open and allows you to control the opening and closing of your QuickBooks files by clicking buttons on the transfer utility’s main screen.
To make a transfer pass, you start QuickBooks and close any open company. Next, start the transfer utility and select the types of data to transfer. You can check boxes to choose any or all of the 45 or so available types of data such as invoice, customer, sales receipt etc. Next, enter the date range that you want to use or select all dates. Then enter any additional filtering criteria that you want to use. You can filter by name, partial name, reference number range, partial reference number, paid status, active status, and others. Next, click a button to open or connect to your source company (the company that has the data you want to transfer in it). Then click the button to export the data and when the exporting has completed (the software beeps when it finishes and it displays status messages on the bottom of screen telling you what its doing) click the button to view the export status report. Look over the report to see if it contains the data that you think it should. The report lists each customer, invoice, and other data that was exported. The exporting process copies the data and doesn't change the source company in any way.
If the export report looks good, click the buttons to open the destination company (the file you are transferring data into) and import the data. After the data is imported, click the button to view the import status report. The import status report lists each customer, invoice etc. that you tried to import and it displays an OK by the ones that were successfully imported. For each of those that were rejected, the report lists an error message telling you why it was rejected. You can correct the condition causing the rejection and export and import the data again. Note that you cannot duplicate any list entries (customers, vendors, etc.) by transferring them more than once but if you successfully import transactions (invoices, bills, etc.) more than once, they will be duplicated without warning. If you need help with interpreting error messages, you can click F5 and email the developers a copy of the import status report so they can look the same report you are looking at.
The software is priced between $79 and $119 (depending on version), and the developer provides 60 days of free email support. You are also eligible for free upgrades within the version you purchase and discounts on upgrades to new versions. New versions are usually issued once a year when the new QuickBooks version is released.