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
In My Opinion – The Value of Timely Reporting
Jul 11 In today’s fast-paced organizations, new developments can unfold at a moment’s notice. Critical decisions often must be made on compressed time sche...
Infusing IDEA into Your Organization
Nov 16 Building a successful and sustainable data analytics program requires a mix of people, processes, and products. At the IDEA Innovations Conference in Houston, l...
Risks of Remote Working
Apr 21 Analyze with Intent & Protect Your Profits As the world fights against the spread of COVID-19, many businesses are in the front lines of the economic impac...

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