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, May 29 '13 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, May 29 '13 at 21:02
I wasn't exactly sure of what I should have in quotechar. So I left it blank. — hydi, May 29 '13 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, May 29 '13 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, May 29 '13 at 21:16
No, you don't. What is a cell? What separates cells? Are there other values in the row apart from `01 10 this is a sentence`? Most of all, can I have a sample of what your data looks like that you are writing. Include the `repr()` of the list in your post if you can. — Martijn Pieters, May 29 '13 at 21:17
I tried to edit the question and include a sample input/output. I'm not able to post images — hydi, May 29 '13 at 21:54
Hope now it helps. I tried even including csv.writer part in the for loop but didn't help. Every time I run, the file becomes blank. — hydi, May 29 '13 at 21:58
No, please advice me on that. Should I first assign it as say: xyz=csv.writer(f,delimiter='',quotechar=',',quoting=csv.QUOTE_MINIMAL) writer.writerow(xyz) — hydi, May 29 '13 at 22:30
No wonder your files are empty if you are not even writing anything. Why don't you first *try* to write some rows and see what results you have? — Martijn Pieters, May 29 '13 at 22:31
I initially realized it while looking through the python documentation but here in my case I don't know what to write or how to write it, i mean what would I write in the paranthesis of .writerow() ? Can you give me an example ? Thank you — hydi, May 30 '13 at 19:28
There is an example `.writerow()` call in abhishekgang's answer below. — Martijn Pieters, May 30 '13 at 19:31
Yes but I have a file with thousands of records. The input file consists of 2 rows (or fields) and the output file should consist of as many rows(or fields) that are needed. So i'm not sure how to populate .writerow( ) as. I tried .writerow([row[0],row[1]]) but it didn't work. — hydi, May 30 '13 at 21:01
Possible duplicate of [How do I read and write CSV files with Python?](http://stackoverflow.com/questions/41585078/how-do-i-read-and-write-csv-files-with-python) — Martin Thoma, Jan 17 '17 at 04:52

score 34 · Accepted Answer · answered May 29 '13 at 22:25

34

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 '13 at 22:25

underrun

6,713
2
41
53

1

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( ) ? – hydi May 31 '13 at 14:46
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. – underrun Jun 04 '13 at 23:50
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 ? – hydi Jun 05 '13 at 15:25
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. – underrun Jun 05 '13 at 23:24
I tried few combinations and fortunately one of them worked.Thanks for the inputs. – hydi Jun 06 '13 at 21:05
This answer leaks memory. Better to use `with open(...)` to create a context manager for the file resource. – ggorlen Feb 14 '21 at 16:07

score 9 · Answer 2 · answered May 29 '13 at 22:31

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 = ","

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. — hydi, May 31 '13 at 14:47

score 3 · Answer 3 · answered Jan 15 '21 at 15:30

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.

How to use delimiter for CSV in Python?

3 Answers3

CSV Files with Custom Delimiters

Write CSV files with quotes

Linked