How to Read Excel xlsx File and convert to 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).
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 Python program:
import pandas as pd_csv
Step 2:
Load the workbook (.xlsx file) that you want to convert to CSV:
dt_dict = pd_csv.read_excel(‘test_Excel.xlsx’, sheet_name=”Product Information”, usecols=[‘Product Name’, ‘Status’])
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:
dt_dict.to_csv(index=False)
Complete code and output
By combining all of the above code, we have the following Python program:
|
1 2 3 4 5 6 7 8 9 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Product Name,Status Wheat,In Stock Rice,In Stock Sugar,In Stock Maze,In Stock Tea Set,In Stock Dinner Set,Out of Stock Rice,Out of Stock Plates,In Stock Tea Spoon,Out of Stock Table Spoon,In Stock |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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:
