The Python programming language has many libraries that can read, write, and manipulate CSV files. Python’s built-in csv module is one such library. It can be used to read or write the contents of a CSV file or to parse it into individual strings, numbers, etc.

When it comes to converting CSV to an Excel file, we must use an external module that let us work with Excel files (xlsx). There are few such libraries to choose from.

For this article, we are going to use the xlsxwriter module.

Create and read CSV files

This example code creates a CSV file with a list of popular writers (3 male and 3 female writers).

The file is written at the default file location. If you open it with a notepad, it’s going to look like this:

Read CSV

This code reads the CSV file and prints the result on the console.

Create Excel Sheet

Now, let’s create an Excel sheet.

This code creates an Excel file called writers.xslx with two worksheets: Male and Female.

At the end of the code, there is the close function. Without it, the file won’t be created.

Convert a single CSV file to multiple sheets

In this part, we are going to read CSV and write everything into an Excel file. Let’s start from the header. There is only one CSV file, so we need to take the header and write it twice into both Excel worksheets.

Row and column counting start from 0 therefore 0 is column A or row 1.

The index starts from the first column and takes the first element from the list, then the second column, and the second element.

Now, we must do the same with the remaining CSV elements.

The code checks each element in the fourth column of the CSV file, if it’s Male, the element is placed inside the first worksheet, if it’s Female, then into the second one.

The result for males:

And for females:

This is the full code:

Convert multiple CSV files to Excel sheets

We can take a different approach. If we have multiple CSV files inside a directory, we can convert each of them into an Excel worksheet named after this file.

We can modify the previous code to create two CSV files, one for female and the other one for male writers:

Next, let’s read the CSV files.

There are a few ways we can use to get all the files with a certain extension; using the glob module is one of them.

The code above gets all the CSV files from the directory and prints them to the console.

What we need to do now, is to create an Excel file and use CSV files names as worksheet names. We also need to copy the contents of each CSV file into each sheet. The following code does just that.

The os.path.basename function strips the full file path and assigns only a name to the file_name variable. Next, this name (with) extension is split into the file name and file extension, where the name path is assigned to file_no_ext.

Each worksheet is named using this variable.