I’m writing a CSV file and need some blank fields inserted.
try:
fo = open(filepathw, "a")
fw = csv.writer(fo, delimiter=';')#, quotechar=' ', quoting=csv.QUOTE_MINIMAL)
fw.writerow([wid, wksn, wksnbar, wktn, wktnbar, wI, wr, wcode, wncode, wprofile,])
fo.close()
except Exception,e:
What I need is e.g.: fw.writerow([wid, wksn, wksnbar, “;;;;;;” wktn, wktnbar, wI, wr, “;;;” wcode, wncode, wprofile,])
However when the CSV is written the quotes are included. The rest of the data, wksn etc, comes from a scalarPrepQuery, so I tried having a static column in SQL with a semi-colon in it, but it still gets quote marks. As you can see from the commented out section I tried adding quoting style, no joy.
Any suggestions?
And to tag another problem onto this thread, if I save the file as .txt all the lines appear one after the other with no blank rows. If I rename as CSV and open in excel, every other row is blank :S
Try adding [None] in the list you’re sending to writerow, eg:
fw.writerow([wid, wksn, wksnbar, None, None, None, None, None, wktn, wktnbar, wI, wr, None, None, None, wcode, wncode, wprofile])
Thanks, I’ll give that a go tomorrow. The blank row thing is a show stopper as the machine that then reads the CSV see’s it as a null entry and can’t handle it. Perhaps if I explicitly write is as a CSV, not a TXT?
OK great, the ‘None’ method works… and as for the blank rows, fo = open(filepathw, “ab”) was the solution… thanks!
2 Likes
OK, so I’d actually ran into the quote issue before, with the header row. Having it as 1 field in the db static which I call at the start of each write, I was getting quotes around it.
So I gave up after a while of google’whacking and created a template.txt file with the header and had code to copy and rename that for every file, not very elegant. And, even with “ab” append binary, there was a blank row between the header row and the first row of proper data. I guess a way around it, which I’ll try tomorrow, is to have 32 static fields in its own table, each with the header value, that I call once at the file create stage.
But I’d like to know why this is?
When I output the file as CSV, open it in notepad, the quotes are there. When I then open it in Excel, the quotes aren’t there, and when I close the file it asks if I want to save it, which then removes the quotes permanently.
Any insight?
Setting the quoting to quote minimal or off entirely definitely should work - I’ve never noticed issues with it before. As for the Notepad vs Excel - that’s just Excel being “helpful” and manipulating the data for you. The csv module is even supposed to default to an Excel friendly format, so I’m honestly not sure what’s going on. Can you post what a row of your data looks like in the DB?
Yes, I’ll post tomorrow. I’ll try my hack-around first (static separate table with 32 columns) as, based on the fact the other data is coming from scalar-prep query’s and works, I think it will work. Just not too elegant.
Well seeing as it’s kinda tomorrow (past midnight)… I remembered I have a file on USB pen drive at home. OK,test6.csv (4.8 KB)
you asked for the raw DB end but might give some clues…
Edit; so yeah the hack-around works…
here’s the table screenshot:
(all field datatypes, barr index and off_fk, are ‘varchar’)
