DataImport

The report is a list of customers, accounts and each investment in the account.

Let’s take a close up look at the data we would like to mine form this report.

We need customer information for each account. The customer data is highlighted in yellow on the above report.

We also need the data for each of the customer’s investments. This data is highlighted in pink, green and blue on the above report. Note that there are multiple lines of investment data for each customer account.

When we are finished we want one line of data in an Excel spreadsheet for each investment. We want each line to contain both the investment data and the customer data as shown below.

First, let’s try opening the text report file in Excel.

The text import wizard is the tool that Microsoft Excel provides for the purpose of parsing lines of text data into separate columns. When we open the file in Excel, the Text Import Wizard as shown above is displayed. The wizard limits us to defining one set of columns to be applied throughout the report.

When imported into Excel, the data is not much more usable than it was in the text file. It would take a lot of work in Excel to solve this problem.

This data mining problem is easily solved with DataImport.

In DataImport, with your report or other file displayed on the screen as shown above, you highlight the data that you want to extract into your spreadsheet or database. The various highlighter colors are a visual confirmation of the data that is selected and how it will be translated. In fact, Highlighter pens were the inspiration for the DataImport user interface.

DataImport quickly unscrambles and translates the data directly into your chosen spreadsheet or database file format.

The file created by DataImport can be immediately opened in Excel, Access or other target application.

When opened in Excel (as shown above) names are parsed into separate columns, numbers, currency and dates are in their correct format. DataImport avoids hours of tedious cleanup work and unlocks data that was previously hopelessly trapped in report fields.