How can I wildmatch CSV headers when loading data?

[Anonymous]

Hi,

When loading data from a CSV file, I want to do something like "LOAD timestamp*" to load all CSV columns named "timestamp1", "timestamp2" etc. from the file. I haven't found anything that simple yet. Is there a way to do this? If not, what would be the simplest workaround in your opinion?

The reason I need wildmatching is because I load from multiple files and each has a different number of columns such as "timestamp2" or "timestamp36" etc. I don't know the exact number of columns with the same name (and different numbers) for each file, since that number varies from file to file.

Thanks,

Chris

[YoussefBelloum]

Hi,

Try this:

1.a first load: LOAD *

2. Drop other fields (if necessary): DROP FIELD X FROM TABLE X;

hope it helps

[Anonymous]

Sure, but I want all those columns named almost the same ("timestamp1", "timestamp21" etc) to be loaded AS a single name, so I can use them in visualizations under a single name. I can't do that if I simply load everything with LOAD *

[Clever_Anjos · Accepted solution]

There´s no easy way of doing this

you can play with load * and after with fieldname() and some logic to rename your fields

[YoussefBelloum]

Maybe you can try this:

TimeStamp:

Load  TimeStamp1 as Timestamp

from X;

concatenate

Load TimeStamp2 as Timestamp

from x;

concatenate

Load TimeStampN as Timestamp

from x;

if it works, you can improve your script with a LOOP

[Anonymous]

Youssef, thanks for answering. However, as I said in my initial post, I don't know the exact number of columns with similar names in each of the file. I cannot write individualized Load scripts for all the files.

[YoussefBelloum]

Try this:

//just for example, if you have 13 timestamp fields

test:

LOAD 

     timestamp1,

     timestamp2,

     timestamp3,

     timestamp4,

     timestamp5,

     timestamp6,

     timestamp7,

     timestamp8,

     timestamp9,

     timestamp10,

     timestamp11,

     timestamp12,

     timestamp13

FROM x;

//just to have the number of the timestamp fields

LET vFieldNo = NoOfFields('test');

DROP Table test;

//loop on the timestamp fields

for a=1 to $(vFieldNo)

Timestamp:

LOAD aaa,

     timestamp$(a) as timestamp

FROM x;

NEXT a

It worked for me with a Sampel data i created

[Clever_Anjos]

Maybe this code can point you in the right direction

for each file in FileList('C:\tmp\278912\*.csv')
     t: // getting first line of file
     LOAD [@1:n] as header
     FROM $(file)(fix, codepage is 1252)
     Where RecNo()=1;

     let header = Peek('header');
     drop Table t;

     columns:
     load col where col like 'timestamp*';
     load SubField('$(header)',';') as col AutoGenerate 1;

     for i = 1 to FieldValueCount('col')
          let col = FieldValue('col',i);
          WholeBase:
          Load [$(col)] as timestamp
          from $(file) (txt, codepage is 1252, embedded labels, delimiter is ';', msq);
     next
     drop Table columns;
next