lunedì 10 ottobre 2016

ODI Tip: Generating flat files with spaces in column names

I had a requirement of generating a .csv file with header out of a database table.
I had created the model as follows:
  • File Format: Delimited
  • Record Separator: Unix  
  • Field Separator: , (comma)
In the interface, I had placed the file as Target Datasource and used 'IKM SQL to File Append' with the following options:

Unfortunately, the header columns were truncated at the first space and adding double quotes did not help. The column header were all truncated after the first space found.
After investigating, I came to the conclusion that there is was no quick or easy way to deal with spaces. I guess the only way is creating a custom version of the IKM.
Therefore, since I have very little time, I have replaced the spaces with underscores and added a OdiOSCommand in the package to replace the underscores with spaces in the output file.

ODIOSCommand is a more flexible version of OSCommand and allows you to invoke a sed command to do the replacement in place (-i) of the underscores with spaces.

sed -i 's/_/ /g' <filename>

Nessun commento:

Posta un commento