the following code extracts data from csv files contained in a folder, creates new folders (based on filename and date), creates and stores multiple csv files in the said folders. more info on this is here how-to-split-csv-file-and-create-multiple-csv-files-based-on-a-column.
gawk -F, '
BEGIN{ start=strftime("%Y %m %d 00 00 00", systime()-86400);
for(min=0; min<1440; min++)
timestamp[strftime("%F %H:%M", mktime(start)+min*60)]
}
{ gsub(/"/,"") }
FNR==1{
hdr=$0; yday=strftime("%Y%m%d", systime()-86400);
fname=FILENAME; sub(/.csv$/,"", fname); dirName=fname"_"yday;
system("mkdir "dirName); next
}
(substr($1,1,16) in timestamp){
cp=$1; gsub(/[-: ]|00$/, "", cp);
print hdr ORS $0 >(dirName"/"cp".csv");
close(dirName"/"cp".csv");
delete timestamp[substr($1,1,16)] }
ENDFILE{ for (x in timestamp){
cpx=x; gsub(/[-: ]/, "", cpx);
print hdr ORS x ",0,0,0,0" >(dirName"/"cpx".csv");
close(dirName"/"cpx".csv")
}
}' multiple*.csv
how can I use part of a filename to create a folder (within awk) e.g. filename is
Thaban_TD_xxxxxx_Forms1.csv
and want to create a folder namedxxxxxx_date
. In its current form, the code above create folders with names based on full csv file name. the length ofxxxxxx
varies but the file name format is always the same.I'd also like to extract Timestamp, Data2, Data4 columns, rename 'Data2' to 'Info' and 'Data4' to 'Output', and then round off data in Info and Output columns to 3 decimal digits. in the same code
input file: Thaban_TD_xxxxxx_Forms1.csv
TIMESTAMP,Data1,Data2,Data3,Data4
"2021-01-03 00:00:00",80953,3.243183,2.943338,358.0123
"2021-01-03 00:01:00",80954,2.173187,1.990327,344.5851
...
"2021-01-03 23:59:00",80957,4.04172,3.82053,355.5481
"2021-01-04 00:00:00",80955,3.700353,3.593842,346.2665
...
"2021-01-04 23:59:00",80956,3.125094,2.922542,350.9915
"2021-01-05 00:00:00",80957,4.04172,3.82053,355.5481
...
"2021-01-05 23:59:00",80956,3.125094,2.922542,350.9915
etc...
This is how I'd like my output to be
Output file 202101030000.csv
inside created folder xxxxxx_20210103
TIMESTAMP,Info,Output
2021-01-03 00:00:00,3.243,358.012
...
Output file 202101032359.csv
inside created folder xxxxxx_20210103
TIMESTAMP,Info,Output
2021-01-03 23:59:00,4.042,355.548
sub
orgsub
to remove unwanted parts, e.g.Thaban_TD_
, from the file name in a similar way as you remove.csv
. To me it is not clear how the second question is related to the code. I suggest to edit your question and show some example input, the corresponding actual output and the expected output.