AWK script for data extraction from large CSV files
A script was developed which can be used to extract data from large CSV files. The script was developed primarily for use in the situation of processing large CSV files containing data on patients. The script was needed in this particular environment because:
- The files are too large to open in standard spreadsheet applications (many millions of rows)
- The task is repetitive such that we want to automate
The script shown below is a small sample script to show how it was used. The script is run under the linux OS using the AWK application
awk -F, '$1=="123" { print $1,$2,$10,$45,$46 }' LargeFile.csv > Result.csv
The script above will search a given csv file ("-F,"), in this case “LargeFile.csv”. If the id of the row matches the specified id (" $1=="123" "). This means the id is contained in column number 1 of the “LargeFile.csv” and the id values equals “123”. The specified columns of the row ("{ print $1,$2,$10,$45,$46 }") is output to the Result.csv file, in this case, column 1, which is the id column, along with columns 2,10,45 and 46.
Example
LargeFile.csv
| Id_Col | Val_1 | Val_2 | Val_3 | Val_4 | Val_5 … | |
|---|---|---|---|---|---|---|
| 098 | a26 | b55 | f656 | d468 | 2ak | … |
| 128 | a78 | b95 | g756 | r898 | 3aj | … |
| 098 | a86 | b85 | c856 | d478 | 5ah | … |
| 123 | a35 | b38 | d956 | b478 | 3ed | … |
The output Result.csv would look something like this if we wanted only columns 1,2 and 5
awk -F, '$1=="123" { print $1,$2,$5 }' LargeFile.csv > Result.csv
| Id_Col | Val_1 | Val_5 |
|---|---|---|
| 123 | a35 | 3ed |
You can see how this short script can be very valuable when dealing with large CSV files containing many millions of rows of data which cannot be open using spreadsheet applications and tasks need to be performed routinely.