Converting XLS to CSV in Linux

1. Overview

Many organizations and individuals often work with Microsoft Excel files (XLS or XLSX). XLS refers to Excel Spreadsheet, which is a binary file format capable of storing a wide range of data types, formulas, charts, macros, and formatting choices. On the other hand, XLSX is a newer Office Open XML (OOXML) format utilized by Excel 2007 and later.

Since the compatibility of XLS and XLSX is not that great, we might need to convert them to a more universal and manageable format like Comma-Separated Values (CSV). In this tutorial, we will explore different methods to convert XLS to CSV in Linux.

2. Why Convert XLS to CSV?

There are several reasons why we might need to convert XLS files to CSV format. Let’s take a closer look at them.

2.1. Cross-Platform Compatibility

CSV files are plain text files with a simple structure, making them compatible with a wide range of software applications on different operating systems. By converting XLS files to CSV, we can ensure that various platforms can share and access the data.

2.2. Lightweight and Efficient

CSV files have a simpler structure compared to XLS files, resulting in smaller file sizes. So, this makes CSV a preferred format for storing and processing large datasets efficiently.

2.3. Easy Data Integration

Many software tools, programming languages, and data analysis libraries have built-in support for reading and manipulating CSV files. By converting XLS files to CSV, we can enable seamless integration of the data with these tools, enhancing our data analysis capabilities.

3. Using LibreOffice

LibreOffice is an open-source office suite that provides a powerful set of tools for word processing, spreadsheets, presentations, and file conversion. Let’s explore how we can leverage LibreOffice to convert XLS to CSV.

First, we need to install LibreOffice:

$ sudo apt install libreoffice

We can use LibreOffice to convert XLS file to CSV by opening an XLS file and saving it as CSV:

In this case, we just need to save the file with the file type Text CSV(.csv).

After saving the file with the file type Text CSV(.csv) then it prompts us to click on OK to export the file.

Alternatively, we can also convert XLS TO CSV using the command line. Before we begin the conversion, let’s install libreoffice unoconv — if it isn’t already installed — on a Linux system with the apt package manager:

$ sudo apt install libreoffice unoconv

Once LibreOffice and unoconv are installed, we can use the unoconv command to convert XLS files to CSV:

$ unoconv -f csv input.xls

The above command will generate a CSV file named input.csv in the current directory. Further, the unoconv command automatically detects the input file format, so it’s not necessary to specify the particular format.

4. Using Gnumeric

Gnumeric is a lightweight spreadsheet application for Linux that supports XLS files. Also, it offers a range of powerful features, including support for complex formulas, data sorting and filtering, graphing capabilities, and compatibility with various spreadsheet formats.

We’ll explore how to use Gnumeric to convert XLS to CSV in Linux.

Similar to the installation of LibreOffice, we can install Gnumeric on a Linux system:

$ sudo apt install gnumeric

Once we install Gnumeric, let’s open the XLS file with Gnumeric:

In this case, we need to navigate the Data tab and export as CSV, and then save the CSV file to the desired location.

Alternatively, after installing Gnumeric, we can also use the ssconvert command-line tool to convert XLS files to CSV. We’ll open a terminal and execute the ssconvert command along with the XLS format filename:

$ ssconvert input.xls output.csv

The ssconvert command automatically detects the input file format and converts it to CSV. In the current directory, the system will name the resulting CSV file output.csv.

5. Using Bash

Bash scripting is a powerful way to automate tasks in Linux, including the conversion of XLS files to CSV. By using Bash, we can create a script that can handle multiple files, perform bulk conversions, and integrate with other data processing tasks. Let’s write a Bash script to convert multiple XLS or XLSX files to CSV:

#!/bin/bash
# Variables to store conversion counts
success_count=0
failed_count=0
failed_filenames=""
# Loop through all XLS and XLSX files in the current directory
for file in *.xls *.xlsx; do
    # Check if the file is a valid XLS or XLSX file
    if [[ -f "$file" && "$file" =~ \.(xls|xlsx)$ ]]; then
        # Generate the output CSV filename
        csv_filename="${file%.*}.csv"
        # Convert XLS or XLSX to CSV
        if libreoffice --headless --convert-to csv "$file" --outdir . >/dev/null 2>&1; then
            # Check if the converted CSV file exists and has content
            if [[ -f "$csv_filename" && -s "$csv_filename" ]]; then
                echo "Converted: $file"
                ((success_count++))
            else
                echo "Failed to convert: $file"
                ((failed_count++))
                failed_filenames+=" $file"
            fi
        else
            echo "Failed to convert: $file"
            ((failed_count++))
            failed_filenames+=" $file"
        fi
    fi
done
# Print the conversion summary
echo ""
echo "Total Conversion success count: $success_count"
echo "Total conversion failed count: $failed_count"
# Print the failed filenames, if any
if [ "$failed_count" -gt 0 ]; then
    echo "Conversion failed filenames:$failed_filenames"
fi

Now, let’s save the script to a file, for example, convert.sh, and make it executable with chmod along with the +x option:

$ chmod +x convert.sh

Then, we can run the script in a Linux terminal in a directory where the Bash script file and XLS or XLSX files are located:

$ ./convert.sh

The Bash script reads each Excel file, converts it to CSV, and saves the resulting CSV file in the same directory. Additionally, the script checks the total conversion success and failed count, as well as the failed filenames, if any.

6. Using xls2csv

Another method to convert XLS files to CSV in Linux is by using the xls2csv command-line tool. xls2csv is a utility specifically designed for converting XLS files to CSV format.

Let’s ensure xls2csv is installed on the Linux system. If it’s not already installed, we can install the xls2csv utility only through the catdoc package. To install the utility, we need to install the catdoc package:

$ sudo apt install catdoc

Once xls2csv is installed, we can use it to convert an XLS file to CSV. We’ll open a terminal and navigate to the directory containing the XLS file we want to convert:

$ xls2csv input.xls > output.csv

In the above command, input.xls represents the name of the XLS file, and output.csv is the desired name for the resulting CSV file. Moreover, the > operator redirects the output of the xls2csv command to the output.csv file.

7. Using csvkit

csvkit is a powerful command-line tool that provides a suite of utilities for working with CSV files in Linux. It also supports the conversion of XLS files to CSV using the in2csv command.

Similarly, let’s install csvkit on the Linux system:

$ sudo apt install csvkit

After installing csvkit, we can utilize the in2csv command to convert XLS files to CSV:

$ in2csv input.xls > output.csv

The command will convert the input.xls file into an ouput.csv file and save it in the same directory.

8. Conclusion

In this article, we explored several methods to convert XLS to CSV in Linux.

Tools such as LibreOffice, Gnumeric, Bash scripting, csvkit, and xls2csv provide reliable options for automating the conversion process. With these methods, we can convert XLS files to CSV, enhancing data management and analysis capabilities.