Audimation Services has been acquired by CaseWare International Learn More.


Blog Image

Importing QuickBooks Data into IDEA

QuickBooks is an essential part of everyday business for small- and medium-sized businesses. With limited full-time employees (FTEs), record keepers and signers are often the same person, which can create separation of duties issues. Its common practice to audit by transaction number. To do this, you will need to normalize the data in IDEA. With a few simple steps, you can easily acquire and transform your QuickBooks Enterprise data into a usable format for analysis.

QuickBooks Steps:

Starting with a standard Journal Entry Report as an example.

  1. Go to “Customize” and add “Account” and “Trans #” as additional columns if one or both are not part of the detail line. Drag these columns to the far left. [Note: Splits may still be still an issue.]
    Customizing a QuickBooks Journal Entry Report
  2. Export the report to Excel or CSV depending on size of file. CSV preferred.
    Send QuickBooks Report to Excel Dialog Box


 IDEA Steps:


  1. QuickBooks reports in Excel, are not a “clean” Excel import. In appearance, they are more like a print report – headers may need to be removed prior to import and columns removed after import. This is why CSV is preferred.
    QuickBooks Column Headers in Excel
  2. When importing TEXT – CSV, don’t forget to define a MASK for the DATE fields & bring in amounts as type NUMERIC. You can also exclude blank lines by filtering – example:
    Using Criteria with Import Assistant
  3. If any columns are blank, requiring fill down, see: Tech Tip: Filling Empty Cells


More Resources

The Financial App is effective for finding: duplicates, out of balance journal entries, missing entries, date-specific entries, time-specific entries, and many more points of interest like journal source and period. Benford’s Law can be applied to find absolute value.


IDEAScripts can also be used to find out of balance transactions, numbers ending in 999 or 000, key terms and credits to cash. IDEAScripts are available to all supported IDEA users. To get there from inside IDEA, click the “File” tab, then click the “IDEAScript Vault” tile.

Best Practices , Importing , Tech Tip

Posted By

By Kris Willison
Kris joined the Professional Services team in January of 2015 as a Solutions Specialist. She has an extensive background in Software and Database Development accumulated from thirty years in IT support with twenty years’ experience in database development, cleanup, audit and migration using Microsoft Access. In her time with Audimation, she has received client praise for her “Top Tier” engagement on Monitor and Scripting projects. Kris enjoys looking at problems from new angles to determine the most efficient means of meeting the clients’ needs. Kris has been breeding/showing purebred Balinese cats since 1972 and Oriental Longhairs since 1996. She also hosts one of the largest online pedigree database sites for Siamese and related breeds with nearly 600 users worldwide.

Related Posts
Analytics: It's All About Insights & Efficiency
Jul 21 Technology impacts every aspect of business. It has the ability to increase productivity, do more with fewer resources and deliver meaningful insights. Both int...
The Rules of Nature and Audit
Jun 06 What’s missing is often as important as what’s present. The rules of nature apply very well to audit if you consider, “Everyone knows it, but do you test ...
Using Data Analytics to Prepare for Fieldwork
Feb 20 Abraham Lincoln once said that if he had eight hours to chop down a tree, he would spend six sharpening his axe. Proper preparation can save hours of effort and...

This website has been designed for modern browsers. Please update. Update my browser now