CSV module issue with csv files containing extra commas

Question

I am following along the book Foundation for Analytics with Python by Clinton W. Brownley (O'Reilly Media Inc.)

For Chapter 2 - Read and Write a CSV File (Part 2) Base Python, with csv module

the script is as the following:

#!/usr/bin/env python3
import sys
import csv

input_file = sys.argv[1]
output_file = sys.argv[2]

with open(input_file, 'r', newline='') as csv_input_file:
    with open(output_file, 'w', newline='') as csv_output_file:

        filereader = csv.reader(csv_input_file, delimiter=',')
        filewriter = csv.writer(csv_output_file, delimiter=',')

        for row_list in filereader:
            print(row_list)
            filewriter.writerow(row_list)

the input file has fields containing commas (the dollar amounts in the last two lines):

Supplier Name,Invoice Number,Part Number,Cost,Purchase Date
Supplier X,001-1001,2341,$500.00,1/20/14
Supplier X,001-1001,2341,$500.00,1/20/14
Supplier X,001-1001,5467,$750.00,1/20/14
Supplier X,001-1001,5467,$750.00,1/20/14
Supplier Y,50-9501,7009,$250.00,1/30/14
Supplier Y,50-9501,7009,$250.00,1/30/14
Supplier Y,50-9505,6650,$125.00,2/3/14
Supplier Y,50-9505,6650,$125.00,2/3/14
Supplier Z,920-4803,3321,$615.00,2/3/14
Supplier Z,920-4804,3321,$615.00,2/10/14
Supplier Z,920-4805,3321,$6,015.00,2/17/14
Supplier Z,920-4806,3321,$1,006,015.00,2/24/14

running the script produces the following output in terminal:

['Supplier Name', 'Invoice Number', 'Part Number', 'Cost', 'Purchase Date']
['Supplier X', '001-1001', '2341', '$500.00', '1/20/14']
['Supplier X', '001-1001', '2341', '$500.00', '1/20/14']
['Supplier X', '001-1001', '5467', '$750.00', '1/20/14']
['Supplier X', '001-1001', '5467', '$750.00', '1/20/14']
['Supplier Y', '50-9501', '7009', '$250.00', '1/30/14']
['Supplier Y', '50-9501', '7009', '$250.00', '1/30/14']
['Supplier Y', '50-9505', '6650', '$125.00', '2/3/14']
['Supplier Y', '50-9505', '6650', '$125.00', '2/3/14']
['Supplier Z', '920-4803', '3321', '$615.00', '2/3/14']
['Supplier Z', '920-4805', '3321', '$615.00', '2/17/14']
['Supplier Z', '920-4804', '3321', '$6', '015.00', '2/10/14']
['Supplier Z', '920-4806', '3321', '$1', '006', '015.00', '2/24/14']

but the book show the expected output like this:

What am I doing wrong?

You're not doing anything wrong, CSV uses comma as a separator, which means that it can't be used anywhere else, including numbers... So technically 1,006,015.00 is not valid number in CSV format. — Nir Alfasi
– Nir Alfasi, Commented Aug 20, 2017 at 0:58
It's quite possible the book's example is just wrong. This doesn't appear to be listed in the confirmed errata though. Are you sure the input file looks like that? Is it actually a plain text file? — Gerrat
– Gerrat, Commented Aug 20, 2017 at 1:06
just double-checked, the screenshot of Figure 2-7 shows the interface of Excel. Using applications like Excel or Numbers to modify the csv file, then export as csv, the cells containing commas would become enclosed by doublequotes — mediter
– mediter, Commented Aug 20, 2017 at 15:08

martineau · Accepted Answer · 2017-08-20 02:05:12Z

7

You have three ways to correct your output:

Remove the commas from the money amounts.
Use QUOTING: Wrap the money amount in double quotes. For example, in the first row $500.00 will be "$500.00". Quoting is a popular technique. When using quoting, change your read statement to this:

filereader = csv.reader(csv_input_file, delimiter=',', quotechar='"')
Use a different delimiter. You don't have to use a comma as the delimiter. To use this method, change the delimiters in your input file to another delimiter. I like pipe-delimited files because pipes are rarely used as text.

filereader = csv.reader(csv_input_file, delimiter='|')

edited Aug 20, 2017 at 2:05

martineau

124k29 gold badges181 silver badges319 bronze badges

answered Aug 20, 2017 at 1:22

LAS

8195 silver badges7 bronze badges

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

1 Comment

LAS Over a year ago

I disagree with the author on this. The comma is used to identify where one field begins and another ends. If you include commas within one of your fields as text, the interpreter can't distinguish them from the end of field delimiter which is what you saw in your output. You have to give it a way to distinguish between a field delimiter and text within the field. Try the quoting solution and see for yourself.

mediter · Accepted Answer · 2017-08-20 15:15:55Z

0

just double-checked, the screenshot of Figure 2-7 shows the interface of Excel.

Using applications like Excel or Numbers to modify the csv file, then export as csv, the cells containing commas would become enclosed by doublequotes

Thank you all for the detailed explanations!

edited Aug 20, 2017 at 15:15

answered Aug 20, 2017 at 15:08

mediter

691 silver badge8 bronze badges

1 Comment

LAS Over a year ago

You're welcome. The best way to thank us on stackoverflow is to accept the answer if it helped you.

Collectives™ on Stack Overflow

CSV module issue with csv files containing extra commas

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related