Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends,
Is there a way to read only few columns or set of columns from given excel. I have excel having 50+ columns but need to read only first 25 columns. How can we achieve this ?
Continuing the idea with CROSSTABLE, it may look as fllws:
RawData:
CROSSTABLE (Field, Value, 1) LOAD * INLINE [Item, 1/1/2016, 2/1/2016, 3/1/2016, A, N, 1
A, 1, 2, 3, 4, 5, 7
B, 11, 12, 13, 15, z, B];
DataCleansed:
LOAD
*
WHERE
LEN(TRIM(Date));
LOAD
DATE(DATE#(Field, 'D/M/YYYY')) AS Date,
Field,
Item,
Value
RESIDENT
RawData;
DROP TABLE RawData;
Have Fun!
This will not restrict columns to be loaded. Rows can be restricted using RecNo()
Hi Vineeth,
do we have any command to read first N columns ? I know about first N Rows
The concept is:
First read all columns, as they are in the Excel, normalize the table and then inspect them (and throw away).
you can: with RECNO():
LOAD * FROM .... WHERE RECNO() = 1;
This might give you a table with all headers only.
Then you may loop through the contents with NOOFFIELDS() and FIELDNUMBER() to read the content and decide, what to do.