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
        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


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):


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

Register CSV function

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):


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

CSV data 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.