How to Convert From Excel to CSV? | 1
|

How to Convert From Excel to CSV?

You would often want to convert Excel files to CSV formats. CSVs are far more compatible with any OS or filesystem.

Excels are awesome. But when it comes to compatibility, CSVs always win. It’s because CSVs are nothing more than plain text files. Any system that could handle text could read CSV.

If you use Excel or other spreadsheet software, you could open an Excel file and save it as CSV. It’s the easiest way to convert Excels into CSVs.

But it’s not the best way all the time.

  1. If your excel file is large, your spreadsheet software will take a very long time to read and write to the disk.
  2. If you have many Excel files converting them to CSV one by one could be a nightmare.
  3. You can’t do this if you’re in a system that doesn’t have or support spreadsheet software. Most servers or serverless functions fall under this category.
  4. If you have many sheets in your Excel files, saving them individually to CSV can create confusion.

But if you know a bit of Python, you can convert hundreds of Excel files into CSV in seconds.

Don’t worry, even if you don’t know how to code in Python. You can still convert Excel files the same way with the package I created.

Install Python dependencies to convert from Excel to CSV.

The first step is to install the requirements. For this, we’re going to use Pandas with Openpyxl.

Pandas is a data analysis and manipulation library in Python. I should say “The” library because Pandas is famous among Python developers.

Openpyxl is a Python library for reading and writing excel files. We won’t be directly working with this library. Instead, Pandas uses Openpyxl under the hood.

To install both packages, we can use the PyPI repository. The following code will install it on your Virtualenv directly or with the help of Poetry.

# On virtualenv
pip install pandas openpyxl

# On Poetry
poetry add pandas openpyxl

If you’re using the anaconda stack, you only have to install Openpyxl. This is because anaconda installation comes with Pandas and most other widespread data manipulation packages.

conda install -c anaconda openpyxl.

Converting a Single Exel file to CSV.

This one is straightforward. You only have to read the excel using Pandas and write it back as CSV.

The following three lines of code will convert your Excel file to CSV.

import pandas as pd
df = pd.read_excel("<PATH TO EXCEL>.xlsx")
df.to_csv("<PATH TO CSV>.csv")

If your use case is as simple as this, probably spreadsheet software is more convenient than Python.

Converting Excel files with many sheets to separate CSVs.

Here’s a slightly more complex case of Excel to CSV conversion.

In the following example, we first read the sheet names of our Excel file. Then we loop through individual sheet names and save them as separate CSVs. The script saves CSVs under the same directory with sheet names as their filenames.

import os
import pandas as pd

file_path = "<PATH TO EXCEL>.xlsx"

// Get sheet names of the file
sheet_names = pd.ExcelFile(file_path).sheet_names

// Loop through sheet names
for sheet in sheet_names:
    df = pd.read_excel(file_path, sheet_name=sheet)# Read sheet

    # Create a new file path with sheet name
    csv_file_name = os.path.join(os.path.dirname(file_path), sheet + '.csv')
    df.to_csv(csv_file_name, index=False)

Convert multiple Excels to CSV at once.

Suppose your working directory has many Excel files, and you want to convert them into CSV. You could use this technique. First, we’ll assume we only have one sheet for each file. Then we’ll expand our code into a many file-many sheet scenario.

The following Python code uses a standard module called glob. Glob helps us to pattern-match file paths. We use it to match all file paths that end with .xlsx in the working directory.

Then we created a function to read and save Excels into CSV. We call this function on each file path we’ve identified.

import os
from glob import glob

import pandas as pd


def convert_excel_to_csv(file_path):
    df = pd.read_excel(file_path)
    df.to_csv(file_path.replace(".xlsx", ".csv"), index=False)
    print("Converted {} to CSV".format(file_path))


def convert_excels_in_directory_to_csv(directory):
    for file_path in glob(directory + "/*.xlsx"):
        convert_excel_to_csv(file_path)


if __name__ == "__main__":
    directory = os.getcwd()
    convert_excels_in_directory_to_csv(directory)

Convert from Excel to CSV, where many Excels have many sheets.

This is probably the difficult part. Our working directory has many Excel files. And each file has many sheets. Our goal is to

  1. create a directory for each excel file;
  2. convert sheets in the excel file to CSV and;
  3. store them inside the newly-created directory.

import os
from glob import glob

import pandas as pd


def convert_exels_with_sheets_to_csvs(directory):

    for file_path in glob(directory + "/*.xlsx"):
        excel_file_name = os.path.basename(file_path)
        excel_file_name = excel_file_name.replace(".xlsx", "")
        new_directory = directory + "/" + excel_file_name
        os.mkdir(new_directory)
        for sheet_name in pd.ExcelFile(file_path).sheet_names:
            sheet_df = pd.read_excel(file_path, sheet_name)
            sheet_df.to_csv(new_directory + "/" + sheet_name + ".csv", index=False)
            print("Converted {} to CSV".format(sheet_name))


if __name__ == "__main__":
    directory = os.getcwd()
    convert_exels_with_sheets_to_csvs(directory)

The above script uses glob to fetch all the Excel files in the current directory. Then it uses the os.mkdir utility to create a folder for each Excel file. Finally, it loops through the sheet name and creates one CSV for each sheet under the new directory.

Final thoughts.

Converting from Excel to CSV can be both easy and hard. It’s easy if you have only one file with very few sheets. But it could be complicated otherwise.

To tackle the problem, you need to convert them programmatically. Also, programmatically converting Excels to CSV benefits not relying on spreadsheet software.

In this post, we’ve discussed various methods to convert Excel to CSV. In your projects, you can either use them directly or grab the concepts and apply them differently.

Thanks for reading, friend! Say Hi to me on LinkedIn, Twitter, and Medium.

Not a Medium member yet? Please use this link to become a member because, at no extra cost for you, I earn a small commission for referring you.

Similar Posts