Importing Data from Excel into QuickBooks Online with ZED AXIS 11.0

UPDATED 02/28/2015

With Zed Axis 11.0, I used it to import some open invoices from an old accounting system into QuickBooks Online and it worked really well.

I will start by showing you a Customer Balance Summary report as of 12/31/2013 from my New QuickBooks Online company file i setup from scratch with no customer Data:

Here is a screenshot of the excel file I have that contains my open invoices:

It must Contain the following fields:

• Customer Name
• Invoice Date
• Invoice Number (Document Number)
• Amount

The due date and memo are optional, However the ITEM NAME is missing from the spreadsheet and it is required! which is ok, because we are going to tell Zed Axis to create a new item for us called “Balance”.  Next Step, I am going to launch Zed Axis:

I am going to click on QuickBooks Online and hit the Connect Button, this is going to launch a Intuit Login Screen:

Then you must verify the company and AUTHORIZE.

After Authorizing the company file, QuickBooks Online will now be synced with Zed Axis, and data may be imported.  Next Step is to select the excel file and load it in Zed Axis.

After the file is loaded and you see the transactions on the preview screen, is time to create the mapping, by going onto Setup colum mapping and click the dropdown menu to choose “Add New Mapping”

This will take you to the mapping screen:

Give the mapping a name and choose the Import Type, you are currently only allowed to import the following types of transactions, based on the mapping window:

I choose Invoice and then mapped the following fields:

• DocNumber to Invoice Number
• TxnDate to Invoice Date
• LineAmount to Amount
• CustomerRefDisplayName to Customer Name
• LineSalesItemRefName to a Constant I manually typed as “Balance”*

*Balance is a new item that I am programming Zed Axis to create for me.  All newly created items will be created based on the parameters set on the “Import Options, More Options” screen, which looks like this:

From the screen shoot you can tell that new items will be created as single-sided “Service Item” instead of Inventory or non-inventory and it will point to the income account “Services”

Since it looks like are ready to go, I click on IMPORT and see what happens:

This screen has been improved to give instant feedback to the user about the status of the import and any errors that may arise.  This whole process from start to finish took about 10-15 minutes, the real variables are making sure the spreadsheets are complete with data and have constant formats; and, of course, the amount of invoices will vary the transfer time.   Not too long ago I transfered about 300 open invoices, it took almost 1 hour.

Lastly, Lets look at the A/R Report in QuickBooks Online to see the end result:

Just to be sure, I’ll double click on one of the invoices to see how the data imported:

Looks Perfect!

Zed Axis is a $199 utility for importing Excel/Spreadsheet data into QuickBooks Desktop or QuickBooks Online.  It is a one time fee with no monthly charges compared to Transaction Pro which is $20/month; there are other differences other than pricing between the two tools.. but that may be a future article.  For the time being, strongly recommend you download the 30-day fully functional trial and see it for yourself:

http://bit.ly/ZedAxis12