Processing CSV files in Bash Scripting

Introduction

CSV files store data in a simple table format. Processing them helps you extract or change information easily using scripts.

You want to count how many rows are in a CSV file.

You need to extract a specific column from a CSV file.

You want to filter rows based on a value in a column.

You want to convert CSV data into another format.

You want to automate reports from CSV data.

Syntax

Bash Scripting

cut -d',' -f<column_number> <file.csv>
awk -F',' '{print $<column_number>}' <file.csv>
grep '<pattern>' <file.csv>

-d',' tells commands the separator is a comma.

-f or $ followed by a number picks a column.

Examples

This extracts the second column from data.csv.

Bash Scripting

cut -d',' -f2 data.csv

This prints the third column from data.csv using awk.

Bash Scripting

awk -F',' '{print $3}' data.csv

This finds all lines containing the word 'John' in data.csv.

Bash Scripting

grep 'John' data.csv

Sample Program

This script reads data.csv, skips the header line, and prints the names (second column).

Bash Scripting

#!/bin/bash
# Script to print names from a CSV file
# CSV format: id,name,age

FILE=data.csv

if [ ! -f "$FILE" ]; then
  echo "File $FILE not found."
  exit 1
fi

# Print header
echo "Names in the file:"

# Skip header and print second column (name)
tail -n +2 "$FILE" | cut -d',' -f2

Important Notes

CSV files can have commas inside quotes; simple tools like cut may not handle those correctly.

For complex CSV parsing, consider tools like csvkit or scripting languages like Python.

Summary

CSV files store data separated by commas.

Use cut, awk, and grep to process CSV files in bash.

Simple commands work well for basic CSV files without quoted commas.

Practice

1. What does the cut -d, -f2 file.csv command do when processing a CSV file?

easy

A. Deletes the second column from the CSV file

B. Extracts the second column from the CSV file using comma as delimiter

C. Counts the number of commas in the CSV file

D. Replaces commas with tabs in the CSV file

Solution

1. Step 1: Understand the cut command with delimiter

   The -d, option sets the delimiter to a comma, which is the separator in CSV files.

2. Step 2: Extract the specified field

   The -f2 option extracts the second field (column) from each line of the file.

3. Final Answer:

   Extracts the second column from the CSV file using comma as delimiter -> Option B

4. Quick Check:

   cut -d, -f2 extracts column 2 [OK]

Hint: Use -d for delimiter and -f for field number in cut [OK]

Common Mistakes:

• Confusing -f with line number
• Using wrong delimiter character
• Thinking cut deletes columns

2. Which of the following is the correct syntax to print the third column of a CSV file using awk?

easy

A. awk -F, '{print $3}' file.csv

B. awk -d',' '{print $3}' file.csv

C. awk -F',' 'print $3' file.csv

D. awk '{print $3}' -F',' file.csv

Solution

1. Step 1: Identify the field separator option in awk

   The correct option to set the field separator is -F,, which tells awk to split fields by commas.

2. Step 2: Use the print statement correctly

   The print statement must be inside single quotes and include curly braces: {print $3} to print the third column.

3. Final Answer:

   awk -F, '{print $3}' file.csv -> Option A

4. Quick Check:

   Use -F for delimiter and print $3 inside braces [OK]

Hint: Use -F, and print $column inside braces in awk [OK]

Common Mistakes:

• Using -d instead of -F for delimiter
• Omitting curly braces around print
• Placing -F option after the script

3. Given a CSV file data.csv with content:

name,age,city
Alice,30,New York
Bob,25,Los Angeles
Carol,27,Chicago

What is the output of the command awk -F, '$2 > 26 {print $1}' data.csv?

medium

A. Alice

B. Alice
Bob
Carol

C. Bob
Carol

D. Alice
Carol

Solution

1. Step 1: Understand the awk condition and field separator

   The command uses -F, to split fields by commas. The condition $2 > 26 filters rows where the second field (age) is greater than 26.

2. Step 2: Apply the condition to each row

   Rows:
   - Header: 'age' > 26? No (string)
   - Alice,30: 30 > 26? Yes, print $1 = Alice
   - Bob,25: 25 > 26? No
   - Carol,27: 27 > 26? Yes, print $1 = Carol

3. Final Answer:

   Alice Carol -> Option D

4. Quick Check:

   Filter ages > 26, print names [OK]

Hint: Filter with $column condition and print desired field [OK]

Common Mistakes:

• Including header row in output
• Confusing field numbers
• Not using correct field separator

4. You wrote this command to extract the first column from a CSV file:
cut -f1 -d',' file.csv
But it returns the whole line instead of just the first column. What is the likely problem?

medium

A. The -f1 option is invalid

B. The file is empty

C. The delimiter option -d',' is missing or incorrect

D. cut cannot process CSV files

Solution

1. Step 1: Check the delimiter option usage

   The command uses -d',', which is incorrect. The single quotes make the delimiter the string ',' (quote comma quote) instead of plain comma (,). cut searches for ',' which isn't in the CSV.

2. Step 2: Understand cut behavior without correct delimiter

   Without splitting on ,, cut falls back to default (TAB), treating each line as one field, so -f1 outputs the entire line.

3. Final Answer:

   The delimiter option -d',' is missing or incorrect -> Option C

4. Quick Check:

   cut needs -d, (no quotes around comma) to split fields [OK]

Hint: Always specify -d, for CSV files with cut [OK]

Common Mistakes:

• Forgetting -d option
• Using wrong delimiter character
• Assuming cut auto-detects delimiter

5. You want to create a new CSV file containing only rows where the city is 'Chicago' from data.csv. Which bash command correctly filters and saves these rows?

hard

A. awk -F, '$3 == "Chicago"' data.csv > chicago.csv

B. grep 'Chicago' data.csv > chicago.csv

C. cut -d',' -f3 data.csv | grep 'Chicago' > chicago.csv

D. awk -F, '{if ($3 = "Chicago") print}' data.csv > chicago.csv

Solution

1. Step 1: Use awk with correct field and condition

   Using -F, sets comma as delimiter. The condition $3 == "Chicago" selects rows where the third column equals 'Chicago'.

2. Step 2: Redirect output to new file

   The output is redirected to chicago.csv using >, saving only matching rows.

3. Final Answer:

   awk -F, '$3 == "Chicago"' data.csv > chicago.csv -> Option A

4. Quick Check:

   Use awk with == for exact match and redirect output [OK]

Hint: Use awk with == for exact match and redirect output [OK]

Common Mistakes:

• Using single = instead of == in awk condition
• Using grep which may match partial fields
• Using cut which extracts columns, not rows