Importing Data Into IDEA

Tips & Resources for Working With Complex Data Sets

The first step after acquiring the right types of data is importing it into IDEA. While IDEA offers superior importing features, some data can be challenging to import. Here are some tips and resources to assist you with importing data into IDEA.

IDEA’s Import Assistant can accept and/or connect to a variety of data types including:

The Drag-and-Drop feature saves time by deciphering the data you’re importing and providing the option to preview data before the import is complete. As always, if you get stuck, contact the IDEA Help Desk for assistance.

Report Reader

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.

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 either 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. Step by step guide to re-using Import Templates

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.

Watch the Tackling Tough Imports video, which covers:

• Standard Layer: Data Saved from QuickBooks to Excel – Working with multiple and multiple formats
• Using a Floating Layer/Floating Trap – Data in rows or lines doesn’t line up properly
• Multi-line Trap – Each detail line has a “header” looking block with one or more lines of detail

User Example:

 
An IDEA user had a 200-page print report, which required an analyst two weeks each month to import into IDEA. The multi-step manual process included scanning the documents, converting them to PDFs, copying and pasting them into Excel, them importing them into IDEA. Audimation Services stepped in to help them find a more efficient way to import their data into IDEA. They used OCR software and IDEA’s Report Reader feature to capture up to 80 percent of the data. IDEA’s editable fields feature was used to perform beginning value matches and match column data to look for errors that required manual correcting. Using the OCR tool and IDEA’s editable fields feature reduced the 2-week process into just two days.

Importing HTML Reports into IDEA

1. Open <filename>.html with MS Word
2. From the Page Layout tab, select Margins and then Custom Margins
3. In the resulting dialog enter “0" for each margin, select Landscape as the orientation, and click OK
4. On the resulting error dialog select Ignore to ensure that your selections stay in place
5. Click “Save As” and then select PDF as the output type
6. Give the file a name and save it to your working folder

Now that you have the file in PDF format, you can use Report Reader to bring the file in to IDEA.

Free IDEAScript

There are more than 80 free IDEAScripts available in the Marketplace to help you with complex processes. The Import Multiple Files script helps you import multiple files of the same type into IDEA for significant time savings. Find instructions on how to access these scripts within the Marketplace.

NEW Audimated App – IS Multi File Import

The IS Multi File Import allows you to import multiple files into IDEA in just one pass. The application will handle all the most common IDEA file types such as dBase, Microsoft Access, Microsoft Excel, Print Report, EBCDIC, Text Fixed Length, Text Delimited and XML.

It allows you to import one or all the worksheets in an Excel spreadsheet and one or all the tables in an Access database. For file types that need an import definition, these files need to be all in the same format and use the same import definition that needs to be defined before starting the import.

It also adds on additional features that could be useful when importing a file such as including the file name in a field, including the record number in a field and automatically generating field statistics.

Here are instructions on how to access the Marketplace.

Importing Services

Audimation Services specializes in helping clients with complex data imports. Our team of experts can merge files from different formats and databases, cleanse and normalize the data, then bring it into IDEA for analysis. We work with large and complex data sets every day to help clients through the initial, and often complex, step in preparing data for analysis. Some perks of working with us include:

• Ironclad non-disclosure agreements and experience handling sensitive data
• Secure file sharing to ensure data safety and integrity
• Access to OCR software to turned scanned paper PDFs into IDEA-read data
• Confirmation of control totals to ensure data was imported correctly
• Results delivered to your specifications, whether an IDEA output file or Excel