How to Convert Excel to CSV in Python

In this article, we will show you how to convert an excel file to the CSV File (Comma Separated Values) using python.

Assume we have taken an excel file with the name sampleTutorialsPoint.xlsx containing some random text. We will return a CSV File after converting the given excel file into a CSV file.

sampleTutorialsPoint.xlsx

Method 1: Converting Excel to CSV using Pandas Module

Algorithm (Steps)

Following are the Algorithm/steps to be followed to perform the desired task −

• Import the pandas module (Pandas is a Python open-source data manipulation and analysis package)

• Create a variable to store the path of the input excel file.

• Read the given excel file content using the pandas read_excel() function (reads an excel file object into a data frame object).

• Convert the excel file into a CSV file using the to_csv() function (converts object into a CSV file) by passing the output excel file name, index as None, and header as true as arguments.

• Read the output CSV file with the read_csv() function (loads a CSV file as a pandas data frame) and convert it to a data frame object with the pandas module's DataFrame() function.

• Show/display the data frame object.

Example

The following program converts an excel file into a CSV file and returns a new CSV file

# importing pandas module
import pandas as pd

# input excel file path
inputExcelFile ="sampleTutorialsPoint.xlsx"

# Reading an excel file
excelFile = pd.read_excel (inputExcelFile)

# Converting excel file into CSV file
excelFile.to_csv ("ResultCsvFile.csv", index = None, header=True)

# Reading and Converting the output csv file into a dataframe object
dataframeObject = pd.DataFrame(pd.read_csv("ResultCsvFile.csv"))

# Displaying the dataframe object
dataframeObject

Output

On executing, the above program will generate the following output −

|  index | Player Name         | Age | Type      | Country          | Team                      |Runs  | Wickets |
|--------|---------------------|-----|-----------|------------------|---------------------------|----- |---------|
|   0    |Virat Kohli          |   33|Batsman    |   India          |Royal Challengers Bangalore| 6300 |   20    |
|   1    |Bhuvaneshwar Kumar   |   34|Batsman    |   India          |Sun Risers Hyderabad       | 333  |   140   |
|   2    |Mahendra Singh Dhoni |   39|Batsman    |   India          |Chennai Super Kings        | 4500 |    0    |
|   3    |Rashid Khan          |   28|Bowler     |   Afghanistan    |Gujarat Titans             | 500  |   130   |
|   4    |Hardik Pandya        |   29|All rounder|   India          |Gujarat Titans             | 2400 |    85   |
|   5    |David Warner         |   34|Batsman    |   Australia      |Delhi Capitals             | 5500 |    12   |
|   6    |Kieron Pollard       |   35|All rounder|   West Indies    |Mumbai Indians             | 3000 |    67   | 
|   7    |Rohit Sharma         |   33|Batsman    |   India          |Mumbai Indians             | 5456 |    20   |
|   8    |Kane Williamson      |   33|Batsman    |   New Zealand    |Sun Risers Hyderabad       | 3222 |     5   |
|   9    |Kagiso Rabada        |   29|Bowler     |   South Africa   |Lucknow Capitals           | 335  |    111  |

In this program, we use the pandas read_excel() function to read an excel file containing some random dummy data, and then we use the to csv() function to convert the excel file to csv. If we pass the index as a false argument, the final CSV file does not contain the index row at the beginning. Then we converted the CSV to a data frame to see if the values from the excel file were copied into the CSV file.

Method 2: Converting Excel to CSV using openpyxl and CSV Modules

Algorithm (Steps)

Following are the Algorithm/steps to be followed to perform the desired task −

• Use the import keyword, to import the openpyxl (Openpyxl is a Python package for interacting with and managing Excel files. Excel 2010 and later files with the xlsx/xlsm/xltx/xltm extensions are supported. Data scientists use Openpyxl for data analysis, data copying, data mining, drawing charts, styling sheets, formula addition, and other operations) and CSV modules.

pip install openpyxl

• Create a variable to store the path of the input excel file.

• To create/load a workbook object, pass the input excel file to the openpyxl module's load_workbook() function (loads a workbook).

• Opening an output CSV file in write mode with open() and writer() functions to convert an input excel file into a CSV file.

• Using the for loop, traverse each row of the worksheet.

• Use the writerow() function, to write cell data of the excel file into the result CSV file row-by-row.

Example

The following program converts an excel file into a CSV file and returns a new CSV file −

# importing openpyxl module and csv modules
import openpyxl
import csv

# input excel file path
inputExcelFile = 'sampleTutorialsPoint.xlsx'

# creating or loading an excel workbook
newWorkbook = openpyxl.load_workbook(inputExcelFile)

# getting the active workbook sheet(Bydefault-->Sheet1)
firstWorksheet = newWorkbook.active

# Opening a output csv file in write mode
OutputCsvFile = csv.writer(open("ResultCsvFile.csv", 'w'), delimiter=",")

# Traversing in each row of the worshsheet
for eachrow in firstWorksheet.rows:

# Writing data of the excel file into the result csv file row-by-row
OutputCsvFile.writerow([cell.value for cell in eachrow])

Output

On executing, the above program a new CSV file (ResultCsvFile.csv) will be created with data of Excel.

In this program, we have an excel file with some random dummy data, which we load as an openpyxl work and set to use using the active attribute. Then we made a new CSV file and opened it in writing mode, then we went through the excel file row by row and copied the data into the newly created CSV file.

Conclusion

In this tutorial, we learned how to read an excel file and convert it to an openpyxl workbook, then how to convert it to a CSV file and remove the index, and finally how to convert the CSV file to a pandas data frame.

Vikram Chiluka

e

Updated on: 29-Aug-2023

23K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started