Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Please upload a small sample spreadsheet for more detailed assistance.
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
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
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