How to use delimiter for CSV in Python?

Question

I'm having trouble with figuring out how to use the delimiter for csv.writer in Python. I have a CSV file in which the strings separated by commas are in single cell and I need to have each word in each individual cell, e.g:

100 , 2559  ,,Main, St,LEOMA,LEOMA,498,498, AK,AK
140 , 425   ,,Main, St,LEOMA,LEOMA,498,498, AK,AK
100 , 599   ,,Main, St,LEOMA,LEOMA,498,498, AK,AK

should have each word in an individual cell:

100 2559    Main    St  LEOMA   LEOMA   498 498 AK  AK
140 425     Main    St  LEOMA   LEOMA   498 498 AK  AK
100 599     Main    St  LEOMA   LEOMA   498 498 AK  AK

I tried:

import csv
workingdir = "C:\Mer\Ven\sample"
csvfile = workingdir+"\test3.csv"
f=open(csvfile,'wb')

csv.writer(f, delimiter =' ',quotechar =',',quoting=csv.QUOTE_MINIMAL)

Why are you using spaces as a quote character when you already have spaces in your sentence? — Martijn Pieters
– Martijn Pieters, Commented May 29, 2013 at 21:00
I must confess to be confused by your question; you talk about writing CSVs with commas but you want your output to be with spaces instead? Can you show us a sample of a row (a python list) that you want to write? — Martijn Pieters
– Martijn Pieters, Commented May 29, 2013 at 21:02
I wasn't exactly sure of what I should have in quotechar. So I left it blank. — hydi
– hydi, Commented May 29, 2013 at 21:13
You set it to the space character. You didn't leave it blank. Leave it to the default " or set quoting=csv.QUOTE_NONE to disable quoting. Your problem is not with quoting, I don't think. — Martijn Pieters
– Martijn Pieters, Commented May 29, 2013 at 21:15
I have bunch of addresses generated as a CSV file but in the form of ex: 15,29,Main St,city,county,zip,state. And all of these are in single cell. I want to separate them (each word) in individual cell. Did I make it any clear? — hydi
– hydi, Commented May 29, 2013 at 21:16

underrun · Accepted Answer · 2013-05-29 22:25:33Z

36

Your code is blanking out your file:

import csv
workingdir = "C:\Mer\Ven\sample"
csvfile = workingdir+"\test3.csv"
f=open(csvfile,'wb') # opens file for writing (erases contents)
csv.writer(f, delimiter =' ',quotechar =',',quoting=csv.QUOTE_MINIMAL)

if you want to read the file in, you will need to use csv.reader and open the file for reading.

import csv
workingdir = "C:\Mer\Ven\sample"
csvfile = workingdir+"\test3.csv"
f=open(csvfile,'rb') # opens file for reading
reader = csv.reader(f)
for line in reader:
    print line

If you want to write that back out to a new file with different delimiters, you can create a new file and specify those delimiters and write out each line (instead of printing the tuple).

answered May 29, 2013 at 22:25

underrun

6,8712 gold badges44 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

hydi Over a year ago

I tried it and what it does it is it prints each character in one cell instead of the entire number or word separated by a ','. Could you indicated what exactly goes in .writerow( ) ?

underrun Over a year ago

writerow needs a list ... if you pass it a string it will make each character its own cell and that sounds like what it is doing.

hydi Over a year ago

thanks for responding. I tried that approach. I created an empty list and appended the values of the rows while reading from file1 and tried to write them in file2 but when I do that, file2 is turning out to be a duplicate of file1. Could you give an example of how the statement containing delimiter is written ?

underrun Over a year ago

you can set up your writer with different delimiters than your reader and that would let you write out the file in a different dialect.

hydi Over a year ago

I tried few combinations and fortunately one of them worked.Thanks for the inputs.

|

abhishekgarg · Accepted Answer · 2013-05-29 22:31:14Z

9

ok, here is what i understood from your question. You are writing a csv file from python but when you are opening that file into some other application like excel or open office they are showing the complete row in one cell rather than each word in individual cell. I am right??

if i am then please try this,

import csv

with open(r"C:\\test.csv", "wb") as csv_file:
    writer = csv.writer(csv_file, delimiter =",",quoting=csv.QUOTE_MINIMAL)
    writer.writerow(["a","b"])

you have to set the delimiter = ","

answered May 29, 2013 at 22:31

abhishekgarg

1,4639 silver badges14 bronze badges

1 Comment

hydi Over a year ago

In my case, what exactly do I write in the place of "a","b"?. I have thousands of records. The above mentioned in the question is only a small sample subset.

Milovan Tomašević · Accepted Answer · 2021-01-15 15:30:09Z

CSV Files with Custom Delimiters

By default, a comma is used as a delimiter in a CSV file. However, some CSV files can use delimiters other than a comma. Few popular ones are | and \t.

import csv
data_list = [["SN", "Name", "Contribution"],
             [1, "Linus Torvalds", "Linux Kernel"],
             [2, "Tim Berners-Lee", "World Wide Web"],
             [3, "Guido van Rossum", "Python Programming"]]
with open('innovators.csv', 'w', newline='') as file:
    writer = csv.writer(file, delimiter='|')
    writer.writerows(data_list)

output:

SN|Name|Contribution
1|Linus Torvalds|Linux Kernel
2|Tim Berners-Lee|World Wide Web
3|Guido van Rossum|Python Programming

Write CSV files with quotes

import csv

row_list = [["SN", "Name", "Contribution"],
             [1, "Linus Torvalds", "Linux Kernel"],
             [2, "Tim Berners-Lee", "World Wide Web"],
             [3, "Guido van Rossum", "Python Programming"]]
with open('innovators.csv', 'w', newline='') as file:
    writer = csv.writer(file, quoting=csv.QUOTE_NONNUMERIC, delimiter=';')
    writer.writerows(row_list)

output:

"SN";"Name";"Contribution"
1;"Linus Torvalds";"Linux Kernel"
2;"Tim Berners-Lee";"World Wide Web"
3;"Guido van Rossum";"Python Programming"

As you can see, we have passed csv.QUOTE_NONNUMERIC to the quoting parameter. It is a constant defined by the csv module.

csv.QUOTE_NONNUMERIC specifies the writer object that quotes should be added around the non-numeric entries.

There are 3 other predefined constants you can pass to the quoting parameter:

csv.QUOTE_ALL - Specifies the writer object to write CSV file with quotes around all the entries.
csv.QUOTE_MINIMAL - Specifies the writer object to only quote those fields which contain special characters (delimiter, quotechar or any characters in lineterminator)
csv.QUOTE_NONE - Specifies the writer object that none of the entries should be quoted. It is the default value.

import csv

row_list = [["SN", "Name", "Contribution"],
             [1, "Linus Torvalds", "Linux Kernel"],
             [2, "Tim Berners-Lee", "World Wide Web"],
             [3, "Guido van Rossum", "Python Programming"]]
with open('innovators.csv', 'w', newline='') as file:
    writer = csv.writer(file, quoting=csv.QUOTE_NONNUMERIC,
                        delimiter=';', quotechar='*')
    writer.writerows(row_list)

output:

*SN*;*Name*;*Contribution*
1;*Linus Torvalds*;*Linux Kernel*
2;*Tim Berners-Lee*;*World Wide Web*
3;*Guido van Rossum*;*Python Programming*

Here, we can see that quotechar='*' parameter instructs the writer object to use * as quote for all non-numeric values.

Collectives™ on Stack Overflow

How to use delimiter for CSV in Python?

3 Answers 3

6 Comments

1 Comment

CSV Files with Custom Delimiters

Write CSV files with quotes

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

CSV Files with Custom Delimiters

Write CSV files with quotes

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related