Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi all
i have a data in excel there so many columns has 'Merge&Center' condition. while loading my value is not coming for all rows whereever it supposed to be. please advise.
Thanks in advance
Try this:
Table:
LOAD col1,
If(Len(Trim(col2)) = 0, Peek('col2'), col2) as col2,
If(Len(Trim(col3)) = 0, Peek('col3'), col3) as col3,
If(Len(Trim(col4)) = 0, Peek('col4'), col4) as col4 ,
If(Len(Trim(col5)) = 0, Peek('col5'), col5) as col5 ,
If(Len(Trim(col6)) = 0, Peek('col6'), col6) as col6 ,
If(Len(Trim(col7)) = 0, Peek('col7'), col7) as col7 ,
col8
FROM
Sample.xlsx
(ooxml, embedded labels, table is Sheet1);
See for example this discussion: Re: Autofill column with one single field
Here is a step by step guide showing how to use the TABLE load wizard:
You will have to repeat these 6 steps for each column and increase the value of (3) with 1 for each column that needs to be filled:
Steps 1 to 3 under is to rename the column headers if necessary:
Try this:
Table:
LOAD col1,
If(Len(Trim(col2)) = 0, Peek('col2'), col2) as col2,
If(Len(Trim(col3)) = 0, Peek('col3'), col3) as col3,
If(Len(Trim(col4)) = 0, Peek('col4'), col4) as col4 ,
If(Len(Trim(col5)) = 0, Peek('col5'), col5) as col5 ,
If(Len(Trim(col6)) = 0, Peek('col6'), col6) as col6 ,
If(Len(Trim(col7)) = 0, Peek('col7'), col7) as col7 ,
col8
FROM
Sample.xlsx
(ooxml, embedded labels, table is Sheet1);
You can by the way - skip the entire Wizard and just type this into the load script:
LOAD
@1, @2, @3, @4
FROM
myExcel.xlsx (ooxml, no labels, table is Sheet1, filters(
Replace(1, top, StrCnd(null)),
Replace(1, top, StrCnd(null)),
Replace(1, top, StrCnd(null))
));
When you have four columns/fields mentioned in line 2 - you will have 3 lines from 5 to 7 (one less than mentioned in line 2).
hi All,
Thanks for the gr8 help.