Getting a large amount of data out of QuickBooks Online is harder than it should be, and the difficulty is not caused by one single problem. Reports are slow to generate before they can be exported. Exports come back with data missing from them. Files that do come out come out formatted in a way that Excel does not handle cleanly. Data that reaches a third-party system does not always map to the fields that the third-party system expects.
Each of those problems has a cause, and each of those problems has something that can be done about it.
Key Takeaways
- QuickBooks Online forces you to run a report on screen before exporting, so large datasets take a long time to load.
- Export limits often drop transactions or customer details, so break reports into smaller timeframes and review each section before moving on.
- Choosing CSV over Excel avoids merged cells and misaligned columns that make exported reports hard to sort.
- Before sending data to an ERP or analysis tool, check that system’s import requirements and fix field mapping in Excel if needed.
Slow Report Generation Before Export
QuickBooks Online requires that a report be run on screen before that report can be exported. The report cannot be sent straight to a file. Where the date range covers a large number of transactions, the on-screen report takes a long time to build, and the export cannot be started until the on-screen report has finished building. In some cases the report finishes building and the export that follows still does not contain every transaction that the report displayed, because a separate size limit is applied at the point of export.
How to Fix Slow Report Generation
A shorter date range should be used. Where an export made from a shorter date range still comes back incomplete, the date range should be shortened again. The date range that works for one part of the year will not necessarily work for another part of the year, because the number of transactions recorded in a month varies across the year, so the date range has to be adjusted rather than set once and reused. Reports that are run outside of business hours also complete faster, because the load on the system is lower at those times.
Missing or Incomplete Data in Exports
A limit is applied to how much data QuickBooks Online will export at one time. The export completes and the file is produced, so nothing indicates that anything is wrong. Transactions are missing from the file. Customer details are missing from the file. Summary figures at the bottom of the file are calculated from the rows that were exported rather than from the rows that exist in QuickBooks Online, so the totals look correct while being wrong. The work of rerunning the report over smaller periods and exporting each period separately is the only way to recover the rows that were dropped.
How to Ensure a Complete Export
Report filters should be checked before the report is run rather than after the export has been produced. A report that is broken into smaller time periods should have each exported period reviewed before the next period is exported. A record should be kept of which periods have already been exported, because that record is what prevents the same period being exported twice and prevents a period being skipped entirely.
Formatting Problems in Exported Reports
An export sent to Excel arrives carrying the formatting that QuickBooks Online applied to the on-screen report. Cells are merged where the on-screen report had a heading spanning several columns. Columns are misaligned where the on-screen report had indented subtotals. A merged cell cannot be sorted, and a column that is misaligned cannot be filtered, so a report that reads correctly on screen becomes a file that cannot be worked with.
How to Fix Formatting Issues
An export sent to CSV rather than to Excel does not carry that formatting, because CSV holds no formatting at all. The file that results is plain and it is sortable. Report settings inside QuickBooks Online can be adjusted before the export is run, and adjusting those settings reduces the amount of formatting that is carried across. Excel has data cleaning tools that will repair a file that has already been exported, and those tools are the remaining option where the export cannot be run again.
Compatibility Issues with Third-Party Applications
Data exported from QuickBooks Online is frequently loaded into another system, which may be a different accounting package, an enterprise resource planning system, or a financial analysis tool. The receiving system expects fields in a particular structure and with particular names. QuickBooks Online produces fields in its own structure and with its own names. Where the two structures do not match, the load fails, or the load succeeds and places values in the wrong fields, which is the worse of the two outcomes because it is not obvious that anything went wrong.
How to Address Compatibility Issues
The import requirements of the receiving system should be read before the export from QuickBooks Online is run, rather than afterwards. Where the fields do not correspond, the mapping can be corrected in Excel before the file is loaded into the receiving system. A direct connection built on the QuickBooks API removes the file from the process entirely, and a third-party data connector will do the same thing without requiring anything to be built.
Preventing Export Issues in QuickBooks Online
A QuickBooks Online file that is maintained regularly exports more reliably than a QuickBooks Online file that is not. Data should be verified and the file should be optimized on a regular schedule. The export format should be chosen deliberately, and CSV should be chosen in preference to Excel wherever the formatting is not needed.
Best Practices to Prevent Export Issues
QuickBooks Online should be kept updated and the operating system it runs on should be kept updated, because performance problems are frequently traced back to one or the other being out of date. Time should be allowed for the export, since the report has to be generated on screen before the export can begin and that generation cannot be skipped. Where exports are run often and are running badly, the accounting platform itself is worth reconsidering, and so are the data integration tools sitting around it.
Exporting a large amount of data out of QuickBooks Online is slow, it is incomplete, it is badly formatted, and it does not load cleanly into other systems. Every one of those four problems has a workaround and none of the four has a fix. Looking at alternatives to QuickBooks Online is a reasonable response to that.
