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

31 thoughts on “Create and read csv


  1. Stewart
    - October 12, 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.

    1. Frank
      - October 17, 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

      1. Scot
        - December 8, 2015

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

        Great site, btw.

        1. Frank
          - December 8, 2015

          Thanks Scot! I’ll look at this asap

  2. Ravi
    - August 6, 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]) ?

  3. Skylar
    - July 13, 2015

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

  4. Fabricio
    - July 9, 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.

    1. Frank
      - July 9, 2015

      Thanks! I’ll add the previous button

  5. Pavan
    - June 14, 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

    1. Frank
      - June 14, 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'])

      1. Avi
        - August 18, 2015

        How do you make it not skip a line?

        1. Frank
          - August 18, 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.

          1. Avi
            - August 19, 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…

            1. Frank
              - August 19, 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

              1. Avi
                - August 19, 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?

                1. Frank
                  - August 19, 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.

                  1. Avi
                    - August 19, 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.

                    1. Frank
                      - August 19, 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'])

                    2. Avi
                      - August 19, 2015

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

                    3. Frank
                      - August 19, 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.

                    4. Avi
                      - August 20, 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.”


  6. Tj
    - June 11, 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.

    1. Frank
      - June 12, 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'])

Leave a Reply

Login disabled