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 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:
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.
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.

Comments

Popular posts from this blog

SMTP servers of South Africa

SMTP Settings Below is a list of SMTP sites in South Africa, using this and the ISP Map you can try and find which one works best for you. Telkom smtp.saix.net (ADSL) smtp.telkomsa.co.za (56k dial up) smtp.telkomsa.net Internet Solutions smtp.isdsl.net (ADSL) smtp.dial-up.net (56k dial up on IS) smtp.layerone.net (3g backbone) Vodacom smtp.vodacom.co.za smtp.vodamail.co.za MTN smtp.mtn.co.za Cell C smtp.cellc.co.za (GPRS) mail.cmobile.co.za (also used by Virgin) ABSA mail.absa.co.za iBurst smtp.wbs.co.za smtp.iburst.co.za @lantic smtp.lantic.net (ADSL,Dialup, ISDN) Sentech smtp.sentech.co.za MWEB smtp.mweb.net (ADSL) - this is to be retired End June 2012, use below instead smtp.mweb.co.za (56k dial-up & ADSL & business) iAfrica smtp.uunet.co.za smtp.iafrica.com Neotel smtp.neotel.co.za Tiscali NOW MWeb smtp.tiscali.co.za Netactive NOW MWeb smtp.netactive.co.za Global smtp.global.co.za Hertzner Use y

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) http://news.office-watch.com/t/n.aspx?a=716 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

Programmatically send email in Access using VBA and Outlook

When I first wrote my early Access system I used a function I setup in Outlook and then I called that. On upgrading I had a few problems with the older method then I found some help on the MSDN website here... and the related video. The first thing to do before starting the code is go into access, access the VBA (press Ctl+G) then click on "Tools" The "References" and then make sure you scroll down to "Microsoft Outlook ..." the version there will be determined by what version you have installed. This the code that I ended up with after looking at the site above Private olApp As Outlook.Application Private olNameSpace As Outlook.NameSpace ' The basics of this from MSDN site ' http://msdn.microsoft.com/en-us/library/ee208547(v=office.12).aspx Private Sub InitOutlook()  ' Initialize a session in Outlook   Set olApp = New Outlook.Application   'Return a reference to the MAPI layer   Set olNameSpace = olApp.GetNamespace("MA