When we are analyzing data in excel file, we may have to copy some data into a csv file. In this tutorial, we will use an example to illustrate python beginners on how to do.
Preliminary
As to an excel file, there are some sheets in it. In order to process excel file, we should read data sheet by sheet.
We can use python pandas to read a sheet in an excel. Here is an example:
import pandas
excel_data = pandas.read_excel('test.xlsx', sheet_name='member')
print(excel_data)Run this code, you can get the output:
No Name Age 0 1 Tom 24 1 2 Kate 22 2 3 Alexa 34
To know more on how to read data in excel using python pandas, you can read this tutorial.
Python Pandas read_excel() – Reading Excel File for Beginners
Copy all data in a sheet to a csv file
We can copy all data in a sheet to a csv file. Here is an example:
excel_data.to_csv("test.csv")
This code can make us copy all data in member sheet to csv file test.csv. Then you can find the content of test.csv is:
The resutl is not a standar csv content, to make the data be similar to common csv, we can do like this:
excel_data.to_csv("test.csv", sep = '\t', header = True, index = False)
Then the csv file will be:
Copy some rows to csv
If you plan to copy some rows in a sheet to a csv file, you can refer to this example:
new_excel_data = excel_data[excel_data['Age'] < 30]
print(new_excel_data)
new_excel_data.to_csv("test_new.csv", sep = '\t', header = True, index = False)
In this code, we only plan copy members whose ages are smaller than 30. The test_new.csv will be:
No Name Age 1 Tom 24 2 Kate 22