Disable automatic setting of the field data type when working with files

Overview

When Etlworks reads data in a specific data exchange Format such as CSV, XML, JSON,  or other similar, it samples the data and sets the column data type using the best estimate. For example, if the value of a column in several sequential rows appears to be numeric, the system sets the data type for this column to numeric for the rest of the data set.

It works very well, but in some rare cases, the string columns are wrongly recognized as numeric, which is causing issues when loading data into the destination (typically a database).

Example

Let's consider a data set below:

id, first_name, last_name
1, Joe, Doe
2, Jane, Doe
AB, Sima, The Dog

Based on the data in the first 2 records, the system sets the data type for the column id to numeric and automatically creates a table in the database with this data type. When it's trying to load the third row, with id set to AB, it fails with an exception because the database expects that column id has a numeric data type.

Process

The recommended solution is to manually set the column data type in the Mapping.

In some cases, specifically, when loading data in some sort of staging table, it is easier to configure the data exchange Format with the property All fields are strings enabled. In this case, the system will create all fields with the string (VARCHAR) data type. The length of the field will be automatically set to the maximum allowed length for the VARCHAR fields for the specific database.