Shell Script to Convert Excel Files to CSV

A bash shell script that batch converts all Excel files (.xlsx) in a directory to CSV format using Python's xlsx2csv library, with detailed code explanation.

3 min read

This shell script converts all Excel files (.xlsx format) in a specified directory to CSV files and saves them in a separate directory. It uses Python’s xlsx2csv library for the conversion.

Prerequisites

1. Python Installation

Ensure that Python is installed on your system.

2. Installing the xlsx2csv Library

xlsx2csv is a Python library for converting Excel files to CSV. Install it with the following command:

pip install xlsx2csv

Shell Script

Save the following content as a .sh file (e.g., excel_to_csv.sh).

#!/bin/bash

# Directory containing the Excel files to convert
# Example: Target Excel files on the Desktop
input_dir="${HOME}/Desktop"

# Directory to save the converted CSV files
# Example: Create a csv_output directory on the Desktop
output_dir="${HOME}/Desktop/csv_output"

# Create the output directory if it doesn't exist
mkdir -p "${output_dir}"

# Function to convert an Excel file to CSV
# Argument: $1 - Full path to the input Excel file
convert_excel_to_csv() {
    local input_file="$1"
    # Generate the output file name (remove the extension from the original file name and add .csv)
    local output_file="${output_dir}/$(basename "${input_file%.*}").csv"

    echo "Converting '${input_file}' to '${output_file}'..."

    # Execute conversion using python3 -m xlsx2csv
    # The -m option means executing the module as a script
    python3 -m xlsx2csv "${input_file}" "${output_file}"

    if [ $? -eq 0 ]; then # If the exit status of the previous command is 0, it succeeded
        echo "Conversion successful: '${output_file}'"
    else
        echo "Conversion failed for: '${input_file}'" >&2 # Error messages go to stderr
    fi
}

# Loop through all .xlsx files in the specified directory
# for ... in ...; do ... done syntax
for excel_file in "${input_dir}"/*.xlsx; do
    # Check if the file exists (prevents the loop from running once when no files match)
    if [ -f "${excel_file}" ]; then
        convert_excel_to_csv "${excel_file}"
    fi
done

echo "All Excel files processed."

How to Run the Script

  1. Save the above script as a file named excel_to_csv.sh or similar.
  2. Grant execute permission to the script.
    chmod +x excel_to_csv.sh
  3. Run the script.
    ./excel_to_csv.sh

Script Explanation

  • #!/bin/bash: Specifies that this script should be executed with the Bash shell.
  • input_dir, output_dir: Define the source and destination directory paths. ${HOME} represents the user’s home directory.
  • mkdir -p "${output_dir}": Creates the directory for the converted CSV files if it doesn’t exist. The -p option creates parent directories as needed and doesn’t error if the directory already exists.
  • convert_excel_to_csv() function:
    • local: Indicates that variables defined within the function are local variables.
    • basename "${input_file%.*}": Removes the path and extension from the file name.
    • python3 -m xlsx2csv "${input_file}" "${output_file}": Executes the xlsx2csv module to convert the Excel file to CSV.
    • if [ $? -eq 0 ]: Checks whether the exit status $? of the previous command is 0 (success).
  • for excel_file in "${input_dir}"/*.xlsx; do ... done: Loops through all files with the .xlsx extension in input_dir.
  • if [ -f "${excel_file}" ]; then ... fi: Checks whether what was found in the loop is actually a file. This prevents the loop from executing once when no files match *.xlsx.

This script is useful for batch converting Excel files to CSV.