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

[M03]

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

A

B

C

D

.

.

n

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

[SerhanKaraer · Accepted solution]

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

[M03]

Thanks for the solution