Read range of columns in Excel

[manojkulkarni]

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 ?

[prieper]

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!

[manojkulkarni]

This will not restrict columns to be loaded. Rows can be restricted using RecNo()

[manojkulkarni]

Hi Vineeth,

do we have any command to read first N columns ? I know about first N Rows

[prieper]

The concept is:

First read all columns, as they are in the Excel, normalize the table and then inspect them (and throw away).

[prieper]

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.