topic Re: Loading data from Excel file in App Development

Loading data from Excel file

Sharbel — Thu, 11 May 2023 07:24:56 GMT

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

Re: Loading data from Excel file

sergio0592 — Thu, 11 May 2023 07:45:27 GMT

Hello,

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

Re: Loading data from Excel file

Sharbel — Fri, 12 May 2023 11:31:26 GMT

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

Re: Loading data from Excel file

Chanty4u — Fri, 12 May 2023 18:13:23 GMT

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);

Re: Loading data from Excel file

Chanty4u — Fri, 12 May 2023 18:15:22 GMT

For payment column you can try this

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

Re: Loading data from Excel file

edwin — Fri, 12 May 2023 20:22:02 GMT

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

Re: Loading data from Excel file

edwin — Fri, 12 May 2023 20:23:48 GMT

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

Re: Loading data from Excel file

Sharbel — Fri, 12 May 2023 20:32:07 GMT

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 .

Re: Loading data from Excel file

edwin — Fri, 12 May 2023 21:01:42 GMT

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

Re: Loading data from Excel file

Sharbel — Sat, 13 May 2023 12:02:19 GMT

Works Perfect!

Thanks a lot 🙂

Re: Loading data from Excel file

edwin — Mon, 15 May 2023 19:21:17 GMT