Read CSV data from a file

A very common task is reading data stored in comma-separated value (csv) files. Excel has very good built-in functionailty to read csv files. However, it is often useful to do pre-processing in Python before loading the data into Excel. You may also only want to write the pre-processing code once and use it within Excel and your other Python projects.

We will use this Python code to read a csv file:

# Load a csv file for use in Excel
import os
import csv


def read_csv(filepath, dialect="excel"):
        """Reads the csv file at filepath.

        Uses the Python csv module as described at https://docs.python.org/3/library/csv.html
        """
        rows = []
        if not os.path.exists(filepath):
                raise ValueError(f"{filepath} does not exist.")
        with open(filepath) as csvfile:
                rows.extend(csv.reader(csvfile, dialect))
        return rows


if __name__ == "__main__":
        # Data from https://eforexcel.com/wp/downloads-18-sample-csv-files-data-sets-for-testing-sales/
        print(read_csv(r"D:\github\xlslim-code-samples\data\10000SalesRecords.csv"))

Note

All the Python code and Excel files shown are available from github in the xlslim-code-samples repo.

Save the Python code as a new file on your PC. I saved the file in my Documents folder.

Open Excel and enter this RegisterPyModule() formula (amending the location to match where you saved the file):

=RegisterPyModule("c:\users\russe\documents\read_csv.py")

You should see a message similar to this confirming the module registration:

The read_csv() function is now available in Excel and can be called by entering this formula (amending the csv file to one on your PC):

=read_csv("c:\users\russe\documents\10000SalesRecords.csv")

As easy as that! The data is now available in Excel.

Typically the Python code would be doing some pre-processing of the data, perhaps merging with other data sources, before passing the data to Excel.