Knowledgebase

Quickbooks Import/Export for CloudCommerce

Configuration and User Manual

Quickbooks Import is very easy to use. However, due to the complexities of accounting systems, there is a bit of configuration and preparation that needs to be done for it to work smoothly.  

We suggest that you read this manual in its entirety before beginning the configuration process. We also suggest that, if you have limited knowledge of quickbooks, to consult with your book keeper or Quickbooks expert before implementing changes and importing iff files into Quickbooks.

Contents

1 Purpose

2 Support

3 Features

4 Configuration

5 Importing Quickbooks Lists to Quickbooks Import

6 Setting Up Products and Shipping

6 Understanding Matching

7 Matching shopping cart to Quickbooks

8 Preparing Quickbooks Using the Product Import Utility

9 Importing Customers

10 Using Quickbooks Import to Import New Orders

11 Importing the Previous Batch of Orders

12 Known Limitations

12 Known Bugs

14 FAQ’s

15 Troubleshooting

1) Purpose

Quickbooks Import allows you to import sales invoices, customers, products, and payments from your shopping cart directly into Quickbooks. The standard import includes new invoices and new customers, with options available to configure it as needed. Sales tax is supported, as well as shopping cart product attributes,

Quickbooks item groups, and conversion between shopping cart and Quickbooks product names. This version of Quickbooks Import only supports US versions of Quickbooks, and will not work with other versions due to the differences in taxation systems between countries.

2) Support

Support is available via email, phone, tickets and chat at www.cloudcommerce.org

a) [email protected]

b) www.obnit.com/clientarea.php

c) 1-800-699-0820 ext. 2

3) Features

a) Imports new invoices and new customers in one iif file

b) Imports payments with invoices or as sales receipts

c) Can go back and re-import previous invoices

d) Can import all customers or products

e) Converts shopping cart products to Quickbooks item groups

f) Converts shopping cart products with attributes to separate Quickbooks items

g) Converts shopping cart products with attributes to Quickbooks sub-items

h) Converts any shopping cart product to a Quickbooks item with a different name

i) Converts shopping cart shipping methods to Quickbooks shipping names

j) Converts shopping cart payment methods to Quickbooks payment types.

k) Configurable for Quickbooks with sales tax turned on or off

l) Creates unique Quickbooks customer names

m) Supports discounts, coupons, and fees

n) Supports multiple payment methods, including Authorize.net and PayPal

o) Almost no alteration of shopping cart files

p) Multilingual interface

q) Invoice description language options

r) Converts essential data to default store language

s) No file editing needed – all settings done in a graphical interface

t) Configuration saved in a database table making upgrades easy

u) Table creation is automatic after installation

v) Table updates are automatic when new versions are installed

w) Sanitizes data to avoid iif file errors

x) Strips blank lines and line feeds in addresses for proper importing to different Quickbooks versions

y) Shows country name only for foreign orders (option)

z) Changes state/province names to postal codes (option)

aa) Adds customer comments to invoices (option)

bb) Updates status and sends emails to customers (option)

cc) Deletes credit card numbers (option)

dd) Support for discounts, coupons, and fees

ee) Automatic detection of installed payment, shipping, and discount modules in shopping cart

ff) Automatic database installation and upgrading

gg) Support for products with multiple attributes (pass-through only, no matching yet)

hh) Option to only import orders with a specific status (to support PayPal IPN)

ii) Payment method matching to Quickbooks

jj) Indication of which payment methods are prepaid and which aren’t

kk) User configurable customer numbers

ll) User configurable Invoice and PO numbers

4) Configuration

a) After installing the database tables, you will be prompted to continue to the configuration.

b) Check and update all the configuration options explained below as needed for your store. Note that various Quickbooks account names are required and must match exactly what you already use in Quickbooks.

c) You must click “Submit” to continue, even if you did not make any changes.

4) Importing Your Quickbooks Lists to Quickbooks Import

a) Open Quickbooks.

b) Go to File|Export|Lists to iif Files (may vary slightly with the version).

c) Select “Item List,” “Shipping Method List,” and “Payment Method List.”

d) Name the file lists.iif.

e) Alternately, you can separately save items to items.iif, shipping methods to ship.iif, and payments to payments.iif.

f) Upload the file or files to the admin/Quickbooks Import_import directory.

g) Continue following the instructions in the next section for setting up products, discounts/fees, shipping, and payments.

5) Setting Up Products, Shipping, Discounts/Fees, and Payments

a) Products

i) Go to the Quickbooks Import menu item “Set up Products.”

ii) If you have properly uploaded the items.iif or lists.iif file in the previous section, it will be found and you will be asked to click the button to import the files into QBI. The items.iif file will have priority if both are found.

iii) Click “Import iif Items File.”

iv) Your Quickbooks items (products) will import and be listed on the screen.

b) Shipping

i) Now go to “Set up Shipping.”

ii) If you have properly uploaded the ship.iif or lists.iif file in the previous section, it will be found and you will be asked to click the button to import the files into Quickbooks Import. The ship.iif file will have priority if both are found.

iii) Click “Import iif Shipping Methods File.”

iv) Your Quickbooks shipping methods will import and be listed on the screen.

v) If you later find that you need to add, change, or delete items or shipping methods in Quickbooks,you may import an updated iif file to Quickbooks Import at any time. The lists will be updated, and any matches previously made (explained in the section below) will be preserved.

c) Discounts/Fees

i) Now go to “Set up Discounts/Fees.”

ii) If you have properly uploaded the items.iif or lists.iif file in the previous section, it will be found and you will be asked to click the button to import the files into QBI. The items.iif file will have priority if both are found.

iii) Click “Import iif Discounts/Fees File.”

iv) Your Quickbooks discounts and fees will import and be listed on the screen.

v) If you later find that you need to add, change, or delete items or shipping methods in Quickbooks, you may import an updated iif file to Quickbooks Import at any time. The lists will be updated, and any matches previously made (explained in the section below) will be preserved.

d) Payments

i) Now go to “Set up Payments.”

ii) If you have properly uploaded the payment.iif or lists.iif file in the previous section, it will be found and you will be asked to click the button to import the files into Quickbooks Import. The payment.iif file will have priority if both are found.

iii) Click “Import iif Payment Methods File.”

iv) Your Quickbooks payment methods will import and be listed on the screen. At the same time, Quickbooks Import will automatically detect all your shopping cart payment modules and will add or update this data as well in the database.

v) Each payment method will have a select box with the options “Not Paid” and “Pre-Paid.” Methods that are paid on-line, that you may wish to import as Invoices/Payments changed to “Pre-Paid.” Click on “Update Types” to save your changes.

vi) If you later find that you need to add, change, or delete items or shipping methods in Quickbooks, you will be asked to click the button to import the files into Quickbooks Import. The items.iif file will have priority if both are found. you may import an updated iif file to Quickbooks Import at any time. The lists will be updated, and any matches previously made (explained in the section below) will be preserved.

6) Understanding Matching

a) In the next section after this, you will match your shopping cart and Quickbooks items. Read this section to understand how this procedure works before proceeding to the next. Matching products, discounts/fees, shipping methods, and payment methods are necessary for most stores.

b) Quickbooks can not create items on the fly from shopping cart orders – the items in the iif file you import into Quickbooks must be identical to items that already exists in Quickbooks. To give you greatly enhanced flexibility, Quickbooks Import has matching tables that allow it to change the name of any shopping cart product or product/attribute combination to any Quickbooks item or item group.

c) Every shopping cart product and product/attribute combination must either exist in Quickbooks, or be matched in the matching tables. To set this up, you can use the Matching utility and/or the Product Import utility as described below. If you already have shopping cart and Quickbooks product names that match identically, then you do not have to do either.

d) The item description that appears on the Quickbooks invoice does not have to match. In fact, one Quickbooks Import configuration item gives you the option to leave the description in the customer’s native language (for stores where customers may use a variety of different languages when ordering).

e) Unless they are matched to something else in the matching tables, shopping cart products with attributes will appear as Quickbooks items/subitems when imported. For example, the shopping cart product “Running Shoe Size 10” will import as “Running Shoe:10”.

f) Shopping cart products with multiple attributes can not be matched at this time. But, they can be

imported and will appear as a Quickbooks item:subitem:subitem... . You must make sure that the item and subitems already exist in Quickbooks before importing these orders.

g) Shipping methods also need to be matched. As the shopping cart’s shipping entries are much too long for Quickbooks, and include the package weight, they need to be parsed and converted to Quickbooks shipping methods. If a shopping cart method is not in the matching table, a default type of “Method not set” will appear on the invoice.

h) Note that the terminology used by the shopping cart and Quickbooks differs as follows (shopping cart/Quickbooks):

Things for sale: products/items

Product code: model/name

Product name: name/description

Paragraph describing the product: description/(n/a)

7) Matching shopping cart to Quickbooks

a) In Quickbooks Import, select the menu item “Match” and the submenu for Products, Discounts/Fees, Shipping, or Payments as desired.

b) You only need to match products where the product code will not be exactly the same in both the shopping cart and Quickbooks (shopping cart model/Quickbooks name).

c) All shopping cart shipping methods must be matched to Quickbooks shipping items, or else your invoices will not show the shipping method chosen by the customer.

d) Note that because some contributed shipping modules do not properly include the shopping cart language files which Quickbooks Import scans to find all the shipping methods, some shipping methods may be missing or incorrectly worded. In this case, you may need to either make corrections in the shipping module language files, or manually add missing shipping methods in the Quickbooks Import_shipcart database.

e) If you add or delete any products, discounts/fees, shipping methods, or payment methods in Quickbooks, you can upload a new iif file and load it into Quickbooks Import (the match screens will prompt you if a file with the correct name is found in the Quickbooks Import_import directory). Only the changes, deletions, and additions will be made and your previous matches will be preserved.

f) The match utility creates a drop-down menu for each shopping cart store product that lists every

Quickbooks item and item group. If you have thousands of items in your store, it can take some time to load the page. In this case, the “product rows displayed” option in the configuration should be set to a low number.

g) Note that you must click the button to save your changes before going to the next page when the list spans multiple pages. Otherwise your changes will not be saved.

8) Preparing Quickbooks Using the Product Import Utility

If you have never used Quickbooks with your on-line store, and you wish to import all your store items into Quickbooks, click the “Create iif file from all shopping cart products” button in the Quickbooks Import Utilities to make an iif file that you can import into Quickbooks. This will work properly with shopping cart products that have attributes, but will not work for shopping cart products that you wish to match to Quickbooks item groups. Unless you have a large number of items that are not already in Quickbooks, and you will not wish to change the names or use item groups, this method is not recommended. It is preferable to add your items directly in Quickbooks, and then match them to the items in the shopping cart using the “Match|Products” page as explained above. Note that the item import, if you choose to do it, only needs to be done one time.

9) Importing Customers

You may use the Import Customers feature on the Create page to make an iif file for importing all your shopping cart customers (only those who have actually placed successful orders) into Quickbooks. It is not necessary to do this, as customers are always imported with their orders. Use this feature only if you wish to have all your prior customers in Quickbooks for historical reasons.

10) Using Quickbooks Import to Import New Orders

a) Go to the Quickbooks Import menu item “Create iif”

b) Under New Orders, click “Download iif File.”

c) In the dialog box that pops up, select “Save to Disk” and choose the location.

d) In Quickbooks, go to File|Import|iif Files.

e) In the file box that pops up, select the file that you just saved and click “open.”

f) Quickbooks will confirm that the file was imported successfully!

Note: Sometimes Quickbooks will confirm success even though the import has failed. The first few times you import the iif files, make sure that the invoices and customers have imported correctly. If they haven’t and/or you receive any errors messages, please save the iif file for troubleshooting, and write down the exact text of any error messages. Quickbooks Import has been thoroughly tested to avoid any errors, but...

11) Importing the Previous Batch of Orders

Every time you import new orders, they then become the “Previous Batch.” If you need to reimport them for some reason, for example if you change an option in Quickbooks Import and wish to try again, use the “Previous Batch of Order” import feature on the Create page.

12) Known limitations

a) Sales tax groups are not currently supported. Quickbooks has a serious bug where sales tax groups may incorrectly calculate sales tax. This is because Quickbooks rounds each tax item to the nearest penny, instead of rounding just the tax group. Intuit is aware of this bug but has not corrected it for many years! For this reason, do not use sales tax groups to avoid input errors. If you still wish to use sales tax groups, use a single tax item that reflects the total tax rate, import the invoices, and then change the tax to a group on the invoice afterwards. Note that this work-around could cause problems with prepaid orders, because the invoice total may change by one penny after switching from a single sales tax item to a group.

b) Credit card numbers can not be included as iif files do not support this. However, payments are supported, so if your store uses a credit card payment gateway, customer payments can be imported into Quickbooks as invoices/payments or sales receipts.

c) Sales receipts are numbered sequentially in Quickbooks and do not reflect the shopping cart order number. To partially work around this, the shopping cart order number is imported into the “PO Number” field. In the Quickbooks templates, you can change this to read “Shopping cart Order Number.”

d) Products with multiple attributes will pass through to Quickbooks but can not yet be matched.

e) Non-US versions of Quickbooks are not yet supported.

f) The product export iif will only show quantities for master items. Items with attributes will show as zero inventory. This is due to a limitation of the shopping cart program, as there is no inventory tracking for individual attributes. This is due to be addressed in future versions of the shopping cart.

13) Known bugs

a) In some rare cases the automatic database installation procedure does not work correctly. Check that all file system and web paths in osC are set up correctly – this sometimes happens on test sites that are not live.

14) FAQs

15) Troubleshooting