1. Overview

In this tutorial, we’ll look at how we can parse values from Comma-Separated Values (CSV) files with various Bash built-in utilities.

First, we’ll discuss the prerequisites to read records from a file.

After that, we’ll check different techniques to parse CSV files into Bash variables and array lists.

Finally, we’ll discuss how we can use a few third-party tools for advanced CSV parsing.

2. Prerequisites

Let’s briefly review the standards defined for CSV files:

  1. Each record is on a separate line, delimited by a line break.
  2. The last record in the file may or may not end with a line break.
  3. There may be an optional header line appearing as the first line of the file, with the same format as regular record lines.
  4. Within the header and records, there may be one or more fields separated by a comma.
  5. Fields containing line breaks, double quotes, and commas should be enclosed in double-quotes.
  6. If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote.

CSV files containing records with commas or line breaks within quoted strings are not in our scope. However, we’ll discuss it briefly in the last section of the tutorial.

Let’s now set up our standard sample CSV file:

$ cat input.csv
SNo,Quantity,Price,Value
1,2,20,40
2,5,10,50

2.1. Reading Records From a File

We’ll now run an example to read records from our input file:

#!/bin/bash
while read line
do
   echo "Record is : $line"
done < input.csv

Here we used the read command to read the line-break (\n) separated records of our CSV file. Let’s check the output from our script:

Record is : SNo,Quantity,Price,Value
Record is : 1,2,20,40
Record is : 2,5,10,50

As we can notice, there’s a complication: The header of the file is also getting processed. So, let’s dive into the solutions.

2.2. Ignoring the Header Line

Let’s run another example to exclude the header line from the output:

#!/bin/bash
while read line
do
   echo "Record is : $line"
done < <(tail -n +2 input.csv)

Here, we used the tail command to read from the second line of the file. Subsequently, we passed the output as a file to the while loop using process substitution. The <(..) section enables us to specify the tail command and let Bash read from its output like a file:

Record is : 1,2,20,40
Record is : 2,5,10,50

We’ll now try another way to achieve the same result:

#!/bin/bash
exec < input.csv
read header
while read line
do
   echo "Record is : $line"
done 

In this approach, we used the exec command to change the standard input to read from the file. Later, we used the read command to process the header line. Subsequently, we processed the remaining file in the while loop.

3. Parsing Values From a CSV File

So far, we’ve been reading line-break-separated records from CSV files. Henceforth, we’ll look at methods to read the values from each data record.

3.1. From All Columns

Let’s check a way to store the field values as we loop through the CSV file:

#! /bin/bash
while IFS="," read -r rec_column1 rec_column2 rec_column3 rec_column4
do
  echo "Displaying Record-$rec_column1"
  echo "Quantity: $rec_column2"
  echo "Price: $rec_column3"
  echo "Value: $rec_column4"
  echo ""
done < <(tail -n +2 input.csv)

Note that we are setting Input Field Separator (IFS) to “,”  in while loop. As a result, we can parse the comma-delimited field values into Bash variables using the read command.

Let’s also check the output generated on executing the above script:

Displaying Record-1
Quantity: 2
Price: 20
Value: 40

Displaying Record-2
Quantity: 5
Price: 10
Value: 50

3.2. From the First Few Columns

There can be instances where we’re interested in reading only the first few columns of the file for processing.

Let’s demonstrate it with an example:

#! /bin/bash
while IFS="," read -r rec_column1 rec_column2 rec_remaining
do
  echo "Displaying Record-$rec_column1"
  echo "Quantity: $rec_column2"
  echo "Remaining fields of Record-$rec_column1 : $rec_remaining"
  echo ""
done < <(tail -n +2 input.csv)

In this example, we could store the value in the first and the second fields of the input CSV in rec_column1 and rec_column2 variables, respectively. Notably, we stored the remaining fields in the rec_remaining variable.

Let’s look at the output of our script:

Displaying Record-1
Quantity: 2
Remaining fields of Record-1 : 20,40

Displaying Record-2
Quantity: 5
Remaining fields of Record-2 : 10,50

3.3. From Specific Column Numbers

Again, we’ll use process substitution to pass only specific columns to the while loop for reading. Additionally, to fetch those columns, we’ll utilize the cut command:

#! /bin/bash
while IFS="," read -r rec1 rec2
do
  echo "Displaying Record-$rec1"
  echo "Price: $rec2"
done < <(cut -d "," -f1,3 input.csv | tail -n +2)

As a result, we could parse only the first and the third columns of our input CSV.

Let’s validate it with the output:

Displaying Record-1
Price: 20
Displaying Record-2
Price: 10

3.4. From Specific Column Names

There can be situations where we might need to parse the values from CSV based on column names in the header line.

Let’s illustrate this with a simple user-input-driven script:

#! /bin/bash
col_a='SNo'
read -p "Enter the column name to be printed for each record: " col_b
loc_col_a=$(head -1 input.csv | tr ',' '\n' | nl |grep -w "$col_a" | tr -d " " | awk -F " " '{print $1}')
loc_col_b=$(head -1 input.csv | tr ',' '\n' | nl |grep -w "$col_b" | tr -d " " | awk -F " " '{print $1}')
while IFS="," read -r rec1 rec2
do
  echo "Displaying Record-$rec1"
  echo "$col_b: $rec2"
  echo ""
done < <(cut -d "," -f${loc_col_a},${loc_col_b} input.csv | tail -n +2)

This script takes col_b as input from the user and prints the corresponding column value for every record in the file.

We calculated the location of a column using the combination of tr, awk, grep, and nl commands.

First, we converted the commas in the header line into line-breaks using the tr command. Then, we appended the line number at the beginning of each line using the nl command. Subsequently, we searched the column name in the output using the grep command and truncated the preceding spaces using the tr command.

Finally, we used the awk command to get the first field, which corresponds to the column number.

We’ll save the above script as parse_csv.sh for execution:

$ ./parse_csv.sh
Enter the column name to be printed for each record: Price
Displaying Record-1
Price: 20

Displaying Record-2
Price: 10

As expected, when “Price” was given as the input, only the values of the column number corresponding to the string “Price” in the header were printed. This approach can be particularly useful when the sequence of columns in a CSV file isn’t guaranteed.

4. Mapping Columns of CSV File into Bash Arrays

In the previous section, we parsed the field values into Bash variables for each record. Now we’ll check methods to parse entire columns of CSV into Bash arrays:

#! /bin/bash
arr_record1=( $(tail -n +2 input.csv | cut -d ',' -f1) )
arr_record2=( $(tail -n +2 input.csv | cut -d ',' -f2) )
arr_record3=( $(tail -n +2 input.csv | cut -d ',' -f3) )
arr_record4=( $(tail -n +2 input.csv | cut -d ',' -f4) )
echo "array of SNos  : ${arr_record1[@]}"
echo "array of Qty   : ${arr_record2[@]}"
echo "array of Price : ${arr_record3[@]}"
echo "array of Value : ${arr_record4[@]}"

We are using command substitution to exclude the header line using the tail command and then using the cut command to filter the respective columns. Notably, the first set of parentheses is required to hold the output of the command substitution in variable arr_record1 as an array.

Let’s check the script output:

array of SNos  : 1 2
array of Qty   : 2 5
array of Price : 20 10
array of Value : 40 50

5. Parsing CSV File Into a Bash Array

There can be cases where we might prefer to map the entire CSV file into an array. In effect, we can then use the array to process the records.

Let’s check the implementation:

#! /bin/bash 
arr_csv=() 
while IFS= read -r line 
do
    arr_csv+=("$line")
done < input.csv

echo "Displaying the contents of array mapped from csv file:"
index=0
for record in "${arr_csv[@]}"
do
    echo "Record at index-${index} : $record"
	((index++))
done

First, in this example, we read the line from our input CSV and then appended it to the array arr_csv (+= is used to append the records to Bash array). Then, we printed the records of the array using a for loop.

Let’s check the output:

Displaying the contents of array mapped from csv file:
Record at index-0 : SNo,Quantity,Price,Value
Record at index-1 : 1,2,20,40
Record at index-2 : 2,5,10,50

For Bash versions 4 and above, we can also populate the array using the readarray command:

readarray -t array_csv < input.csv

This reads lines from input.csv into an array variable: array_csv. The -t option will remove the trailing newlines from each line.

6. Parsing CSV Files Having Line Breaks and Commas Within Records

So far, in this tutorial, we used the file input.csv for running all our illustrations.

Now, let’s create another CSV file containing line breaks and commas within quoted strings:

$ cat address.csv
SNo,Name,Address
1,Bruce Wayne,"1007 Mountain Drive,
Gotham"
2,Sherlock Holmes,"221b Baker Street, London"

There can be several more permutations and combinations of line-breaks, commas, and quotes within CSV files. For this reason, it’s a complex task to process such CSV files with only Bash built-in utilities. Generally, third-party tools like csvkit are employed for advanced CSV parsing.

However, another suitable alternative is to use Python’s CSV module, as Python is generally pre-installed on most Linux distributions.

7. Conclusion

In this tutorial, we studied multiple techniques to parse values from CSV files.

In the beginning, we discussed the CSV standards and checked the steps to read records from a file.

After that, we implemented several case-studies to parse the field values of a CSV file. Alongside this, we also explored ways to handle the optional header line of CSV files.

Next, we presented techniques to store either columns or all the records of a CSV file into Bash arrays. Finally, we offered a brief introduction to some third-party tools for advanced CSV parsing.

2 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Comments are closed on this article!