Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have an excel spreadsheet that contains 2 separate tables one under the other . They are separated by a blank row. The top table has a permanent amount of rows (4 + headers). The structure of the spreadsheet cannot be changed.
I want to load them as 2 separate tables.
I can easily set the beginning of the dataload for the bottom table by setting the correct number in "header is x lines".
Is there a way to limit the amount of rows uploaded from the top table?
example:
Properties | option 1 | Option 2 |
---|---|---|
size | 3 | 5 |
weight | 30 | 30 |
height | 1.5 | 2.3 |
depth | 5 | 6 |
Color Prices | Option 1 | Option 2 |
Blue | 5$ | 10$ |
Red | 6$ | 10$ |
Yellow | 7$ | 10$ |
Write "first 4" without quotes between load and Table name key word.
e.g.
ABC:
First 4
LOAD * FROM [lib://Folder/File.xlsx];
Maybe like this?
load * where key<=4;
load *,RecNo() as key;
LOAD
Properties,
"option 1",
"Option 2"
FROM [lib://source/Limit rows.xlsx]
(ooxml, embedded labels, table is Feuil1);
drop field key;
Hi Anat,
what kind of output do you expect? Give a screenshot as example?
Beck
Thanks for translation
Hi Kaus,
1. yes - i can name ranges in the spreadsheet. how can use the named ranges in the script?
2. yes- the top table will always have the same number of rows. The bottom table will vary. Based on my answer can I limit the number of rows I load from the top table? what's the script expression for this?
Thanks!
The Named ranges will appear as tables when you load the Excel sheet and is the easyest way to fix the problem:
You can also fix it script-wise - below the two areas are loaded sequentially and the number of row's loaded is limited in the first load-statement and the number of header rows are adjusted in the second load-statement:
Write "first 4" without quotes between load and Table name key word.
e.g.
ABC:
First 4
LOAD * FROM [lib://Folder/File.xlsx];
Thanks Shubham! It worked! I never dreamed it would be so easy!!