topic Re: Handle null values in CSV files in QlikView

Handle null values in CSV files

Wed, 10 Jun 2015 11:03:15 GMT

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.

Re: Handle null values in CSV files

qlikmsg4u — Wed, 10 Jun 2015 11:22:40 GMT

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

Re: Handle null values in CSV files

Wed, 10 Jun 2015 11:28:57 GMT

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!

Re: Handle null values in CSV files

maxgro — Wed, 10 Jun 2015 11:39:32 GMT

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

thanks

Re: Handle null values in CSV files

MayilVahanan — Wed, 10 Jun 2015 12:13:26 GMT

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;

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

Trim(FieldName) -- remove empty values.

Re: Handle null values in CSV files

hic — Wed, 10 Jun 2015 12:13:59 GMT

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

Re: Handle null values in CSV files

Wed, 10 Jun 2015 13:43:07 GMT

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.