How to handle CSV columns with value "NULL" as real NULL on the destination using Informatica Data Engineering?

Question

Is there any option to specify to Informatica that is should consider the text "NULL" on any text file column as real NULL values?

I would want to believe that I would not be required to perform a comparison transformation on each and every column.

The file is NOT a fixed-width delimited.

Would appreciate if someone had gone through this experience. Thank you

Answer 1

When you have a CSV file - it makes things quite simple - change the input type to Command as mentioned in the docs and use sed to read the file:

sed 's/NULL//' sed_test.csv

Here's a sample file that I've created. Below's the content of the file displaed by $ head sed_test.csv command:

George Washington,NULL,NULL, 1789-1797
John Adams,NULL,NULL, 1797-1801
Thomas Jefferson,NULL,NULL, 1801-1809
James Madison,NULL,NULL, 1809-1817
James Monroe,NULL,NULL, 1817-1825
John Quincy Adams,NULL,NULL, 1825-1829
Andrew Jackson,NULL,NULL, 1829-1837
Martin Van Buren,NULL,NULL, 1837-1841
William Henry Harrison,NULL,NULL, 1841
John Tyler,NULL,NULL, 1841-1845

Now, here's the same using sed 's/NULL//g' sed_test.csv | head:

George Washington,,, 1789-1797
John Adams,,, 1797-1801
Thomas Jefferson,,, 1801-1809
James Madison,,, 1809-1817
James Monroe,,, 1817-1825
John Quincy Adams,,, 1825-1829
Andrew Jackson,,, 1829-1837
Martin Van Buren,,, 1837-1841
William Henry Harrison,,,

As you see, the columns are there, values are not there anymore.