Excel to CSV in Python

Define MS Excel

Microsoft Excel is an application provided by the Microsoft corporation which allows us to build graphs to build tables, and it is also used for the macro programming language. MS Excel helps save the file quickly, and we can quickly add or delete any data. The key features of MS Excel are:

• MS Excel allows data filtering so that we can change any values in the MS Excel spreadsheet and add our required data.
• MS Excel provides the headers and footers, which helps in differentiating the data; it also provides the passwords to protect the data from external users.
• MS Excel also supports sorting the data so that we can sort our data in ascending or descending order.
• MS Excel also supports formula auditing; it helps build the relationships between the cells and the tables provided in the data.

Define CSV File

The CSV file is a file that is used to write the text which consists of the data in the spreadsheet the data is separated by the commas. Generally, the Comma separated files (CSV) are mainly used in programming languages, we can import the CSV files into the code of the program with the help of the file declaration. We canwrite the CSV file in notepad or any text file. The CSV file can be opened in any text editor file or any text editor program, but we can't open the excel file only in Microsoft excel or in google sheets only. Generally, the CSV file will be saved with an extension of .csv.

Excel to CSV in Python

The excel file can be converted into a comma-separated file with the help library in the python programming languageknown as the pandas library. The pandas is an open source in python, mainly used to analyze the data. Thepandas dataframe is used to organize the data in a labeled form. The data scientistsprimarily use pandas. The dataframecreated in pandas are mutable. That is, we can insertthe columns into the pandas dataframe. The pandas library in python is faster and simple than any other library.

The pandas are built over the Numpy for operating the pythonlibraries; we require the Numpy. The pandas provide a variety of methods or functions tocomplete the process more easily. To read an excel file we use the read_excel( ) function and we can convert the excel file to a CSV file with the help of to_csv( ).

Code:

# importing the libraries in python


import pandas as pd
# reading the comma-separated value file which is present in excel format


File = pd.read_excel(“Data.xlsx”)
#convering the given file into the comma-separated value file 
file.to_csv(“Data.csv”, index= None, header = True)


#reading the CSV file and converting the file into the dataframe object
df = pd.DataFrame(pd.read_csv(“ Data.csv ”))


#Diaplaying the dataframe
df

Output:

 Here, we can observe that the table of the dataframe consists of the data about the name of the person and their Id, and the cost of the book brought by that person. Here, the file was first saved in excel format but then we converted the file into CSV text.

Converting Excel into CSV with xlrd and CSV Library

The xlrd library is used to read the excel file and the CSV file is used to read and write the CSV file. The excel sheets generally can't be imported into the program but the CSV file can be imported easily into the program so easily.

Code:

# importing the libraries which required to perform the required operations


import xlrd
import CSV 
import pandas as pd


#Opening the file with the sheet index


Spreadsheet = xlrd.open_data(“Data.xlsx”)


#Creating the writer's object


Column =csv.writer( open(“D.csv”, ‘w’, newline=” “))


#The data is written into the CSV file
for row in range(Spreadsheet.rows):
      #Writing the data row by row
      # Performing the operation
Column.writerow(Spreadsheet.row_elements(row))


#Reading the CSV file and converting the data into the datframe


df = pd.DataFramre( pd.read_csv(“D.csv”))


#diaplay the data
df

Output:

Here, we can observe that the table of the dataframeconsists of the data about the name of the person and their Id, and the cost of the book brought by that person. Here, the file was first saved in excel format but then we converted the file into the CSV text. Here first read the data then copied the data into the CSV file and saved the file in the form of CSV.