Trouble in saving a list to csv

Question

I am saving a list to a csv using the writerow function from csv module. Something went wrong when I opened the final file in MS office Excel. Before I encounter this issue, the main problem I was trying to deal with is getting the list saved to each row. It was saving each line into a cell in row1. I made some small changes, now this happened. I am certainly very confused as a novice python guy.

import csv
inputfile = open('small.csv', 'r')
header_list = []
header = inputfile.readline()
header_list.append(header)
input_lines = []
for line in inputfile:
    input_lines.append(line)
inputfile.close()

AA_list = []
for i in range(0,len(input_lines)):
    if (input_lines[i].split(',')[4]) == 'AA':#column4 has different names including 'AA'
         AA_list.append(input_lines[i])

full_list = header_list+AA_list

resultFile = open("AA2013.csv",'w+')
wr = csv.writer(resultFile, delimiter = ',')
wr.writerow(full_list)

Thanks! The text is parsed differently from before

UPDATE: The full_list look like this: ['1,2,3,"MEM",...]

UPDATE2(APR.22nd): Now I got three cells of data(the header in A1 and the rest in A2 and A3 respectively) in the same row. Apparently, the newline signs are not working for three items in one big list. I think the more specific question now is how do I save a list of records with '\n' behind each record to csv.

UPDATE3(APR.23rd): original file enter image description here

Can you include the beginning of `full_list` in your answer? Also your second for-loop is very un-pythonic, you should rather do `for il in input_lines:` and then replace `input_lines[i]` with just `il`. — Jasper, Apr 22 '14 at 07:36
There are numerous issues with your code, which I understand is because you're very new to Python. I strongly recommend investing the time to read through the [tutorial](https://docs.python.org/2/tutorial/) (or [this](https://docs.python.org/3/tutorial/) if you're using Python 3.x). Also study the [`csv` module docs](https://docs.python.org/2/library/csv.html), which include some examples how to use it. — John Y, Apr 22 '14 at 16:07

score 0 · Answer 1 · answered Apr 22 '14 at 15:58

0

Importing the csv module is not enough, you need to use it as well. Right now, you're appending each line as an entire string to your list instead of a list of fields.

Start with

with open('small.csv', 'rb') as inputfile:
    reader = csv.reader(inputfile, delimiter=",")
    header_list = next(reader)
    input_lines = list(reader)

Now header_list contains all the headers, and input_lines contains a nested list of all the rows, each one split into columns.

I think the rest should be pretty straightforward.

answered Apr 22 '14 at 15:58

Tim Pietzcker

328,213
58
503
561

Any particular reason you specify the delimiter? (Not saying it's wrong, just asking.) – John Y Apr 22 '14 at 16:03
I realized if I make input_lines a list object, I will have to change the rest of my code. I found a solution for flat list(full_list in my case), yet the three results are still not in ideal segments(each record in one cell taking A1 to C1 in excel). http://gis.stackexchange.com/questions/72458/export-list-of-values-into-csv-or-txt-file – WHZW Apr 23 '14 at 04:02

Sufian Latif · Answer 2 · 2014-04-23T03:34:28.023

0

append() appends a list at the end of another list. So when you write header_list.append(header), it takes header as a list of characters and appends to header_list. You should write

headers = header.split(',')
header_list.append(headers)

This would split the header row by commas and headers would be the list of header words, then append them properly after header_list.

The same thing goes for AA_list.append(input_lines[i]).

edited Apr 23 '14 at 03:34

answered Apr 22 '14 at 15:59

Sufian Latif

13,086
3
33
70

I corrected the mistakes accordingly in my code and got three cells of data(the header in A1 and the rest in A2 and A3 respectively) in the same row. Apparently, the newline signs are not working for three items in one big list. – WHZW Apr 23 '14 at 03:17
Can you provide the first few lines of the csv file? – Sufian Latif Apr 23 '14 at 03:34
The original csv? Or the output? – WHZW Apr 23 '14 at 03:37
I uploaded a picture of the original data. – WHZW Apr 23 '14 at 03:51

score 0 · Answer 3 · edited May 23 '17 at 12:25

I figured it out. The different between [val], val, and val.split(",") in the writerow bracket was:

[val]: a string containing everything taking only the first column in excel(header and "2013, 1, 2,..." in A1, B1, C1 and so on ).

val: each letter or comma or space(I forgot the technical terms) take a cell in excel.

val.split(","): comma split the string in [val], and put each string separated by comma into an excel cell.

Here is what I found out: 1.the right way to export the flat list to each line by using with syntax, 2.split the list when writing row

csvwriter.writerow(JD.split())

full_list = header_list+AA_list
with open("AA2013.csv",'w+') as resultFile:
    wr = csv.writer(resultFile, delimiter= ",", lineterminator = '\n')
    for val in full_list:
        wr.writerow(val.split(','))

The wanted output enter image description here

Please correct my mistakenly used term and syntax! Thanks.

Trouble in saving a list to csv

3 Answers3