Exporting and importing QuickBooks Online data

How to Export data from QuickBooks Online

There are occasions when you may decide that you want to extract your data from QuickBooks Online. The reasons for this may be various including because

• you want to work offline, probably in Excel, in order to do some fancy accounting analysis
• you have an innate fear of losing data if the cloud suddenly disappears
• you want to migrate out of QuickBooks Online to another platform, including QuickBooks desktop
• you want to migrate data to another QBO subscription.

In the UK and most parts of the world – apart from the US – the only export facility from QBO is to an .xls file (for Excel). All reports can be exported thus – click on the ‘export’ button on the top right of the report and select ‘Export to XLS’.

Customising reports to include all relevant data

It’s important that before you export your data to an .xls or .pdf that you have all the relevant data in the report. There are filter and customising arrangements available in QBO that make this possible. Click on the gear icon in the top right of the report and select the relevant data fields and filters – remember that some of the data may be inactive but this can still be displayed and then exported.

Backing up your QBO data

It is true that there is no complete data export facility for QBO in the UK. In the US, if you’re using Internet Explorer, there is an Export Company data option available – information about this is prominent across the web if you want to look into it. One can only presume that it is a matter of intuit policy that such a facility is not available outside the US for reasons best known to them. As QBO users we should continually hound them to roll out this facility across all the flavours of QBO around the globe.

Does this mean we should give up the idea of ever backing up our QBO data? For some users the answer will be a straight ‘yes’ – surely one of the main reasons for using a cloud-based accounting solution is that this problem is taken care of for you. However others may want the extra security that an occasional download of data provides.

The bottom line, however, is that any back up is going to be a manual exporting of various reports to .xls files. These are the ones that I would choose to offer reasonably comprehensive back up of data:

• customer list
• supplier list
• inventory/ stock list
• general ledger.

All of these reports are available in QBO. If your intention is to transfer the data to another accounting system then you will want additional data, including creditor and debtor files.

Importing into QuickBooks Online

Now we get to the situation that prompted this post. What if you want to move data to QBO, perhaps from another QBO subscription? Let’s not get into why you might want to do this – suffice to say that intuit really don’t want us moving data around between subscriptions (or licences) so do not facilitate it in any way. Which brings us to the key piece of advice here: don’t bother with trying to shift large amounts of data into a new QBO file because you can spend a lifetime trying to make it work.

However, it is possible to import/ upload certain categories of date into QBO: customers, suppliers, inventory and other items and bank transactions. What can’t be uploaded are the transactions themselves as contained in the General Ledger export mentioned above.

The method for importing/ uploading (from Excel) is discussed in the following training video:

There are some important points to consider when you are doing this:

• .csv files seem to work best, but sometimes only if it is an MS-DOS .csv file.
• Have a look at the sample file but don’t be fooled into thinking that the fields mentioned in these are the only fields that can be imported – for example, with stock items, there are numerous fields that can be imported (such as purchase and sales price) which only become evident once you’ve had a go once.
• There cannot be any blank cells – make sure every one is filled with relevant data.
• Be careful with dates – QBO might now like the format in the .csv file.
• Once you’ve tried importing once and it has been accepted, that data is in QBO even if it is wrong – so start with a small number of items first in case you have to manually delete.