1

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
  1. 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 named xxxxxx_date. In its current form, the code above create folders with names based on full csv file name. the length of xxxxxx varies but the file name format is always the same.

  2. 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
3
  • You can use sub or gsub 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.
    – Bodo
    Feb 24, 2021 at 15:56
  • Thank you for your response.. question updated.
    – dante_t
    Feb 24, 2021 at 16:13
  • Please also show the actual output. Can you have more than one line with the same timestamp?
    – Bodo
    Feb 24, 2021 at 16:21

1 Answer 1

2
gawk -F, '
{ gsub(/"/,"") }

FNR==1{
       delete timestamp;
       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)]
       $3="Info"; $5="Output"; hdr=$1 FS $3 FS $5; 
       yday=strftime("%Y%m%d", systime()-86400);
       fname=FILENAME; gsub(/Thaban_TD_|_.*\.csv$/,"", fname); dirName=fname"_"yday;
       system("mkdir "dirName); next
     }

(substr($1,1,16) in  timestamp){
       cp=$1; gsub(/[-: ]|00$/, "", cp);
       printf("%s%s,%.3f,%.3f\n", hdr ORS, $1, $3, $5)>(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" >(dirName"/"cpx".csv");
             close(dirName"/"cpx".csv")
     }
}' multiple*.csv

Things we updated here are as following:

  • Updated gsub(/Thaban_TD_|_.*\.csv$/,"", fname)

    to remove Thaban_TD_ and _<anything>.csv parts from the fileName

  • Added $3="Info"; $5="Output"

    to rename the column#3 to Info and column#5 to Output for the first line only (NR==1)

  • Updated hdr=$1 FS $3 FS $5

    for header-line we only need columns #1, #3 and #5 (FS is the Field Separator and that is what we defined in -F,)

  • Updated printf("%s%s,%.3f,%.3f\n", hdr ORS, $1, $3, $5)

    to output columns#3 and #5 with 3 decimal points %.3f.

  • Updated print hdr ORS x ",0,0"

    reduce to output only 3 columns.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.