CODEWITHSUNDEEP
pythonstringcsv
AlfredoGG
AlfredoGG

Reputation: 23

Python CSV writer: How to write upc code with leading zero as string?

I am using Python with csv writer to export a csv containing UPC codes (which occasionally contain leading zeros) in several columns.

The exported sheet should look something like this:

name, upc    
Blue Perfume, "0853520006427"   
Red Cologne, "0223520006427"   
Beige Tanner, "8292930112345"  
...

In my attempt to achieve this I have tried to add double quotes like so:

productSKU = '\"' + productSKU + '\"'

but this ends up outputing double double-quotes to the csv file like so:

""0853520006427""

I've read online suggestions to try adding an = to the beginning of the sheet so that Excel parses the cell as a string but this is unwanted behavior as it leaves the entry in the resulting csv file like this:

="0853520006427"

as opposed to this:

"0853520006427"

What is the proper way of achieving this end result with csv writer?

EDIT

Here is a quick python script I wrote to test with:

import csv

filename = 'output.csv'

with open(filename, 'w', newline='', encoding='utf-8') as csvfile:
    writer = csv.writer(csvfile)

    rowData = ['Blue Shampoo', '0853520006427']
    writer.writerow(rowData)

    rowData = ['Blue Shampoo', '\"0853520006427\"']
    writer.writerow(rowData)

    rowData = ['Blue Shampoo', '=\"0853520006427\"']
    writer.writerow(rowData)

    rowData = ['Blue Shampoo', '\'0853520006427']
    writer.writerow(rowData)

Notice the resulting data:

Blue Shampoo,0853520006427
Blue Shampoo,"""0853520006427"""
Blue Shampoo,"=""0853520006427"""
Blue Shampoo,'0853520006427

Upvotes: 0

Views: 1858

Answers (2)

CaptainMeow
CaptainMeow

Reputation: 351

I did some testing on my end, and it seems that enclosing the UPC code in double quotes doesn't help prevent EXCEL from reading the field as numbers. Same thing would happen in pandas. You can certainly do something like this: rowData = ['Blue Shampoo', "'0853520006427'"], which would output "Blue Shampoo,'0853520006427'. Excel would read the UPC code in as a string, but it'll have single quotes around it.

Upvotes: 0

Anshul Goyal
Anshul Goyal

Reputation: 77007

I think that you will have to explicitly provide a quote on all your data while writing to csv, and select QUOTE_ALL as the quoting strategy:

In [23]: import csv
    ...: 
    ...: filename = 'output.csv'
    ...: 
    ...: with open(filename, 'w', newline='', encoding='utf-8') as csvfile:
    ...:     writer = csv.writer(csvfile, delimiter=",", quotechar='"', quoting=csv.QUOTE_ALL)
    ...:     rowData = ['Blue Shampoo', '0853520006427']
    ...:     writer.writerow(rowData)
    ...:

Gives output:

"Blue Shampoo","0853520006427"

However, this will also put your first field within quotes (which will be consistent across all fields, so should be ok I guess).

Since the csv module only supports 4 types of quotes (QUOTE_ALL, QUOTE_MINIMAL, QUOTE_NONE, QUOTE_NONNUMERIC), this is the only option you have if you use the csv module. You could of course write to the file without the csv module in a csv compliant fashion.

Upvotes: 0

Related Questions