How to Read Excel xlsx File and Convert to CSV by Pandas

Read and convert Excel .xlsx file into CSV by Pandas

In this tutorial, we will show you how to read a .xlsx file (an Excel file) and then converting to CSV (Comma Separated Values) by using Pandas (A Python library).

Step by step to read and convert xlsx file

Step 1:

Import the pandas into the Python program:

Step 2:

Load the workbook (.xlsx file) that you want to convert to CSV:

The above line of code specifies:

• Our Excel file – test_Excel.xlsx
• Load one of its sheets “Product Information”
• From that sheet, only return two columns i.e. Product Name and Status

This is our sample sheet:

Step 3:

Using the data frame to_csv method

This line of code converts the loaded Excel file into CSV:

Complete code and output

By combining all of the above code, we have the following Python program:

import pandas as pd_csv

#Loading a sheet "Product Information" with two columns only

dt_dict = pd_csv.read_excel('test_Excel.xlsx', sheet_name="Product Information", usecols=['Product Name', 'Status'])

#Convert xlsx file to csv and display

print(dt_dict.to_csv(index=False))

Output:

Saving xlsx data into CSV file

The above code only generated CSV based on an Excel xlsx file.

For saving that comma separated values into a CSV file, you may use the data frame to_csv method as follows:

import pandas as pd_csv

#Loading a sheet "Product Information" with two columns only

dt_dict = pd_csv.read_excel('test_Excel.xlsx', sheet_name="Product Information", usecols=['Product Name', 'Status'])

#Convert xlsx file to csv and display

print(dt_dict.to_csv(index=False))

#Save xlsx data into a CSV file

dt_dict.to_csv('csv_data.csv', index=False)

Output:

The above code creates a new CSV file with the name = “csv_data.csv” in the same directory where Python code file exists.

As we opened the above generated CSV file, this is how it looks in Notepad: