How to extract data from CSV files with different column names

Sometimes the only difference between CSV files is that the column names are different. Up to OL Connect version 2021.2, you would need to create a different data mapping configuration for each of the files to process them. That is no longer necessary. You can now use a method that allows to extract values from CSV files that have different column names.

Let’s say you have two CSV files that are identical in structure but have different column names. The first CSV file contains 3 columns: “FirstName”,”LastName”,”Title”. The second CSV, produced by your French office, has the same 3 columns, but now the headers are: “Prénom”,”Nom”,”Titre”.

Normally, to extract the value from a field in the “Firstname” column, for example, you would drag the field into the Data Model. But when you do this, the column name is used. You can see this when you click on the field in the Data Model, and then on the Step Properties pane, under Field Definition, change Mode from ‘Location’ to ‘JavaScript’.
The function that appears is: data.extract('FirstName',x);
The first parameter is the name of the column. The second parameter (x) is the row offset, which is the row index of the field relative to the current position of the pointer in your file. If the pointer and the field are on the same row, it will be 0.

In the French CSV file, the same column has a different name – in this case “Prénom” – so this method will not work in the French CSV file.

The solution: extract by index

The solution is to replace the above method with a method that extracts a value from a CSV file by specifying a column index instead of a column name. This method, available as of OL Connect 2021.2, is data.extractByIndex(). It accepts the 1-based index of the column to extract as a parameter, followed by the 0-based row offset.

In this example you could simply change the Javascript to: data.extractByIndex(1,x). (Replace x with the row offset.) This method will extract the data from the first column (1) at the x^th row from the pointer, and it will work with both CSV files.