create csv file python
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.
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.
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
[‘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 🙂
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.
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’])
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.
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’])
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.
Thanks! I’ll add the previous button
Hey, there tutorial of Python is amazing .. 🙂 thanks for such a wonderful site man.
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]) ?
How do you make it not skip a line?
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.
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.
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
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?
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.
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.
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’])
Output of cell A1 is: name;profession;age
So now it’s all in one column, instead of three.
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.
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.»
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.
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.
# 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
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
Hi Mat! Change the last line to print(row). Python3 requires brackets to be around the print command.
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?
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.
Hi Frank,
He means the https://pythonspot.com/read. page has an error on it.
Thanks Scot! I’ll look at this asap
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
How can I cancel those empty lines between them? Thanks.
Try to add a comma after your print command. Alternative is print(row.replace(«\n»,»»))
Is there a reason the lists are «initialized» twice?
Thanks for noticing! It should have been «initialized» only once.