CODEWITHSUNDEEP
pythoncssexceldataframe
Arman Çam
Arman Çam

Reputation: 135

Writing data into a CSV file (Excel view)

I have a code like

from random import randint
from string import ascii_lowercase

file = open("data.csv", "w")

numbers = []
for j in range(26):
    # generating a list of random number
    random_num = [randint(0, 7) for i in range(120)]
    numbers.append(random_num)

# matching these numbers with the letters
survey = dict(zip(list(ascii_lowercase), numbers))
for (key, value) in survey.items():
    test_num = ",".join(str(i) for i in value)
    file.write(key + "," + test_num + "\n")

file.close()

and when I open this in excel I get

[![enter image description here][1]][1]

but I need something like

[![enter image description here][2]][2]

How can I do this in Python (by using code) [1]: https://i.sstatic.net/uZACn.png [2]: https://i.sstatic.net/jajl1.png

Upvotes: 1

Views: 45

Answers (2)

René Höhle
René Höhle

Reputation: 27325

You can do different things first CSV is a comma separated file. The easiest way when you work with Excel is to use a ; as separator. Then Excel open it correctly.

When you plan to work much more with python it's recommended to use a lib which can handle excel sheets.

But if you only need a temp file use the first solution.

Upvotes: 0

joao
joao

Reputation: 2293

Even though csv stands for "comma-separated values", using that suffix in the file name isn't enough for Excel to treat it like that, Excel actually thinks that TAB is the default separator. Your excel is just putting each line of the file into the first cell of each row.

Rename your output file to "data.txt". When you open it in Excel, this will trigger the Text Import Wizard, where you can change the separator from TAB to comma.

Upvotes: 1

Related Questions