Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am pretty sure if this question has already been asked in some sort in other topics, but apparently, I have not gotten the information needed to solve my problem, so I am hoping someone in here can help me solve the problem, and save some time.,
I want to import all files from a folder and convert the file into fields that I can use to analyze the amount and content of the files in that folder.
A typical file would look like this -> 0006037356_50548136_valuereport_20170831023203_2105.csv
I then want to extract the information from this file name so I have fields with
Right now I am doing the extraction by using this load.
LOAD
FileName() as Filename,
Mid(FileName(), 1, 10) as import_elvaco_enhed,
Mid(FileName(), 12, 😎 as meter_no,
Mid(FileName(), 33, 😎 as import_date,
Mid(FileName(), 41, 6) as import_time_stamp
FROM [lib://Mobistyle/*]
(txt, codepage is 28591, embedded labels, delimiter is ';', msq);
I want to do the following.
I only want to look at the filename but for some reason when loading the data, I get all the millions of lines included in the files, and even if the data load uses the time, and displays that millions of lines have been imported, I am not able to use any of that information?
(Not that I need it, just think it a waste of time and storage)
The following should cover most of your needs:
load
*,
filebasename() as F1,
num(subfield(filebasename(), '_', 1)) as F2,
num(subfield(filebasename(), '_', 2)) as F3,
date(floor(timestamp#(subfield(filebasename(), '_', 3), 'YYYYMMDDhhmmss'))) as F4,
time(frac(timestamp#(subfield(filebasename(), '_', 3), 'YYYYMMDDhhmmss'))) as F5,
filesize() as F6
from YourCSV;
If you don't want to load the content of these files else just their meta-data you will need a for each loop over dirlist/filelist. You will find a good example within the help by for each and here: Loops in the Script.
Of course each field which is created will consume some ressources but Qlik stored only the distinct fieldvalues of a field and therefore you don't worry about it.
- Marcus
The following should cover most of your needs:
load
*,
filebasename() as F1,
num(subfield(filebasename(), '_', 1)) as F2,
num(subfield(filebasename(), '_', 2)) as F3,
date(floor(timestamp#(subfield(filebasename(), '_', 3), 'YYYYMMDDhhmmss'))) as F4,
time(frac(timestamp#(subfield(filebasename(), '_', 3), 'YYYYMMDDhhmmss'))) as F5,
filesize() as F6
from YourCSV;
If you don't want to load the content of these files else just their meta-data you will need a for each loop over dirlist/filelist. You will find a good example within the help by for each and here: Loops in the Script.
Of course each field which is created will consume some ressources but Qlik stored only the distinct fieldvalues of a field and therefore you don't worry about it.
- Marcus
Buityful work
It works like a charm, and I now have a bunch of new tools (expressions) to play with.
THANKS!
I was to existed before testing the date part of the load
The date conversion only gave me Null values.
load
*,
filebasename() as Filename,
num(subfield(filebasename(), '_', 1)) as import_elvaco_meter_no,
num(subfield(filebasename(), '_', 2)) as meter_no,
date(floor(timestamp#(subfield(filebasename(), '_', 3), 'YYYYMMDDhhmm'))) as import_date,
time(frac(timestamp#(subfield(filebasename(), '_', 3), 'YYYYMMDDhhmm'))) as import_time,
filesize() as import_filesize
FROM [lib://Kildeparken/*]
(txt, codepage is 28591, embedded labels, delimiter is ';', msq);
You changed the format-code for timestamp#() which is a converting-function and not a format-function. Therefore try it with:
date(floor(timestamp#(subfield(filebasename(), '_', 3), 'YYYYMMDDhhmmss')), 'DD.MM.YYYY') as F4
time(frac(timestamp#(subfield(filebasename(), '_', 3), 'YYYYMMDDhhmmss'))' 'hh:mm') as F5
- Marcus
I figured it out. It works in both ways.
The reason it did not work, was because it was looking at the _valuereport_
1 2 3 4
0006037356_50548136_valuereport_20170831023203_2105.csv
date(floor(timestamp#(subfield(filebasename(), '_', 3), 'YYYYMMDDhhmmss')), 'DD.MM.YYYY') as import_date,
time(frac(timestamp#(subfield(filebasename(), '_', 3), 'YYYYMMDDhhmmss')), 'hh:mm') as import_time,
Thank you for your help, it NOW works like a charm, and actually did to start with