Python Copy Some Data From Excel to CSV: A Beginner Guide – Python Tutorial

By | April 10, 2020

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:

python convert excel sheet to csv file

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:

python convert excel sheet to csv file with no index number

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

Leave a Reply

Your email address will not be published. Required fields are marked *