Loading data from Excel file

[Sharbel]

hello,

I have the following  row data from excel file:

I want help with script that loads the above data into the following table:

Thanks,

Sharbel

[sergio0592]

Hello,

Qlik will generate script for your Excel load. In script Editor, go to Insert/Load statement/Load from file

[Sharbel]

Hello,

I was thinking of Load script that loads all columns except for columns that starts with the letter "c"  since they are empty.

any help with such a script?

Sharbel

[Chanty4u]

May be you can try this for the columns where your data is null.

LOAD

    Field1,

    Field2,

    ...

    REPLACE(FieldN, null(), 0) as FieldN

FROM [Path\to\your\Excel\File.xlsx]

(ooxml, embedded labels, table is Sheet1);

[Chanty4u]

For payment column you can try this

    IF(IsNull(Payment), '$-', Payment) as Payment

[edwin · Accepted solution]

normally you specify the field names you want to load.  so you may have more requirements than just doing this for a single XLS.  however, to answer you specific question - and this may give you an idea for what you really intend.

load all the fields in the XLS, then iterate through the fieldnames and eliminate what you dont need (especially if Qlik Sense loads blank fieldnames - it will probably show up as the column letter); as you iterate through the field names, you collect only the field names you need in a variable, then use that variable to either reload your table.  there can be multiple variations - like collect the field names you dont need and then do a drop field command, or reload from resiednt only the fields you want.  here is the idea using the first scenario:

tmpTable:
load
    *
FROM [lib://....xlsx]
(ooxml, embedded labels, table is Sheet1);

set vFields='';
For f = 1 to NoOfFields('tmpTable')
if Upper(left(FieldName($(f), 'tmpTable'),1))<>'C' then
	let vFields='$(vFields)' & ',' & FieldName($(f), 'tmpTable');
end if;
Next f
let vFinal=right('$(vFields)',len('$(vFields)')-1);
drop table tmpTable;
Table:
load 
	$(vFinal)
FROM [lib://....xlsx]
(ooxml, embedded labels, table is Sheet1);

hope that generates ideas

[edwin]

if you plan to do this for multiple tables, maybe a configuration table that holds the table name, the field names and the XLS file would be a better solution.  but then again, it really depends on what you ultimately want to achieve

[Sharbel]

Note that the number of "empty " columns may vary from excel file to another, so maybe the load  script should be dynamic in the sense that it runs over all columns in certain table   and drops columns that meet certain criteria - in my case the criteria would be no data in specific column/s .

[edwin]

If you don’t want to use a configuration file. Then you can load all your xls files. Then loop through all the tables and all the fields and drop all the fields that satisfy your criteria 

I think a drop field would be simpler. Just collect all the fields to drop

[Sharbel]

Works Perfect!

Thanks a lot 🙂