Writing to CSV file but wrong formatting

Question

I am trying to write to an empty CSV file.

I want the output on the CSV file to be something like this:

Date,Open,High,Low,Close,Volume,Adj Close
2014-03-28,538.32,538.94,534.25,536.86,7106900,536.86
2014-03-27,540.02,541.50,535.12,537.46,7929700,537.46
2014-03-26,546.52,549.00,538.86,539.78,10706000,539.78
2014-03-25,541.50,545.75,539.59,544.99,10081900,544.99
2014-03-24,538.42,540.50,535.06,539.19,12703600,539.19
2014-03-21,531.93,533.75,526.33,532.87,13373200,532.87
2014-03-20,529.89,532.67,527.35,528.70,7442800,528.70
2014-03-19,532.26,536.24,529.00,531.26,8027000,531.26
2014-03-18,525.90,531.97,525.20,531.40,7487400,531.40
2014-03-17,527.70,529.97,525.85,526.74,7126600,526.74

But I end up getting a very large number of rows, every character is a different cell.

My code is:

with open('prova.csv','w',newline='') as f:
    writer = csv.writer(f,delimiter=',',quotechar='"')
    writer.writerows(request3)
    f.flush()
    f.close()

And the output of request 3 is the same table as above.

You shouldn't need to close the file, and I'm 99% sure you also don't need to flush it. Please show us the contents of request3 as a python object so we can figure it out. — 2rs2ts
– 2rs2ts, Commented Apr 1, 2014 at 16:40

Wayne Werner · Accepted Answer · 2014-04-01 16:42:32Z

2

If request3 is this string:

request3 = """Date,Open,High,Low,Close,Volume,Adj Close
2014-03-28,538.32,538.94,534.25,536.86,7106900,536.86
2014-03-27,540.02,541.50,535.12,537.46,7929700,537.46
2014-03-26,546.52,549.00,538.86,539.78,10706000,539.78
2014-03-25,541.50,545.75,539.59,544.99,10081900,544.99
2014-03-24,538.42,540.50,535.06,539.19,12703600,539.19
2014-03-21,531.93,533.75,526.33,532.87,13373200,532.87
2014-03-20,529.89,532.67,527.35,528.70,7442800,528.70
2014-03-19,532.26,536.24,529.00,531.26,8027000,531.26
2014-03-18,525.90,531.97,525.20,531.40,7487400,531.40
2014-03-17,527.70,529.97,525.85,526.74,7126600,526.74"""

Then yes, you're going to have problems. From the documentation

A row must be a sequence of strings or numbers for Writer objects and a dictionary mapping fieldnames to strings or numbers (by passing them through str() first) for DictWriter objects.

So if you're not using a DictWriter then you'll want to have data that looks like:

request3 = [['Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'Adj Close'],
            ['2014-03-28', '538.32', '538.94', '432', '123'],
            # more rows here
            ]

answered Apr 1, 2014 at 16:42

Wayne Werner

52.3k35 gold badges213 silver badges304 bronze badges

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

3 Comments

2rs2ts Over a year ago

If request3 is that string, why not just write it to the file directly? Assuming that there should have been newlines in it.

Paolo Over a year ago

I had converted request3 to a list, being = 'Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'Adj Close\n2014-03-28', '538.32', '538.94', '534.25', '536.86', '7106900', '536.86\n2014-03-27', '540.02', '541.50', ecc by doing request3 = request3[1:-1].split(',')

2rs2ts Over a year ago

@Paolokiller note how you're writing a 1D list. You need a 2D list.

2rs2ts · Accepted Answer · 2014-04-01 17:01:33Z

Based on your feedback in a comment, You were doing the following:

request3 = request3[1:-1].split(',')

Which got you a 1-dimensional list:

['Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'Adj Close\n2014-03-28',
'538.32', '538.94', '534.25', '536.86', '7106900', '536.86\n2014-03-27',
'540.02', '541.50', ...]

So it looks like your original request3 was a string like this:

"""Date,Open,High,Low,Close,Volume,Adj Close
2014-03-28,538.32,538.94,534.25,536.86,7106900,536.86
2014-03-27,520.02,541.50,..."""

No good. You seek a 2-dimensional list like so:

[['Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'Adj Close'],
 ['2014-03-28', '538.32', '538.94', '534.25', '536.86', '7106900', '536.86'],
 ['2014-03-27', '540.02', '541.50', ...]]

Luckily, not too hard to fix. You need to get each line first, then split each line by the commas. Although the multi-line string I showed doesn't have newline literals in it, they are there; you saw this in your output e.g. 'Adj Close\n2014-03-28'. So, split on newline characters first!

request3Lines = request3[1:-1].split('\n')
request3Separated = [line.split(',') for line in request3Lines]

An example (ignoring the slice you used for some reason):

>>> request3 = """Date,Open,High,Low,Close,Volume,Adj Close
2014-03-28,538.32,538.94,534.25,536.86,7106900,536.86
2014-03-27,540.02,541.50,535.12,537.46,7929700,537.46"""
>>> request3Lines = request3.split('\n')
>>> request3Lines
['Date,Open,High,Low,Close,Volume,Adj Close', '2014-03-28,538.32,538.94,534.25,536.86,7106900,536.86', '2014-03-27,540.02,541.50,535.12,537.46,7929700,537.46']
>>> request3Separated = [line.split(',') for line in request3Lines]
>>> request3Separated
[['Date', 'Open', 'High', 'Low', 'Close', 'Volume', 'Adj Close'], ['2014-03-28', '538.32', '538.94', '534.25', '536.86', '7106900', '536.86'], ['2014-03-27', '540.02', '541.50', '535.12', '537.46', '7929700', '537.46']]

Thank you very much, it works! I now have the CSV file in the format I wanted.

Collectives™ on Stack Overflow

Writing to CSV file but wrong formatting

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related