Migrating QuickBooks to Sage One Cloud accounting - Part 1 Exporting the data

Some notes

Sage means sage one online accounts, wherever we say Sage we mean Sage one. The QuickBooks we used was version 2012 Professional. But most of the information is similar.

What you need

> You need the templates from Sage and the data from QuickBooks, see below for how to do these.

Get Import Templates from Sage

To get the templates for the items go to help.accounting.sageone.co.za/en_za/accounting/from-your-previous-accounting-system.html

Although you can construct the templates from the information in this post you can download samples of the templates need. Below are the links they provide: Use the following downloads which are referenced in the guide:

• General Ledger Accounts Import Template
• Customer Import Template
• Customer Outstanding Invoices Import Template
• Supplier Import Template
• Supplier Outstanding Invoices Import Template
• Item Import Template 

Another useful page that is hard to find on their system is: help.accounting.sageone.co.za/en_za/accounting/importing-and-exporting-data.html.

Here you can see additional information, on max fields sizes, and the field defaults. The have a summary in the following expandable menus:

• Importing Data
• Common CSV Import Issues
• Checking your System Preferences on a Mac
• Exporting Data
• Customer Map Fields
• Supplier Map Fields
• Item Map Fields
• Asset Map Fields
• Time Tracking Tasks Map Fields
• Time Tracking Projects Map Fields

You need these to place your export data in to from QuickBooks.

QuickBooks Export Files

You now need to export your data from QuickBooks. Below uses QB 2012.

The order in which you do the imports is important since items may refer to one another. So, doing general accounts import then items, then supplier than customer may be the recommended way.

General Ledger export from QB

A number of sites do not recommend that the General ledge is exported. So, doing at least the last quarter does mean you have some history. From QuickBooks go to Reports->Accountant & Taxes->Account Listing:

Then choose Print.

Save as something like QBGLExport.csv.

Customers Export

Customer export is from Customer Centre [Ctrl+J]. Then from the Select Excel-> Export Customer List->New list.

Then create a new sheet:

Then create a new sheet:

And save it as something like QBCustomersExport.

Customer Open Invoices Export

To export Customer open invoices. It may be worth noting (and this will be repeated) that this may be better to do once all the other exports are done and checked, this should really be done at then just before going live.

In QuickBooks use Reports->Customers & Receivables->Open Invoices. Make sure the date is the last date you were not duplicating invoices in Sage from QuickBooks.

This actually shows unreconciled balances, so you may need to tidy it up – After tidying it up filter by type->Invoice.

Then Export to Excel:

Save the file as something like QBCustomerOpenInvoices.

Note: We recommend doing this after the migration is totally done, and the bank recon up to day of migration is complete.

Suppliers Export

To export all the suppliers, go to Suppliers->Supplier Center->Excel-> Export Supplier List.

Then select new worksheet:

Save the worksheet as QBSupplierExport.

Note: this export does not have the Tax/VAT reference number – if you want that look at the next section.

Alternative Export Supplier Export

If want to export VAT number (Tax Reference) then you need to make a custom report, based on the Supplier’s Phone List Report, which is under Reports->Suppliers & Payables->Supplier Phone List.

Then once the report is displayed, click Customise Report.

Then using the Look For select the fields listed below:

• Bill 1,2,3,4,5
• Main Phone – should already be selected
• Fax
• Primary Contact
• Ship 1,2,3,4,5
• Credit Limit
• Main EMail
• VAT ID
• Other 1, 2, 3 – We had custom fields BankAcc, BankBranch and Secondary Contact
• Supplier Type

Then Export to Excel:

Save the file as something like QBSupplierExport.

Suppliers Unpaid Bill Export

It may be easier to do open balances for Suppliers, however if want history or you have a number of suppliers this may be preferred. As with client open balances this should probably be done once all the migration is done.

To export suppliers, open balances we go to Reports->Suppliers & Payables->Unpaid Bills Detail.

You may need to tidy this up to, making sure all unpaid bills are unpaid, rather than unallocated. Then filter the report to only display TransactionType=Bill.

Then Select Excel->Create New Worksheet.

Then select Create new worksheet.

Save the worksheet as QBSupplierOpenBillsExport.

Items Export

Important: You need to be logged in as admin to export the items. The export – does not us the inactive filter, it exports all the items.

From the Menu select Lists->Item List.

Then at the bottom of the screen select Excel->Export All Items.

Then select Create new worksheet.

Save the worksheet as QBItemsExport.

Asset Export

To export your assets from QuickBooks (QB) you can find a list under Reports->Lists->Fixed Asset Listing.

You may also have Assets in the Items list, depending on your version. Those you will have to copy across manually to the Asset Import list.

One of the issues with this report, is that although a lot of information about each asset is stored in QuickBooks, this information is not available via any fields I could find in the Customized Report. Also, perhaps our accounts had flaws but, we had no values in the fields Accumulated Depreciation and Book Value, which would have been useful. So, depending on the Assets, you may find it easier to copy and paste these values across instead of exporting.

But if you use the report then as before the export it to QBAssetListExport.

Check the data

Open all the various files and check that the data in there is what you actually want to import. Delete any clients and suppliers that you no longer deal with.

Data Migration in Part 2

We will post part 2 where we cover the actual manipulation of the data. It will be broken up in to 2 parts.