Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
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