Load 3 & 4 columns from excelund

[Anonymous]

Hi All,

I have a source like 2 excel sheet,which is Sheet_3 and Sheet_4.Refer the below source screenshot.

Sheet_3.xlsx:

Col1	Col2	Col3
a	test	1
b	test2	2
c	test4	3
d	test5	4

Sheet_4.xlsx:

Col1	Col2	Col3	Col4
e	T1	6
f	T2	7	pppp
g	T3	8	ooo
h	T4	3	kkk

When I try to load the both the sheet like Sheet_*.xlsx

Script:

Test1:

LOAD Col1,

     Col2,

     Col3,

     Col4

from

(ooxml, embedded labels, table is Sheet1);

That it throws following error. Can any 1 assist me how ican resolve this fix.

Regards,

Shakila D

[hector_munoz]

Hi Shakila,

Try the following:

Test1:

LOAD @1 AS Col1,

     @2 AS Col2,

     @3 AS Col3,

     @4 AS Col4

FROM

(ooxml, no labels, table is Sheet1, filters(

Remove(Row, Pos(Top, 1))

));

Regards,

H

[Anonymous]

If i put Load * means my data set will get affected.Its not coming properly.

[Anonymous]

Same error came Héctor.

Can you please explain what is the purpose of below the line.

(ooxml, no labels, table is Sheet1, filters(Remove(Row, Pos(Top, 1))));

[vishsaggi]

Look here? It might help.

Load different columns from multiple excel files?

Or Try adding just column name Col4 in your first table and load again.

[Anonymous]

Hi Vishwarath, The attached URL contains the cross table conceptscontainsI don't want to load the 2 excel sheet with the cross table concept. I need to load the both sheets with single load. My Expected output is: Col1 Col2 Col3 Col4 a test 1 b test2 2 c test4 3 d test5 4 e T1 6 f T2 7 pppp g T3 8 ooo h T4 3 kkk If any other possibilities to load the both sheets with single load. Regards, Shakila D

[Anil_Babu_Samineni]

Here, When you try to use * / All. Structure and columns should be same. First sheet you have 3 columns and 2nd sheet you have 4 columns and you used 4 columns with * / All. Here, Qlikview and any other BI Toll won't capable the capture of not in listed field names. Would you re-check to remove 4th column on 2nd sheet then try. And Sheet name should be same for each sheet in excel

[Anonymous]

No, I don't want to put * forthis case.Can you please share the alternative options.

When i load the CSV files with following code its got executed, can any 1 tell the reason why it's executed for CSV files not for .xlsx files.

Table1:

LOAD RowNo() as [File Row],

  Date(Date#(Textbetween(FileName(), 'tier3-excel_', '.csv'), 'MMDDYYYYTT')) as [File Date],

num(Date#(Textbetween(FileName(), 'tier3-excel_', '.csv'), 'MMDDYYYYTT')) as [Date Number],

  @1 as Position,

subfield(@2,'.',1) as [AMI TGB ID],

subfield(@2,'.',2) as [TGB Name],

SubField(@1, '-',1) as [Category ID],

SubField(@1, '-',2) as Category,

     if(len(trim(@4))>0, @2 & @3, @2) as [Description],

     if(len(trim(@4))>0, @4, @3) as Counts

FROM

(txt, codepageis 1252, no labels, delimiter is ',', msq);