1

I have a very messed up CSV file where the header looks the following:

maindata.csv

time;area;measurement1;measurement2;measurement3;.....measurement12

I was able to split the single large file based on measurement names separately using the following gawk command format in order to keep each separate file in the following CSV Format:

measurement1.csv -> has csv header [time,area,measurement1]
measurement2.csv -> has csv header [time,area,measurement2]
....
measurement12.csv -> has csv header [time,area,measurement12]

gawk command:

gawk -F';' '{print $1","$2","$X}' maindata.csv > /splitdata/measurementX.csv

where X is the column number of the respective measurement.

I had to do this manually everytime on the terminal, and wanted to know if I could actually somehow extract the measurement column name using gawk and replace the above command with some variable than can automatically be used to name the file measurementX.csv

Example

gawk -F';' '{print $1","$2","$3}' maindata.csv | head -n 1 > splitdata/measurement<get the column 3 name here>.csv

P.S. for brevity the column names are measurementX. In practice, they can be temperature, or pumpmotor etc.

3

measurements.awk:

BEGIN{FS=";";OFS=","}
{
    for (X=3;X<=NF;X++){
        if (NR==1){name[X]=$X}
        print $1,$2,$X > name[X]".csv"
    }
}

See that NR==1 is only true for the header. So we save the measurements names in the array name, and then proceed to print each set [time,area,measurement] in the corresponding file.

$ gawk -f measurements.awk file
$ cat file
time;area;temperature;pumpmotor;diameter
1;2;3;4;5
6;7;8;9;10
$ cat diameter.csv 
time,area,diameter
1,2,5
6,7,10
$ cat temperature.csv 
time,area,temperature
1,2,3
6,7,8
$ cat pumpmotor.csv 
time,area,pumpmotor
1,2,4
6,7,9
| improve this answer | |

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.