Handle null values in CSV files

[#1]

Hello,

I'm trying to extract data from a CSV file with fixed width.

I found au NUL symbol in the file which causes a shift in the extraction and gives splitting errors.

Below a sample screen shot of the problem :

NB : this screen shot was taken from Notepad++ and I manually added the red lines to explain this issue.

Does anyone has faced a similar problem ?

Can I handle it in QlikView script or should I ask for a clean file ?

Thanks in advance.

[qlikmsg4u]

You can do it in qlikview by using transformation step by specifying fill condition. You can replace null value with some other value

[#3]

Hi Ghassen,

Its always bet to work with clean data but as that is not always an option it might be worth building a temporary loading state where you handling this kind of issues by using the replace function (if the null always should be replaced with a spec thing ex. a 0).

Good luck!

[maxgro]

could you post some lines of the file (with NUL)

thanks

[MayilVahanan]

Hi

If the value NUL is seen in qlikview report itself, then u can replace that NUL with replace function.

May be try like this

Trim(Replace(FieldName, 'NUL','')) as FieldName;

Or

If NUL is not seen then but empty values are there, then u can try like this

Trim(FieldName) -- remove empty values.

[hic]

You can solve it this way:

Load

  Left(Line,3) as Field1,

  Mid(Line,4,4) as Field2,

  Mid(Line,8,3) as Field3

  ;

LOAD Replace( @1:n, 'NUL', '') as Line

  FROM [File.csv] (fix, codepage is 1252);

HIC

[#7]

Thank you all for your answers

I just got a clean file !

I promise you i will test your solutions when I'll have some free time, it's a very intersting challenge.