logo

Create and read csv

Spreadsheets often export CSV (comma seperated values) files, because they are easy to read and write. A csv file is simply consists of values, commas and newlines. While the file is called ‘comma seperate value’ file, you can use another seperator such as the pipe character.

Related course
Data Analysis with Python Pandas

Create a spreadsheet file (CSV) in Python
Let us create a file in CSV format with Python. We will use the comma character as seperator or delimter.

import csv

with open('persons.csv', 'wb') as csvfile:
filewriter = csv.writer(csvfile, delimiter=',',
quotechar='|', quoting=csv.QUOTE_MINIMAL)
filewriter.writerow(['Name', 'Profession'])
filewriter.writerow(['Derek', 'Software Developer'])
filewriter.writerow(['Steve', 'Software Developer'])
filewriter.writerow(['Paul', 'Manager'])

Running this code will give us this fil persons.csv with this content:

Name,Profession
Derek,Software Developer
Steve,Software Developer
Paul,Manager

You can import the persons.csv file in your favorite office program.

python csv Spreadsheet file created in Python

 

Read a spreadsheet file (csv)
If you created a csv file, we can read files row by row with the code below:

import csv

# open file
with open('persons.csv', 'rb') as f:
reader = csv.reader(f)

# read file row by row
for row in reader:
print row

This will simply show every row as a list:

['Name', 'Profession']
['Derek', 'Software Developer']
['Steve', 'Software Developer']
['Paul', 'Manager']

Perhaps you want to store that into Python lists. We get the data from the csv file and then store it into Python lists. We skip the header with an if statement because it does not belong in the lists. Full code:

import csv

# create list holders for our data.
names = []
jobs = []

# open file
with open('persons.csv', 'rb') as f:
reader = csv.reader(f)

# read file row by row
rowNr = 0
for row in reader:
# Skip the header row.
if rowNr >= 1:
names.append(row[0])
jobs.append(row[1])

# Increase the row number
rowNr = rowNr + 1

# Print data
print names
print jobs

Result:

['Derek', 'Steve', 'Paul']
['Software Developer', 'Software Developer', 'Manager']

Most spreadsheet or office programs can export csv files, so we recommend you to create any type of csv file and play around with it :-)

Related course
Data Analysis with Python Pandas

BackNext





Leave a Reply:




Tj Thu, 11 Jun 2015

Hello, I am using version 2.5.4 of Python. My question is why when I try and use the 'open' function like (with open('persons.csv', 'wb') as csc file:). It says there's an error in your program: invalid syntax and has the open highlighted.

Frank Fri, 12 Jun 2015

Hi Tj, this works in Python 2.7.6. The keyword with became part of Python in versuib 2.6
There are two options make it work on Python 2.5:

import csv
import sys

f = open('persons.csv', 'wb')
try:
filewriter = csv.writer(f)
filewriter.writerow(['Name', 'Profession'])
filewriter.writerow(['Derek', 'Software Developer'])
filewriter.writerow(['Steve', 'Software Developer'])
filewriter.writerow(['Paul', 'Manager'])
finally:
f.close()

Alternative, import the __future__ module:

from __future__ import with_statement
import csv

with open('persons.csv', 'wb') as csvfile:
filewriter = csv.writer(csvfile, delimiter=',',
quotechar='|', quoting=csv.QUOTE_MINIMAL)
filewriter.writerow(['Name', 'Profession'])
filewriter.writerow(['Derek', 'Software Developer'])
filewriter.writerow(['Steve', 'Software Developer'])
filewriter.writerow(['Paul', 'Manager'])
Pavan Sun, 14 Jun 2015

Hi Frank,

Trying to create a csv file using and write into it shows an error saying:

Type-Error: A byte-like object is required not a 'str'

so I am not clear how can I resolve it if I am interested to enter strings as values into rows.

Thank you in advance

Frank Sun, 14 Jun 2015

Hi Pavan, are you using Python 3.5.0 by any chance?
str in Python 2 is a byte string, str in Python 3.5 is a unicode string.
In Python 3.5 open the file in 'w' not in 'wb', that will stop that error message. I tested the code below with Python 3.5.0

import csv
with open('persons.csv', 'w') as csvfile:
filewriter = csv.writer(csvfile, delimiter=',',
quotechar='|', quoting=csv.QUOTE_MINIMAL)
filewriter.writerow(['name','profession'])
filewriter.writerow(['Pavan','Python'])

Fabricio Thu, 09 Jul 2015

Hi, as a sugestion to the site (that's outstanding, by the way), you should put a "previous tutorial buttom" at the bottom of the page. Because, when I need to take a break I save the page to my favorite and later return to the point that I stopped, in that case I loose the previous pages that I saw in my browser, making impossible to return to a previous lesson.

Frank Thu, 09 Jul 2015

Thanks! I'll add the previous button

Skylar Mon, 13 Jul 2015

Hey, there tutorial of Python is amazing .. :) thanks for such a wonderful site man..!!

Ravi Thu, 06 Aug 2015

Instead of using a separate variable rowNr and checking the condition on every iteration, can't we add all the rows including the header, and outside the loop, call names.remove(names[0]) ?

Avi Tue, 18 Aug 2015

How do you make it not skip a line?

Frank Tue, 18 Aug 2015

Do you want more data on the same row? Simply add more parameters, as: filewriter.writerow(['name','profession','...','....'])
There is no set (x,y) in the csv file format but there is in some other spreadsheet formats.

Avi Wed, 19 Aug 2015

When I run this script it prints only on every odd row. I want it to print on row 1,2,3,4, not 1,3,5,7...

Frank Wed, 19 Aug 2015

Could you post the exact code you are using and open the .csv output in a text editor and post here?
Just to be sure, in the loop put "print(rowNr)". In office be sure to set the delimiter to a comma

Avi Wed, 19 Aug 2015
import csv
with open('persons.csv', 'w') as csvfile:
filewriter = csv.writer(csvfile, delimiter=',',
quotechar='|', quoting=csv.QUOTE_MINIMAL, lineterminator="\n")
filewriter.writerow(['name','profession','age'])
filewriter.writerow(['Avi','Accountant'])
filewriter.writerow(['Henry','Blacksmith'])

I solved the issue with the "lineterminator="\n"" code which I found on some forum. So now it's printing to rows 1,2,3,4 etc.
Now I have a new question. I want to print some strings with commas in it. How do I make it so those strings don't get deliminated midway through and end up in different cells?

Frank Wed, 19 Aug 2015

Hi Avi, good to hear! The most common method is to change the delimiter from a comma to something else, such as a semicolon or pipe. A second alternative may be to escape the characters, but depending on the office program reading the file this may or may not work.

Avi Wed, 19 Aug 2015

Thanks for your help.
I am trying to change the deliminator to a semicolon with this code:

import csv

with open('persons.csv', 'w') as csvfile:
filewriter = csv.writer(csvfile, delimiter=';',
quotechar='|', quoting=csv.QUOTE_MINIMAL, lineterminator="\n")
filewriter.writerow(['name';'profession';'age'])
filewriter.writerow(['Avi';'Accountant'])
filewriter.writerow(['Henry';'Blacksmith'])

However, I'm getting an invalid syntax error. Note, I am using Python 3.4.3 and have not made any changes to csv.py.

Frank Wed, 19 Aug 2015

Hi Avi, this will work:

import csv

with open('persons.csv', 'w') as csvfile:
filewriter = csv.writer(csvfile, delimiter=';',
quotechar='|', quoting=csv.QUOTE_MINIMAL, lineterminator="\n")
filewriter.writerow(['name','profession','age'])
filewriter.writerow(['Avi','Accountant'])
filewriter.writerow(['Henry','Blacksmith'])
Avi Wed, 19 Aug 2015

Output of cell A1 is: name;profession;age
So now it's all in one column, instead of three.

Frank Wed, 19 Aug 2015

Hi, set the delimiter to a semicolon ';' in office. If you want to flip the column/row order, you could do this:

import csv

with open('persons2.csv', 'w') as csvfile:
filewriter = csv.writer(csvfile, delimiter=';',
quotechar='|', quoting=csv.QUOTE_MINIMAL, lineterminator="\n")
filewriter.writerow(['name','Henry'])
filewriter.writerow(['profession','Accountant'])
filewriter.writerow(['age','35'])

I hope I understood correct. Let me know if you have any questions.

Avi Thu, 20 Aug 2015

Thank you, that works great. I had to Google how to set the delimiter to a semicolon ';' in office. This is how:

"Change the separator in all .csv text files

In Microsoft Windows, click the Start button, and then click Control Panel.
Open the dialog box for changing Regional and Language settings.
Type a new separator in the List separator box.
Click OK twice."

Stewart Mon, 12 Oct 2015

Something is wrong with the 'read and write files' page. It doesn't complete loading and I can't scroll the pages. Then I get an error that the page is not responding and to recover it - which doesn't work either. Please fix.

Mat Wed, 14 Oct 2015

Frank
Thanks for this. New to Python and haven't programmed for many years. I want to read a multi-row.csv file (float), and I've copied your code into Phycharm.

import csv

# open file
with open('gisp2short.csv', 'rb') as f:
reader = csv.reader(f)

# read file row by row
for row in reader:
print row

I'm getting an error message "SyntaxError: Missing parentheses in call to 'print'".
What am I doing wrong?
TIA
Mat

Frank Sat, 17 Oct 2015

Hi stewart, which file are you trying to read? There is no scrolling, the data is simply in a variable. To scoll you need a textbox widget

Frank Sat, 17 Oct 2015

Hi Mat! Change the last line to print(row). Python3 requires brackets to be around the print command.

Jenna Sat, 28 Nov 2015

Great resource. However, when I try to read a cvs file I keep getting
Could not find a part of the path 'C:\Program Files\Rhinoceros 5 (64-bit)\System\artist_song_list\artists-songs-albums-tags.csv'.
Traceback:
line 4, in script
even though I just put the file in Rhino's System. Thoughts?

Frank Sun, 29 Nov 2015

Thanks Jenna! Try to put the file between quotes or using double slashes: 'C:\\Program Files\\Rhinoceros 5 (64-bit)\\System\\artist_song_list\\artists-songs-albums-tags.csv'. A single slash in Python is used as escape characters, such as \n for newline.

Scot Tue, 08 Dec 2015

Hi Frank,
He means the https://pythonspot.com/read... page has an error on it.

Great site, btw.

Frank Tue, 08 Dec 2015

Thanks Scot! I'll look at this asap

Victor Sat, 12 Dec 2015

Hi Frank. I have some problem for the first program. After running the code. I have a file persons.csv but it looks like this .
Name,Profession

Derek,Software Developer

Steve,Software Developer

Paul,Manager

How can I cancel those empty lines between them? Thanks.

Frank Sun, 13 Dec 2015

Try to add a comma after your print command. Alternative is print(row.replace("\n",""))

Kathryn Thu, 14 Jul 2016

Hi Frank,

Is there a reason the lists are "initialized" twice?

names = []
jobs = []

Frank Fri, 15 Jul 2016

Hi Kathryn,

Thanks for noticing! It should have been "initialized" only once.