Skip to main content

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

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:
Another useful page that is hard to find on their system is:

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.
Customer Open Invoice Export QuickBooks 2 Sage

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

QB2S1 CustomerOpenInvoicesExport

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.
Suppliers Export from QuickBooks

Then select new worksheet:
Suppliers Export to new Sheet

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.

Alternative Export Supplier Export report

Then once the report is displayed, click Customise Report.
Customise Report

Then using the Look For select the fields listed below:
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:
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.
Suppliers Unpaid Bill Export report

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.
Transaction bill type

Then Select Excel->Create New Worksheet.
Excel Export

Then select Create new worksheet.
new sheet excel export
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.
QB2Sage Items Export

Then select Create new worksheet.
new sheet excel export

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.
QB2Sage Assets Export

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.


Popular posts from this blog

POST as form using C# to Pay Provider

The Remote Post Form class I have used the code for the jigar site and added my own goodies, so all credit to him. Sorry about the flast formating but is the only way I could get a neatish post using System; using System.Collections.Generic; using System.Web; using System.Security.Cryptography; /// <summary> /// Remotely handle and Post form from /// </summary> public class RemotePost { private System.Collections.Specialized.NameValueCollection Inputs = new System.Collections.Specialized.NameValueCollection(); public string Url; public string Method; public string FormName; public RemotePost(string pURL) { // // TODO: Add constructor logic here // Url = pURL; Method = "post"; FormName = "frmRemotePost"; }   public void Add(string name, string value) { Inputs.Add(name, value); } public void

Fixing winmail.dat problem - specifically in Quickbooks

For months we have had problems with attachments from Quickbooks. Having looked down many avenues I think we have found a fix or few: (See below for update) Here are a few websites that help out: Microsoft Outlook/Exchange MS-TNEF handling (aka "Winmail.dat", "Win.dat", or "Part 1.2" problem of unopenable email attachments) KB958012 : When you use Outlook 2007 to send an e-mail message, the recipient of the message sees an attachment that is called Winmail.dat Essentially the summary is that there is a problem with Outlook trying to force Rich Text Format. so you need to turn that off. Mail users not receiving email in an Outlook derivative will have a problem. This format is called So: Go into Outlook On the "Tools" menu, click "Options", then click the "Mail Format" tab, and then the "Internet Format" button. Set "When sending Outlook Ri

Bitcoin / Cryptocurrency – what is it and how can I benefit

What is it I started investigating Bitcoin when it was worth just over $1000 a bitcoin. I was interested in what it was and how it worked. A lot of people are saying we missed the boat, but I believe that everyone should at least try put a little money in now, or at least use a faucet (see below) to make a little micro-currency. You can read a Wiki article about bitcoin and its history etc. But what you need to know is that it is a currency, that is independent of country. No one really knows who invented the concept of a cryptocurrency since the person who published the paper used a nom de plume. All new cryptocurrencies work more or less the same way as Bitcoin. So as I explain below I interchange these terms. Bitcoin is the original cryptocurrency. How Bitcoin works The currency releases a coin based on a mathematical formula. There will never be more than 21 million bitcoins (other cryptocurrencies do not work like this). Each bitcoin can have divided into one hundred mil