X
Icon

The 21st Minute


Blog


Blog Image

5 Avenues for Importing Data into IDEA

IDEA Tech Tip


One of the greatest benefits to CaseWare IDEA is the sheer amount of data it can read and make universally consumable – both in file types and in data length. There are many different file types IDEA can accept, and then clean, merge or join to bring cross-company visualization that leadership needs to make solid decisions backed by reliable data. Some data requires a multi-step approach before it can be imported into IDEA.

The Import Assistant in IDEA is our most utilized tool. In most cases, clients are able to find one of these avenues in which to get their data into IDEA. Below we have outlined a few that require a little attention before you can begin your analysis.

Import Assistant - Select File Format Screen

 

PDFs & Print Reports

 

Report Reader transforms PDFs created by any application into clean data table imports. IDEA can read both structured PDFs and those with irregular sizing and positioning.

Data Types Accepted by Report Reader

  • Mainframe text reports
  • DOS print to disk files
  • Generic/Text only file (Windows)
  • Word processor text-only files
  • Text-only accounting reports
  • Email reports
  • Windows Event Log dump files
  • Print to files (PRN)
  • Tab Separated Variable files
  • Standard Data Format (SDF)

 

Working with PDFs

 

When you get a PDF saved from a system to a file, there is typically data underneath the PDF layer. If you can put your cursor on the document and left click and drag, then highlight the data (i.e., name or amount), you can most likely create a template and put it into a database form to work with it.

To learn how to create a template to import a print file into IDEA, view the Report Reader Tutorial:

Access the Start menu and select “All Programs.” Select the “IDEA” folder, and then select the “Documentation” folder. Look for the PDF called “Importing a File with Report Reader” and “More Examples.”

 

Importing Date Fields

 

IDEA’s Report Reader now has built-in intelligence to read date fields even if the date format is not completely consistent. For example, a file having the dates 5/9/2017 and 12/15/2017 can now be set up in Report Reader using one mask. To do this, set the length of the field to exactly the length of the longest date. In our example, that would be the length of 12/15/2017. Then set the mask to match the longest date. Again, in our example, the mask would be mm/dd/yyyy, corresponding to the 12/15/2017 date. Report Reader will bring all the dates, both 5/9/2017 and 12/15/2017 into IDEA under one mask.

Sometimes, data is very “dirty,” and Report Reader cannot determine what kind of data is being imported and what type of field to create. If there is anything in the field that doesn’t look like a date, it will either create a Numeric or Character field. Fortunately, there are ways to correct this in the database after the file has been imported. Read the Tech Tip on Importing Date Fields

 

Reusing Import Templates

 

Report Reader is a powerful tool for bringing PDF or print reports into IDEA. However, importing the same report month after month can get time-consuming and tedious, especially if the report is particularly complex and requires multiple layers and numerous fields. If the reports follow the exact same formatting, this process can be made a little easier if users are able to reuse an existing template file. This can save you from having to recreate layers and traps all over. Click here for step-by-step help.

 

Scanned PDFs & OCR

 

Scanned documents are essentially a picture of the document. When you open it in Adobe Reader, left click and move your cursor, the document will light up. In this case, you’ll need to use an Optical Character Recognition (OCR) to capture the data and import it into IDEA.

 

QuickBooks

 

One of the most used types of data is from QuickBooks. QuickBooks requires just a bit of planning before importing into IDEA. A huge first step is having a good understanding of the dataset, and then to answer these questions:

  • Determine the test that will be appropriate
  • Obtain an accountant’s copy or portable backup
  • Determine the fields that you need
  • Use standard fields for the imports, if at all possible

Then use the create CSV option in the Excel tab of QuickBooks. This enables you to use RDF import files and the use of scripts.  You can manipulate the type of field it is before importation or after. For example:

  • @Ctod(@SpanExcluding(_ENTERED_LAST_MODIFIED_,””).”MM/DD/YYY”) – will convert Entry Date to Date format
  • @Ctod(_DATE_,”mm/dd/yyyy”) – will convert Actual Date to a Standard Date format

Remember, there are some quirks, for example QuickBooks desktop and online have different functions. For example, you may have to work separately with transaction numbers. Remember – if you get stuck, call our Help Desk (888-641-2800, Option4 or [email protected]).

The best part of this process is that it can be repeated. Once you have imported several datasets, you should have a library of standard scripts to access, for common audit functions. To do this, use the customize ribbon option under the File tab, Options in IDEA to have your scripts there for reimporting as necessary. We also have the IDEA Script Vault – never accessed it? Check out this link for details: Explore the IDEAScript Vault and if you don’t find what you need there – call Audimation and we will walk you through the steps.

 

SAP

 

SAP SmartExporter and SAP/AIS both work well with IDEA. For ease of use, we recommend using SmartExporter. This solution was developed with IDEA users in mind and allows you to join tables during the import and save import definitions. Once configured in SAP, it makes your data importation into IDEA for analysis not only fast, but accurate and secure.

IDEA can read files produced by SAP Audit Information Systems directly. The first three lines of every SAP/AIS file consists of the technical field name, field description, and field name from the chosen data dictionary, in that order. IDEA imports the second line, the field description, as the field name to be used in the output database; and imports the first and third line, the technical field and the data dictionary field name, as the field description.

 

ODBC

 
ODBC Data Source Admin - Source Selection Screen

Open Database Connectivity (ODBC) is a standard programming language middleware for accessing information stored in structured files or database management systems (DBMS). This information typically, but not always, resides on database servers located within your network environment. ODBC is available on all Microsoft Windows environments, including your PC or laptop.

ODBC allows users to enter SQL statement to filter the data and imports data from sources such as Oracle, MS SQL and Access. The only thing that needs to be checked in this case is that the matching ODBC Drivers need to be installed on the local pc and on the machine hosting the data for a seamless transfer.

While it may seem difficult at first glance, it is simply a program interface available to IDEA that provides a connection to source systems for data import. In some cases, your IT department may need to provide you the proper security access and credentials as well as giving you the proper drivers for the source system.

 

Application
ODBC Program Interface

MS Access

Oracle

SQL Server

Other

Flat File

ODBC Driver

ODBC Driver

ODBC Driver

ODBC Driver

ODBC Driver

Access Database

Oracle Database

SQL Server Database

Other Database

File

 

The data “lives” in the databases. ODBC simply allows you to extract data from the databases directly into IDEA. ODBC allows you to capture the required data fields, control filtering and establish repeatable processes to enable continuous auditing.

For example, if you request a canned report of journal entries, it may lack critical information about who posted the entry and when it was posted. By moving away from standard reports and using ODBC to extract data directly from the database, you can get the required fields you need.

 

dBASE

 

As to dBASE, it is one of the most common computer formats used for exporting data from computer systems and is the easiest file format to import into IDEA. The only hitch is that you cannot create additional fields if you are importing multiple dBASE files at the same time. Just take your time, import into IDEA and merge, append or join as needed.

 

Reconcile for Accuracy

 

Once information is imported into IDEA, all information must be reconciled to the source data. The most effective way to do this is through “Control Totals.” The “Control Totals” can be used to calculate a grand total for numeric fields. In addition, you can use the “Field Statistics” to view fields of data type: Numeric, Dates and Time. Through this view you can see the grand total as well as the absolute grand total and check for any items that may have come in as errors. If the data does not reconcile, import errors may go undetected. This may include underlying issues with data supplied by the client. You can view both the “Control Total” and the “Field Statistics,” under the Properties window.

A bit of double-checking for accuracy can save you hours of effort and ensure your results are spot-on! Dealing with data imports can be tricky, but we’re here to help. Contact the IDEA Help Desk [888.641.2800] at any point – that’s what we’re here for!


CaseWare IDEA , Importing , Tech Tip



Posted By

By Audimation Team


Related Posts
Gaining an IDEAScript Advantage
Sep 25 CPA Firm's Journey in Becoming Data-Analytics Driven   A group of data analytics enthusiasts at Schneider Downs & Co. asked staff and senior-level ...
Tech Tip: Creating Separate Files for Summarizations
Sep 25 Follow these simple steps to create and export separate files based upon your summarization: Open the original file, in this case SAMPLE-CUSTOMERS Fro...
Tech Tip: Duplicate Identification
Aug 28 Raw data is often incomplete, messy and inconsistent. One of the first steps in the data preparation process is the detection and removal of duplicate records. ...
BROWSER NOT SUPPORTED

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

×