Force load of blank cells from Excel

[#1]

Hi!

I'm having a problem with loading a block of data from Excel into QV.

I'm loading data from multiple files, and most of the time i'd have something like:

Year	A	B	C
2009	10	20	13
2010	43	24	52
2011	23	52	34

but sometimes I'd get:

Year	A	B	C
2009
2010
2011

...where the users didn't fill in the data (maybe they didn't have any).

In this case I still need to read it in.

I have a table, where one of the fields is assigned a static value:

...

    'Label1' as [Label],

    @2 as ,

    @3 as ,

...

and later I join it with another table, relying on the [Label] for the correct join.

The problem appears, when I have blank cells, the [Label] never gets assigned the value "Label1" for some reason.

I don't care if the cells are blank, NULL is fine with me, but QV doesn't even store a null character as the value for these cells.

I'm lost, any help is appreciated.

Thanks!

[#2 · Accepted solution]

You could try a formula in the script  for each field like:

...

    'Label1' as [Label],

if(isnull(@2) or @2='','Null',@2) as ,

if(isnull(@3) or @3='','Null',@3) as ,

...

This might populate the blank cells with at least the word Null

[#3]

Perfect. Thank you marcsliving!

I did try the isnull(), but not the @2=''. Apparently the cells were read in as blank strings and not NULL.

Thanks