Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cristihainic
New Contributor III

How can I wildmatch CSV headers when loading data?

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

1 Solution

Accepted Solutions
Employee
Employee

Re: How can I wildmatch CSV headers when loading data?

There´s no easy way of doing this

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

7 Replies
YoussefBelloum
Esteemed Contributor

Re: How can I wildmatch CSV headers when loading data?

Hi,

Try this:

1.a first load: LOAD *

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

hope it helps

cristihainic
New Contributor III

Re: How can I wildmatch CSV headers when loading data?

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 *

Employee
Employee

Re: How can I wildmatch CSV headers when loading data?

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
Esteemed Contributor

Re: How can I wildmatch CSV headers when loading data?

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

cristihainic
New Contributor III

Re: How can I wildmatch CSV headers when loading data?

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
Esteemed Contributor

Re: How can I wildmatch CSV headers when loading data?

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

Employee
Employee

Re: How can I wildmatch CSV headers when loading data?

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

Community Browser