Using the wizard for CSV and Excel files

The DataMapper wizard for CSV and Excel files helps you create a data mapping configuration for such files. The wizard automatically detects delimiters and extracts all data in one extraction step. It also auto-detects the data type of each field (String, Integer, Boolean, Currency, Float or Date).

The wizard interprets each line in the file as a record. If your data file contains transactional data, you will probably want more lines to go in one record and put the transactional data in detail tables.

The wizard cannot create detail tables. If the file contains transactional data, create the data mapping configuration without a wizard (see Creating a new data mapping configuration), and add the extraction steps yourself.

There are two ways to open a CSV file or Excel file with a wizard: from the Welcome screen or from the File menu.

  • From the Welcome screen

    1. Open the OL Connect Welcome page by clicking the Home icon at the top right, or select the Help menu and then Welcome.

    2. Click New at the left..

    3. Click Data at the right and select CSV or Excel.

    4. Click the Browse button and open the file you want to work with.

    5.  Click Next.

  • From the File menu

    1. In the menu, click File > New.

    2. Click the Data mapping Wizards drop-down and select From CSV/XLSX/XLS File.

    3. Click Next.

    4. Click the Browse button and open the file you want to work with.

    5. Click Next.

Note: Excel files saved in "Strict Open XML" format are not supported yet.

After selecting the file, take a look at the preview to ensure that the file is the right one and the encoding correctly reads the data. Click Next.

For an Excel file you can make the following settings:

  • First row contains field names: Uses the first row of the Excel sheet as headers, which automatically names all extracted fields.

  • Sheet: Select the sheet from which the data should be extracted.

  • Sort on: Allows to select a field on which to sort the data, in ascending (A-Z) or descending (Z-A) order. Note that sorting is always textual. Even if the selected column has numbers, it will be sorted as a text.

For a CSV file the wizard will display the different settings it has detected, allowing you to change them:

  • Encoding: Defines which encoding is used to read the file.

  • Separator: Defines which character separates each field in the file.

  • Comment Delimiter: Defines which character starts a comment line.

  • Text Delimiter: Defines which character surrounds text fields in the file. Separators and comment delimiters within text are not interpreted as separator or delimiter; they are seen as text.

  • Ignore unparseable lines: Ignores any line that does not correspond to the settings above.

  • First row contains field names: Uses the first line of the CSV as headers, which automatically names all extracted fields.

  • Sort on: Allows to select a field on which to sort the data, in ascending (A-Z) or descending (Z-A) order. Note that sorting is always textual. Even if the selected column has numbers, it will be sorted as a text.

Tip: The Sort on option, combined with the Stop data mapping option of the Action step, allows to process only a group of items without having to examine all records. (See also: Action step properties.)

Verify that the data are read properly.

Only the first 10 rows of a data file are analyzed to detect a field's data type. If the data type of values in a column is not consistent throughout the file, an exception may be thrown if a value cannot be interpreted, and data loss may occur if the detected data type is more general than the data type in a subsequent row. For example, if an Integer is detected in the analyzed rows, but a subsequent row contains a Float value, the value of the Float value is truncated to an Integer.
The correct data type can be selected in the field's properties, see Setting the data type.

Finally click Finish. All data fields are automatically extracted in one extraction step.