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:
Screen Shot 2014-07-21 at 1.20.55 AM

 

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

Screen Shot 2014-07-21 at 1.30.52 AM

 

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:

Screen Shot 2014-07-21 at 1.34.48 AMI am going to click on QuickBooks Online and hit the Connect Button, this is going to launch a Intuit Login Screen:

Screen Shot 2014-07-21 at 12.58.36 AM

 

Then you must verify the company and AUTHORIZE.

Screen Shot 2014-07-21 at 1.38.44 AM Screen Shot 2014-07-21 at 1.38.56 AM

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.

Screen Shot 2014-07-21 at 1.28.30 AM

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:

Screen Shot 2014-07-21 at 1.28.40 AM

 

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:

Screen Shot 2014-07-21 at 1.25.16 AM

 

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:

Screen Shot 2014-07-21 at 1.29.09 AM

 

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:

Screen Shot 2014-07-21 at 1.29.55 AM

 

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:

Screen Shot 2014-07-21 at 1.30.12 AM

 

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

Screen Shot 2014-07-21 at 1.59.47 AM

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

 

 

 

 

 

Hector Garcia

Hector Garcia

Use our link to get 30% off for a year, valid through 07/31/2022

3 Responses

  1. Hi Hector, I have an excel spreadsheet that I use to do my petty cash on. I have set the columns up with the exact headers that the batch entry in the accountant batch entry transaction. However when I try and even copy and paste, it copies it but it keeps on telling me the transactions in red must be corrected. there are however no transactions in red

    1. in red is gonna have all the vendor names that have to be created and the accounts names you must choose…

  2. I have an Excel sheet of invoices to import to QB. I was told that you have a trial offer?

Leave a Reply

Your email address will not be published. Required fields are marked *



Popular Courses

Private QuickBooks Training onsite

Customized Training

Accounting & Bookkeeping Services

Video-Based Virtual QuickBooks Course

Share

30% Off QuickBooks

Use our link to get 30% off for a year, valid through 07/31/2022