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.
Solved! Go to Solution.
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 *
Maybe you can try this:
Load TimeStamp1 as Timestamp
Load TimeStamp2 as Timestamp
Load TimeStampN as Timestamp
if it works, you can improve your script with a LOOP
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.
//just for example, if you have 13 timestamp fields
//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$(a) as timestamp
It worked for me with a Sampel data i created
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)
let header = Peek('header');
drop Table t;
load col where col like 'timestamp*';
load SubField('$(header)',';') as col AutoGenerate 1;
for i = 1 to FieldValueCount('col')
let col = FieldValue('col',i);
Load [$(col)] as timestamp
from $(file) (txt, codepage is 1252, embedded labels, delimiter is ';', msq);
drop Table columns;