topic Re: Loading from excel having multiple sheets and each sheets having different number of column and different column names. in App Development

Loading from excel having multiple sheets and each sheets having different number of column and different column names.

M03 — Thu, 09 Feb 2023 06:20:32 GMT

Hi! All,

I am trying to load from Excel which is having multiple sheets (around 350 sheets). Each sheet has different number of columns and few of the sheets column names are also different. I tried below code.

But I am encountering 'Field not found error for few of the sheets'. Kindly help.

Code is as below:

First of all I created one new sheet in the same Excel workbook and listed down name of all the sheets.

For.eg.

Sheet List

Thn i wrote below code in qlik

ExcelRead:

Load

"Sheet List"

From [lib://filepath/sheetname.xlsm]

(ooxml,embedded labels, table is Sheet1);

Table:

Load

Concat(chr(39)&"SheetList"&chr(39),',') as "ConcatList"

Resident "ExcelRead";

Drop Table "ExcelRead";

Let vExcelName = Peek('ConcatList');

Drop Table Table;

For Each SheetName in $(vExcelName)

ExcelObjects:

Load

A as Name,

B as Country,

C as State,

D as Street,

'$(SheetName)' as [SheetList]

From [lib://filepath/sheetname.xlsm]

(ooxml, no labels, Header is 8 lines, table is Sheet1)

where A<> Null();

Kindly guide me with the code

Re: Loading from excel having multiple sheets and each sheets having different number of column and different column names.

SerhanKaraer — Fri, 10 Feb 2023 13:09:45 GMT

Hello M03,

Put this script in a loop for each sheet, you will get your result.

ColumnNames: FIRST 1 LOAD RecNo() as RecNo, * FROM [$(YourExcelFilePath)] (ooxml, no labels, table is $(YourSheetName)); ColumnLabels: CrossTable (Column, Label) LOAD * Resident ColumnNames; DROP Field RecNo, Column; DROP Table ColumnNames; ColumnOrder: LOAD Concat(chr(34) & Label & chr(34), ',', AscOrder) as Fields; LOAD Label, RecNo() as AscOrder Resident ColumnLabels; DROP Table ColumnLabels; LET vFields = Peek('Fields',0,'ColumnOrder'); Drop Table ColumnOrder; YourOutputInMemoryTable: LOAD $(vFields) FROM [$(YourExcelFilePath)] (ooxml, embedded labels, table is $(YourSheetName));

Re: Loading from excel having multiple sheets and each sheets having different number of column and different column names.

M03 — Fri, 10 Feb 2023 23:38:11 GMT

Thanks for the solution