Greetings

I'm running in circes trying to accomplish something very simple. I have a csv file with multiple lines resembling:

0002345, 24, 5, 0.24

I need this format in my output file:
'0002345', 24, 5 0.24

The first entry is a census meshblock code and the leading zeros are relevant for comparison purposes. After extensive googling, I have found half a dozen different ways to convert 0002345 into a string, all of which work brilliantly at the prompt. However, when thrown into a for loop, the csv I get always returns the item without the 'quotes'.

Here's the fourth version that works at the prompt but not in my loop:

wf1 = open('meshoutE.txt', 'w') #output file handle
writer1 = csv.writer(wf1)
for entry in rf1:
    entry[0] = str(entry[0]).zfill(7)
    writer1.writerow(entry)
wf1.close()

I've tried many variations on the theme without success, in both 2.6 and 3.1. I cannot seem to get the quotes into the output. I am clearly missing the forest for the trees! Anyone got an ax?

Recommended Answers

All 6 Replies

Correction:

I need this format in my output file:
'0002345', 24, 5, 0.24

Mustn't drop the commas!

I suggest

entry0 = "'{e:0>7}'".format(e=entry[0])

May you tell, why you need single quotes, which you can do many ways, including repr function?

What does rf1 look like? Need only a few data lines.

As far as I understand csv.writer() turns all data into strings, however you can specifiy the quote character this way:
writer1 = csv.writer(wf1, quotechar="'")
I know that's hard to see, but this is a single quote inside double quotes.

You can also specify the dialect of the csv file, for instance 'excel'

Kia ora folks

All fair questions. I didn't articulate well in the post. I don't care if it's single or double quotes, only that my column one data is formatted as text and not a number, so that I can do a join to another database, where the meshblock field is text and includes the leading zeros. The csv.reader certainly turns everything into text which is why all my code to date has never tripped over this, as even reading from the secondary database comes in as text. However, as I am learning, whenever the csv.writer writes this to a text or csv file, it's formatted numeric, even when the input was text and even when I've converted it to string as in the above example.

Here is a sample of the rf1 input, as cut and pasted from the text editor. (That way you see the quotes where things are formatted as text) In a spreadsheet programme, all the leading zeros get truncated as the format is numeric for column one, and this format issue is preventing the join at the next step in my work:

"MB_JOIN","HSECNT06","POP_06","PV","PVPH","GED_07"
0000100,0,3,3,0,34
0000200,24,87,2,0.08,34
0000300,24,75,7,0.29,34
0000502,12,24,1,0.08,34
0000600,15,45,2,0.13,34
0000700,24,66,5,0.21,34
0000800,45,150,3,0.07,34
0000900,9,27,2,0.22,34
0001000,27,63,7,0.26,34

It seems laughably simple but it has tripped me up for many an hour yesterday! If I could just get column one looking like this:

"0000100" or this:
'0000100' then Bob's my uncle!

Kia ora

Having answered vegaseat's question, I went on to test Gribouillis' suggestion, which worked like a charm. (I hadn't seen this one in all the variations google provided) For completeness, I commented out my str.zfill and added the suggestion, adding a missing pair of brackets to his/her opening variable:

wf1 = open('meshoutE.txt', 'w') #output file handle
writer1 = csv.writer(wf1)
for entry in rf1:
    entry[0] = "'{e:0>7}'".format(e=entry[0])
    #entry[0] = str(entry[0]).zfill(7)
    writer1.writerow(entry)
wf1.close()

The input from rf1 (pasted above) came out like this:
'MB_JOIN',HSECNT06,POP_06,PV,PVPH,GED_07
'0000100',0,3,3,0,34
'0000200',24,87,2,0.08,34
'0000300',24,75,7,0.29,34
'0000502',12,24,1,0.08,34
'0000600',15,45,2,0.13,34
'0000700',24,66,5,0.21,34
'0000800',45,150,3,0.07,34
'0000900',9,27,2,0.22,34
'0001000',27,63,7,0.26,34

My aging database was happy and the join went well while I had a fine breakfast. Gold star!

Many thanks! I have rarely posted since 2007 because I usually find the answer first and am not often competent to answer other's queries. It's a great resource!

commented: thanks for letting us know +15
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.