Load a field only if exists , from csv

[#1]

Hi,

I have a csv and I want to load some data, if some fields exists.

The problem is an error: it can't load if        in else side of if clause    it is used a field that don't exist.

Have an ideea how to make it works?

Thanks!

COLSIN:

FIRST 1 LOAD *

FROM

..file.csv;

Tab1:

LOAD

rowno() AS ORD,

if(FieldNumber('field 1','COLSIN')>0,[field 1])     AS [_FIELD1],

if(FieldNumber('field 2','COLSIN')>0,[field 2])     AS [_FIELD2]

..

FROM

..file.csv;

DROP TABLE COLSIN;

[Gysbert_Wassenaar · Accepted solution]

Something like this maybe:

COLSIN:

FIRST 1 LOAD *

FROM

..file.csv;

for f = 1 to NoOfFields('COLSIN')

    LET vFieldName = FieldName(f,'COLSIN');

    if match('$(vFieldName)', 'MyField1','MyField2','MyField3', ..etc) then

        LET vFieldList = '$(vFieldList)' &',' & '$(vFieldName)';

    end if

next

LET vFieldList = mid('$(vFieldList)',2);

Tab1:

LOAD

rowno() AS ORD, MyFieldX, MyFieldY, ...etc, $(vFieldList)

FROM

..file.csv;

DROP TABLE COLSIN;

Set vFieldName=;

Set vFieldList=;

[#3]

You're on the right track. First you need to obtain the list of fields available then construct a string of the fields that you want. Finally, load the data.

Try something like:

COLSIN:

FIRST 1 LOAD *

FROM

file.csv;

SET fields = "always_included_field1,always_included_field2,";

IF (FieldNumber('field 1','COLSIN')>0) THEN

  LET fields = fields & ",[field 1] AS [_FIELD1]"

END IF

IF (FieldNumber('field 2','COLSIN')>0) THEN

  LET fields = fields & ",[field 2] AS [_FIELD2]"

END IF

DROP TABLE COLSIN;

COLSIN:

LOAD $(fields)

FROM

file.csv;

[#4]

HI ,

      PLease try the below code and No Hard Code is required here .

Every thing can be held in automated way

COLSIN:

LOAD  *

FROM

[..\TIME.xlsx]

(ooxml, embedded labels, table is Sheet3);

Let DList='';

Let g = NoOfFields('COLSIN');

For h = 1 to g

   let DList = DList & chr(39)& FieldName(h,'COLSIN') & chr(39) & if(h<>g,',');

  Next

FieldList:

LOAD * Inline //Your Required field Names list

[

FieldName

Field1

Field3

];

Let v_attr_list ='';

    For i=0 to NoOfRows('FieldList')-1

        Let v_attr_list = v_attr_list & chr(39)& Peek('FieldName',i,'FieldList') & chr(39) & if(i<>j,',');

    Next

Let i=0;

For Each attr in $(DList)

Let index = MixMatch( '$(attr)' , $(v_attr_list) );      

           IF $(index)>0 then

             if i=0 then 

               Result:

               LOAD

                 RowNo() ,

                    $(attr)

                  Resident COLSIN;

                  let i=i+1;

             else

              Join(Result)

               LOAD

                  RowNo(),

                   $(attr)

                 Resident COLSIN;

               endif;     

            ENDIF;

NEXT      

    DROP Table COLSIN;

Hope it helps you

If there is anything please let me know.

.

[#5]

Job done,

Lot of thanks to all ! (I marked correct in cronological order because all responses were helpfull and corect)