IDEA Tech Tip: Understanding a Bad Data File

When importing data files into your data analysis software, it can be hard to know if any of the imported data is invalid. Fortunately, CaseWare IDEA Data Analysis software highlights invalid cells in red, but how do you determine what the invalid entry is? In the following image, we can see that a Date of Birth value is incorrect, but we can’t tell what it’s supposed to be.

If you’d like to follow along in IDEA, you can find the database depicted in this image—named ASCIIDelimitedwithBadDate—in the Samples project as a text file.



In small files, it is relatively easy to return to your source and identify the error.  In large files, however, this simply isn’t practical. You need to be able to view all invalid entries.  


CaseWare IDEA gives you this ability with what is called the “Bad Data” database. To enable this option, select Options from the File menu in IDEA, then click on the System tab and clear the checkbox that reads, “Do not generate a ‘Bad Data’ database when importing data”. Your options should appear the same as in the image below:



Once you’ve done this, when you import a file that has bad data, a second file will be created that reveals exactly where all the bad data is—and what the original incorrect value was. In the following image, we can now see that the Error message in record number 70 that we saw when the file was imported was generated because the date was entered as February 31, 1960.



Because of IDEA’s read-only capabilities (which ensure the integrity of your data), this value can’t be overwritten; however, you are able to append an editable date field to type in the new values. As an alternative, you could also report the error to the client and request updated data for analysis.


Interested in more step-by-step instructions on maximizing your use of CaseWare IDEA? Visit the Resources section of our website, or watch our variety of tutorial videos.



About Jeffery Sorensen, IDEA Product Manager

Jeffery Sorensen is the Product Manager for CaseWare IDEA Data Analysis software. He is a certified instructor with eight years of international consulting experience, and he also has more than 20 years of experience in information technology, holding positions within IT systems development, IT security, IT auditing, project management, and as an IT consultant. He has implemented continuous auditing, monitoring and governance, risk and control (GRC) solutions for clients in both the public and private sectors.  

Connect:  Jeffery Sorensen