QuickBooks AaaTeX’s TransImporter
How it Works
(Learn and Work Smarter not Harder)
January 14, 2012
We are always asks ‘How it Works?’. We always answer ‘Very Well’ but that not seem to satisfy them.
To this end will try answer this with what the problem is and what our solution is.
Problem: How to get Excel supported files (csv, xls, xlsx, tab delimited) of data into QuickBooks?
Excel file types has been used for a long time as a way of transferring information. Many systems have an export capability to product these files. Customers may use Excel to create templates for their sales people who do not have QuickBooks. This information needs to be ‘imported’ into QuickBooks but QuickBooks cannot import transactions. So how is this to be done?
Our TransImporter (actual has several versions TransImporter (Sunsetted), TransImporter2011 and the new TransImporter2012). The TransImporter’s support QuickBooks Desktop versions and QuickBooks Online and but not QuickBooks Point of Sale.
1. Platform: Windows either on a workstation, server or a cloud environment. It is a Windows based program that is started like any other Windows application i.e. Start Programs menu or a shortcut on your desktop. Future versions of the TransImporter will be web/cloud based. These should be available by 3rd quarter 2012.
a. Internal Text for csv and tab delimited text (Excel not needed)
b. Excel - for xls, xlsx, etc. and optional for csv and tab delimited text.
c. FTP (File Transfer Protocol) can be used in the TransImporter2012 to get the files.
3. QuickBooks Interface. The Integrator uses the Intuit provided and supported QBSDK Foundation Class (QBFC). This does not require Admin/Single user mode. It is an API for QuickBooks (there are different QBSDKs for QuickBooks and QuickBooks Point of Sale). There is no support for QuickBooks on the MAC.
4. File Formats
a. Columnar where all data is repeated in each row. Transactions with multiple items have each item on the next row, order/entity info (name, date, etc) are repeated.
b. Header section where there is header information usually onto for the order/entity and items listed below (TransImporter2012).
a. Column Mapping – The various fields that the TransImporter support are comparable to QuickBooks fields (i.e. Name=Customer Name, Item No=Item Name, etc) have a drop down for the column in Excel (i.e. Column C for Name, Column F for Quantity, etc.)
i. The TransImporter2012 also suppose using cell (either actual cell as A4 or a cell name) for fields that are not repeated in each row.
b. Row Start – A row start text box is included to tell the program where the data starts i.e. Row 2.
6. Actual Process – After setup is complete
a. Starting - User Start the program from the Windows Start Menu or a shortcut on the desktop
b. File select – Select from the file list box or browse to the file/folder then double click or click on Go.
7. Configurations - Different configurations can setup
a. Manual – You start the program and click on a couple of buttons.
b. Automatic – You start our folder monitory to check every nnn minutes to check the selected folder for file names that match a pattern.
c. Started from command prompt with command arguments
i. System scheduler to start at a particular time
ii. Startup Folder to start when user logs on.
8. Flexibility – the program is EXTREMELY flexible due to the large number of settings and external map files.
a. Basic Map by columns – Your eCommerce store will have a map file for the store’s field to our/QuickBooks name
b. Item, etc.
9. Customizations – can be done.