Slipt excel data to tables in data loader

[theaokii]

Hi guys,

There is question about the data load

I got 1 excel file that contains tables in one sheet like

TableA
ID	Num
a	1
b	2
c	3
TableB
ID	Pnum
a	12
b	13
c	14
TableC
ID	Num
d	4
e	5
f	6

And I want to load it into 2 table seperately as:

[tableA1]

...

[tableB1]

...

Table A will concatenate to table C cause of the similar

The result likes:

TableA1
ID	Num
a	1
b	2
c	3
d	4
e	5
f	6

TableB1
ID	Pnum
a	12
b	13
c	14

Is it possible to do?

Every help would be appreciated

Thank u

[jonathandienst]

Please upload a small sample spreadsheet for more detailed assistance.

[theaokii]

It was my mistake. Thank you for attention 😄

[JordyWegman · Accepted solution]

Hi,

Use this:

Table:
LOAD
    A,
    "B",
	IF(SubStringCount(A,'Table')>0,1,0) 			as _indTable,
    Previous(IF(SubStringCount(A,'Table')>0,1,0)) 	as _indHeader,
    RangeSum(IF(SubStringCount(A,'Table')>0,1,0), peek(_indTables)) as _indTables,
    RowNo() as _indRowNo
FROM [lib://Downloads/Test.xlsx]
(ooxml, no labels, table is Sheet1);

For each Table in FieldValueList('_indTables')
	Trace >> $(Table);
    
    NoConcatenate
    $(Table):
    Load
    A as ID_$(Table),
    B as Num_$(Table)
    Resident Table
    Where _indTables = $(Table) and _indTable = 0 and _indHeader = 0
    ;

next

You only have to decide which tables you want to concatenate, but you can do this.

Jordy

Climber

[theaokii]

Thank u alot. It works greatly
However, there are 2 points i still consider. Could you enlight me?
1, How to put concatenate for the loop. I have tried but it doesnt work

Spoiler

next

Concatenate
Testconcate:
load ID_$(Table) as ID,
Num_$(Table) as Num
Resident Table
where _indTables = 1 and _indTables = 3
;

2, If there are many table names in the sheet which i cant define like Previous(IF(SubStringCount(A,'Table')>0,1,0)), what will i do

I have tried if(not isnull(A) and is null(B)) but idk why it doesnt work

And the concatenate of case 2 if i cant define all the indTables is too complex for me