Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi to all,
quick question,
qvw that takes data from a folder with 2.000+ csv files, all the 2000 files with the same columns.
One of those columns is a timestamp 2020-05-20T00:00:00
The script very simple, LOAD * from C:\editestfiles\*.csv (txt, utf8 , explicit labels, delimiter is ';', msq);
2 problems:
1.I need to split the time stamp to individuals list boxes , year , month , day
2. need to overpass the problem of explicit labels since all files don't have headers I need to create them.
thanks in advance for the help
Hi,
try below , may be it is help
load *,
Year(Date(Date#(SubField(@21,'T',1),'YYYY-MM-DD'),'DD/MM/YYYY')) as myyear,
Month(Date(Date#(SubField(@21,'T',1),'YYYY-MM-DD'),'DD/MM/YYYY')) as mymonth,
day(Date(Date#(SubField(@21,'T',1),'YYYY-MM-DD'),'DD/MM/YYYY')) as myday ;
LOAD *
FROM
TB01_20200703_173947.xlsx
or
Year((Timestamp#(@21))) as myyear,
month((Timestamp#(@21))) as mymonth,
day((Timestamp#(@21))) as myday
Do you have a sample of the data ?
Hi Alexasso,
1) convert your timestamp to date like doing this:
Date(Floor( Date#(DATE_FIELD)),'MM.DD.YYYY hh:mm') as DATE
2) use date functions like
year(DATE)
moth(DATE)
etc.
BR,
Bernd
there you go , attached the file
column number 21
for the first problem:
Maye be :
load Year(@2) as year,Month(@2) as Month, Day(@2) as Day,* from C:\sampleedi\*.csv (txt, utf8 , explicit labels, delimiter is ';', msq);
for the second :
can you give an exemple of the source file (csv)? and the expected output ?
not working Taoufiq
Hi,
try below , may be it is help
load *,
Year(Date(Date#(SubField(@21,'T',1),'YYYY-MM-DD'),'DD/MM/YYYY')) as myyear,
Month(Date(Date#(SubField(@21,'T',1),'YYYY-MM-DD'),'DD/MM/YYYY')) as mymonth,
day(Date(Date#(SubField(@21,'T',1),'YYYY-MM-DD'),'DD/MM/YYYY')) as myday ;
LOAD *
FROM
TB01_20200703_173947.xlsx
or
Year((Timestamp#(@21))) as myyear,
month((Timestamp#(@21))) as mymonth,
day((Timestamp#(@21))) as myday