How to Write Python Output into an Excel CSV File?

[Arman777]

I have a code like

from random import randint
from string import ascii_lowercase

file = open("data.txt", "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 the output is

a 4 4 7 6 1 5 3 2 4 1 6 0 4 2 4 7 0 7 5 3 7 7 0 1 3 5 6 1 6 7 7 4 0 5 3 7 2 7 7 4 5 0 7 7 4 5 3 3 1 3 5 5 5 1 0 6 4 5 1 3 0 0 3 6 6 7 0 1 7 5 2 4 4 7 5 7 7 4 5 0 1 2 6 1 7 2 7 1 4 1 7 2 4 0 6 1 7 0 1 5 6 2 0 1 5 4 6 1 3 6 5 0 7 6 2 0 3 3 2 5
b 3 6 1 4 6 3 5 4 2 0 6 3 7 2 0 3 3 2 6 6 5 1 0 1 1 3 3 6 4 7 1 2 2 5 1 2 6 6 5 5 7 6 0 6 6 5 3 3 4 7 1 6 7 4 1 6 2 7 5 3 0 5 7 4 5 4 6 7 4 3 2 2 3 3 4 3 6 0 4 3 7 0 5 5 2 0 6 3 0 2 5 5 2 1 1 1 2 0 6 3 6 4 5 7 2 6 3 2 0 6 2 6 3 7 6 1 7 0 2 0
c 2 1 6 6 0 6 5 6 4 3 0 1 2 5 6 2 3 7 5 2 0 7 6 1 6 2 4 5 1 2 6 4 2 7 5 0 4 4 2 2 0 2 5 6 5 6 2 0 1 6 5 4 0 3 2 3 5 4 6 5 0 4 2 6 6 4 2 0 7 5 1 5 1 0 3 7 0 7 2 1 2 3 4 6 6 7 7 1 0 4 1 7 4 0 1 1 3 6 0 3 0 3 0 4 2 3 1 1 6 1 7 5 1 4 2 3 6 6 0 0
d 1 1 4 0 0 7 5 0 2 2 5 6 7 6 5 2 4 6 7 0 3 3 6 1 2 4 0 0 5 4 1 2 2 6 7 0 6 4 1 7 6 5 4 7 0 5 1 7 2 2 2 0 4 3 5 3 2 2 6 6 3 2 1 5 4 1 3 7 6 4 5 4 6 4 0 4 4 0 4 4 5 7 3 4 2 4 0 7 1 4 4 0 2 0 4 0 6 1 4 0 3 1 4 4 0 6 0 6 7 5 3 0 2 0 2 4 2 3 0 6
e 3 3 2 3 6 7 7 5 3 5 6 5 3 1 4 4 7 6 5 4 2 0 5 6 3 5 3 2 2 6 7 3 2 6 6 0 6 6 4 5 0 7 0 2 0 3 2 5 0 0 2 5 6 2 4 3 3 6 5 2 1 5 3 1 6 5 3 5 4 3 0 5 6 3 2 7 1 7 4 6 0 2 6 2 3 4 2 1 3 6 7 6 6 0 6 1 4 6 4 5 1 0 6 0 1 0 2 5 1 3 0 2 3 1 4 3 7 7 2 1
f 1 0 7 2 7 5 0 0 7 4 5 2 0 3 0 6 2 0 0 6 3 7 3 6 0 6 7 6 2 2 6 3 5 3 3 5 5 7 5 3 5 1 4 2 5 6 1 6 3 5 2 0 2 1 6 2 4 5 4 6 3 2 7 6 0 0 0 5 3 5 5 0 6 5 4 4 3 3 6 1 5 6 2 4 3 2 5 2 4 4 3 5 4 3 3 5 0 1 5 7 1 7 6 2 6 4 5 7 0 7 0 7 3 2 0 0 4 7 2 5
g 6 6 1 6 1 4 0 6 5 2 5 3 2 7 3 5 2 1 2 6 0 7 6 6 1 0 7 3 6 5 5 2 3 7 0 0 7 6 0 5 4 6 3 4 6 5 6 1 7 6 4 5 6 2 7 5 6 3 3 6 5 7 3 5 4 5 2 0 0 7 4 7 2 7 6 7 5 6 6 1 4 5 3 1 3 6 0 7 2 4 5 1 2 0 0 3 7 1 3 7 6 6 6 3 6 2 2 6 1 1 0 6 4 1 0 5 2 6 3 2
h 6 5 5 4 7 0 1 6 7 0 6 3 5 1 4 1 2 4 5 0 1 7 7 4 5 0 7 1 4 4 0 6 4 1 6 7 7 0 6 5 3 6 7 0 2 1 1 4 0 6 2 1 3 5 6 6 1 1 0 0 4 5 7 7 2 3 6 7 1 7 2 3 1 6 7 5 2 1 2 2 6 1 2 3 3 2 7 3 7 6 3 1 3 0 2 2 6 6 6 2 5 3 3 0 1 1 4 3 2 3 6 7 5 4 6 6 5 4 7 0
i 4 1 6 5 1 0 2 7 4 4 4 6 3 7 6 4 3 1 1 5 6 2 3 2 5 3 5 1 0 0 4 6 1 5 3 4 4 6 2 0 0 3 2 3 0 5 7 2 1 7 0 2 2 6 2 1 0 1 1 2 5 0 5 4 3 4 7 0 5 7 2 6 6 2 6 7 7 2 7 1 7 5 6 2 1 0 4 3 7 1 5 6 5 7 6 5 0 1 0 0 5 1 0 0 6 3 6 5 3 3 6 6 5 4 6 6 2 2 3 5
j 1 6 1 5 4 7 1 0 3 4 5 5 5 2 4 6 1 3 1 1 5 0 5 2 2 3 7 3 4 1 1 7 2 5 7 7 0 6 6 4 4 7 3 7 6 4 3 7 3 2 6 2 1 1 2 3 3 2 4 6 0 0 0 4 7 3 6 1 7 2 2 0 4 4 4 1 7 4 2 0 2 2 3 5 0 1 6 6 7 7 6 4 3 3 7 5 0 5 3 4 4 4 5 6 0 7 5 3 0 1 6 3 3 2 2 5 4 7 1 7
k 5 2 7 0 7 6 5 7 3 4 1 5 0 6 0 0 7 2 1 5 6 6 5 2 2 7 0 2 0 4 4 3 6 3 0 7 3 4 7 2 5 2 3 1 0 5 6 6 2 6 5 7 2 3 6 5 5 3 7 2 5 2 4 1 2 6 7 1 5 1 4 2 5 2 7 3 4 2 2 7 6 5 1 3 7 5 4 4 2 2 2 7 7 7 6 5 0 6 4 5 1 1 6 0 0 3 3 1 0 5 3 3 5 2 3 6 7 2 7 3
l 7 4 5 0 2 4 5 1 1 0 6 3 6 1 6 0 7 0 4 4 1 4 0 3 3 6 4 3 1 3 0 2 5 6 4 1 6 2 5 3 7 3 6 1 2 4 5 4 3 1 7 7 6 5 5 0 6 1 2 4 7 1 4 2 7 0 6 3 1 7 6 4 4 1 2 5 2 0 5 4 4 2 0 4 4 4 0 7 1 0 4 4 7 3 7 4 0 2 3 4 0 6 4 7 7 6 5 4 1 1 3 3 2 0 0 0 3 5 1 1
m 7 3 1 4 6 4 0 4 4 3 0 4 2 5 4 4 1 0 6 7 0 7 0 2 5 6 7 5 1 0 7 5 5 2 3 7 4 5 4 4 4 4 6 1 3 4 1 2 6 4 6 0 5 2 0 3 0 5 5 6 0 1 1 7 4 1 0 2 0 6 6 0 0 6 0 0 2 4 1 0 6 1 7 7 5 3 5 0 5 3 6 6 4 7 7 0 3 7 3 7 2 2 6 2 1 6 6 2 2 5 7 2 1 4 0 6 7 5 1 7
n 3 3 7 1 4 3 5 3 0 0 5 6 2 2 6 4 6 7 5 1 2 0 6 2 5 5 4 2 5 0 2 4 0 2 0 1 7 6 2 6 5 5 5 3 4 7 1 6 2 2 3 6 1 2 4 2 2 5 7 7 6 2 1 1 2 0 5 4 4 0 0 0 0 2 4 4 3 5 7 3 4 2 0 2 3 3 1 0 4 0 5 5 5 3 3 3 5 5 1 0 2 6 2 2 0 6 6 5 4 3 2 0 6 6 0 7 1 7 7 4
o 2 7 4 1 0 1 0 6 1 3 0 0 3 2 4 5 5 1 4 0 1 0 2 7 2 2 1 3 6 2 3 2 3 5 5 6 4 2 2 4 6 5 4 1 6 5 2 6 1 2 7 7 3 7 4 7 6 1 4 1 4 1 6 2 2 6 3 6 4 6 3 1 3 2 2 1 1 6 7 7 3 2 5 0 7 2 7 4 6 3 4 0 1 3 0 2 2 3 0 7 5 1 0 2 1 6 2 4 2 2 6 1 5 1 3 7 7 0 2 0
p 4 5 4 7 2 4 7 7 3 7 2 6 6 4 7 4 7 7 3 2 4 7 0 7 3 5 5 4 3 5 5 1 0 3 3 7 0 2 6 7 6 6 4 3 4 7 5 4 6 1 0 2 5 2 2 6 7 7 6 3 2 6 7 5 6 7 2 4 2 5 4 3 1 2 5 6 5 6 6 6 7 2 6 7 7 5 3 3 1 7 5 5 5 0 3 5 1 1 7 4 1 4 3 6 4 3 6 6 5 2 6 4 7 1 5 1 4 1 1 2
q 5 6 6 3 4 4 7 0 7 2 3 7 2 1 3 6 1 3 1 6 6 0 5 4 5 0 1 5 5 7 0 7 5 4 4 0 5 6 5 5 0 0 2 0 0 1 5 2 2 0 4 3 5 1 5 5 3 3 0 0 1 3 6 6 5 4 4 0 3 1 6 1 5 0 5 2 1 7 2 3 2 1 6 1 2 1 0 6 5 0 7 6 6 3 2 5 7 4 0 0 2 5 6 5 1 2 0 7 5 2 5 3 6 0 7 1 4 3 5 1
r 0 3 6 3 2 4 1 0 1 1 5 7 5 3 5 6 3 7 1 3 5 6 1 6 0 5 7 5 7 3 0 4 3 2 7 5 5 1 0 1 6 0 2 3 7 3 1 5 5 7 6 0 5 6 5 0 3 1 1 0 1 4 1 2 7 3 0 0 0 2 1 7 2 4 2 4 7 5 3 7 6 5 5 2 5 7 0 4 3 0 4 7 7 6 7 6 6 0 4 7 0 3 0 2 0 2 5 1 3 3 4 2 2 3 7 0 1 5 4 5
s 0 4 1 3 4 6 1 3 5 0 3 4 7 3 4 7 7 0 6 4 2 7 1 0 7 2 6 1 0 4 2 3 7 4 4 4 4 3 4 3 1 0 6 1 4 1 5 6 2 5 0 3 4 7 2 6 5 3 5 6 2 1 7 4 6 4 1 4 6 7 5 1 3 4 2 1 2 5 3 3 7 1 2 7 3 2 4 1 0 5 6 7 1 6 2 7 4 0 7 0 5 1 5 6 6 5 6 2 2 5 0 5 6 4 5 5 1 0 2 7
t 1 1 6 2 6 6 4 3 5 5 0 2 0 0 2 2 0 2 3 1 7 0 1 2 2 2 0 5 3 7 5 3 0 6 7 5 1 3 3 7 2 4 2 0 7 0 7 2 6 5 4 7 0 0 3 4 1 1 2 6 3 5 7 0 4 6 4 7 3 4 4 6 3 1 3 3 1 7 5 6 0 4 4 6 2 4 4 4 3 5 7 6 2 2 0 6 2 3 5 2 7 7 5 3 0 6 3 5 6 5 4 2 1 0 6 0 4 0 6 1
u 5 0 0 4 3 7 0 5 0 6 4 1 5 0 4 3 3 1 5 4 6 0 7 3 0 6 0 5 5 0 5 1 6 3 4 0 1 7 4 3 2 1 5 2 5 3 5 6 6 2 4 5 6 3 3 4 2 1 7 0 2 4 6 5 4 4 1 3 7 4 0 0 4 6 2 2 6 3 1 2 0 1 7 7 1 5 1 3 0 0 6 1 0 1 0 5 0 2 2 2 7 7 3 2 4 1 1 6 7 4 5 7 0 6 6 5 2 2 5 7
v 5 1 7 3 7 7 5 1 7 1 5 0 0 3 6 5 7 3 2 6 6 2 3 7 6 3 6 4 2 7 7 7 2 1 3 5 5 5 4 0 7 4 3 3 7 1 5 2 0 1 0 2 3 6 6 0 1 2 7 5 7 6 1 2 7 0 0 7 7 2 2 6 4 1 4 7 3 3 5 1 7 5 0 7 6 5 1 6 3 6 1 5 3 7 0 6 3 5 6 0 1 7 4 7 2 5 3 4 7 0 2 3 7 3 5 1 7 2 4 5
w 2 5 0 1 2 1 2 7 0 2 4 3 6 0 3 3 2 1 4 6 1 1 4 7 6 5 4 4 3 5 5 2 2 1 3 7 5 1 0 2 7 6 1 5 0 7 5 4 3 0 6 4 3 1 0 7 7 2 0 3 3 5 5 0 7 1 4 7 7 2 4 1 3 6 7 0 2 7 7 5 1 5 5 5 5 1 0 5 3 5 0 5 2 3 3 7 7 2 5 7 7 0 0 5 4 7 0 6 5 0 7 4 0 6 5 6 0 6 0 2
x 4 4 0 0 6 1 6 7 2 0 1 5 0 7 0 7 0 1 2 3 3 4 6 2 2 7 5 1 3 1 2 3 2 3 4 3 3 2 6 3 1 6 4 1 2 1 0 4 3 4 7 5 5 0 5 6 3 7 1 5 7 1 1 1 0 5 3 0 7 4 7 0 6 0 5 6 2 2 5 6 5 1 3 0 7 3 0 2 3 6 1 7 0 0 1 1 3 7 6 5 0 7 2 1 2 3 2 6 2 2 3 5 1 5 1 0 5 3 4 7
y 1 1 4 3 4 7 6 3 4 5 1 6 7 4 6 1 6 0 3 0 1 4 1 6 4 0 0 1 3 0 6 5 2 1 0 6 5 1 4 4 3 3 2 5 3 0 2 1 0 3 4 7 5 3 7 2 2 2 1 1 6 6 5 5 5 6 2 0 6 3 5 3 7 7 7 2 0 4 1 1 2 4 4 6 3 4 5 6 4 2 6 2 6 0 1 7 4 6 0 1 7 6 7 1 4 3 5 3 2 6 4 4 5 1 2 4 3 4 0 4
z 0 0 1 1 5 6 3 6 1 6 2 2 4 4 3 5 2 5 4 0 5 2 5 7 7 2 2 2 7 5 2 7 0 1 1 5 0 3 0 5 5 1 4 1 6 5 6 1 7 7 0 5 4 7 2 1 5 2 0 3 0 1 4 5 5 3 7 2 7 2 3 7 2 6 1 2 6 1 4 1 4 0 1 2 3 0 2 3 4 1 6 1 1 6 7 7 6 6 3 2 6 3 7 3 7 6 6 1 3 6 7 5 3 7 5 3 0 2 1 1

Now I need to write this into a csv (Excel file) rather than txt. The format of the excel must be something like this

a	2​	0​	.
b	2​	2​	.
c	3​	4​	.
d	1​	5​	.
e	6​	6​	.
f	4​	1​	.
.	.	.
.	.	.
.	.	.

How can I do this ?

 

[PeterDonis]

How can I do this ?

AFAIK you can tell Excel when loading a text file that column values are separated by spaces, so you might be able to load the text file you already have and have Excel treat it the way you want.

If you need to have a CSV file, well, that just means the column values are separated by commas, not spaces. Can you see where those separators (currently spaces) are defined in your source code? (Hint: there is one on line 15 and one on line 16.)

 

[Arman777]

AFAIK you can tell Excel when loading a text file that column values are separated by spaces, so you might be able to load the text file you already have and have Excel treat it the way you want.

If you need to have a CSV file, well, that just means the column values are separated by commas, not spaces. Can you see where those separators (currently spaces) are defined in your source code? (Hint: there is one on line 15 and one on line 16.)

If you mean this

from random import randint
from string import ascii_lowercase
import csv

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()

I get this

 

[FactChecker]

It looks like you have generated a valid CSV file but have loaded it into EXCEL wrong. Did you tell EXCEL to use comma separation when the file is opened into EXCEL?

 

[PeterDonis]

I get this

Open the file in a text editor; it should be values separated by commas on each line. That's a CSV file.

Excel might not be loading it properly. The line ending character you are using is for Unix/Linux, so if you're on Windows or MAC Excel might not be properly interpreting it. Also there might be character encoding issues; as your code is written it will save the file with the default encoding of whatever Python you are running, probably UTF-8; but Excel might not default to UTF-8 when loading. Finally, Excel's definition of "CSV" might not match the standard one; you might have to explicitly tell it that the field separators on each line are commas and not something else. (I would lean towards the last of those since it looks like the lines are being separated properly and the characters are being read properly.)

 

[Arman777]

It looks like you have generated a valid CSV file but have loaded it into EXCEL wrong. Did you tell EXCEL to use comma separation when the file is opened into EXCEL?

how can ı do that ?

 

[Ibix]

With the file open, if you select all of column A, go to the Data tab, select "Text to columns", then "delimited", then make sure the "comma" is ticked, then click OK.

 

[Arman777]

I am using windows.

 

[Arman777]

With the file open, if you select all of column A, go to the Data tab, select "Text to columns", then "delimited", then make sure the "comma" is ticked, then click OK.

I see the point. This works. Okay it works .. I just need to save it as xlsx file. I guess there is not direct way to output like that by coding.

 

[Ibix]

I see the point. This works. But it seems some data is missing ?

We can't read your mind. What do you mean "some data is missing"? Are the lines shorter than expected? Are some lines missing? Something else?

 

[Arman777]

It should be like this..but yeah maybe my excel settings are wrong

 

[Arman777]

We can't read your mind. What do you mean "some data is missing"? Are the lines shorter than expected? Are some lines missing? Something else?

Its not missing sorry my mistake. Excel gave an error but it is okay now.

 

[Ibix]

Actually, you do need to get Excel to load it properly, rather than do the post-hoc fix I proposed. I think Excel won't store more than a certain number of characters in a cell, so you will get data loss if your lines are too long.

Try changing your program to create data.csv instead of data.txt. Excel ought to recognise the extension.

 

[Arman777]

Actually, you do need to get Excel to load it properly, rather than do the post-hoc fix I proposed. I think Excel won't store more than a certain number of characters in a cell, so you will get data loss if your lines are too long.

Try changing your program to create data.csv instead of data.txt. Excel ought to recognise the extension.

I am already creating data.csv see post 3

 

[Ibix]

I am already creating data.csv see post 3

Sorry - I was looking at the version in post #1.

How are you opening it? I don't have a Windows box to hand to play with, but my recollection is that Excel does different things if you open the file with drag-and-drop, double click, and going through the File->Open menu. At least one of them ought to pop up the same dialog as the Text-to-columns menu option.

 

[Ibix]

Or try this: https://www.copytrans.net/support/how-to-open-a-csv-file-in-excel/.

 

[Arman777]

hmm that's the problem I guess. So I am using vs code and it looks like this

as you can see it says data.cvs

And when I look it from the folder view, I see

I am just clicking data.csv to open the file. Thats how I get

 

[Ibix]

Try the other ways, then.

Incidentally, your full name is in the title bar of your screenshots - I don't know if that bothers you.

 

[pasmith]

Why reinvent the wheel? There is a python library specifically for writing CSV files. Alternatively, if you turn your data a pandas DataFrame then those can also be written as CSV with a single function call, There is also a package which will allow you to create xlsx files directly from python, if that is actually what you want. Pandas can also create an a xlsx file directly with one function call.

 

[cgrie]

Why reinvent the wheel? There is a python library specifically for writing CSV files. Alternatively, if you turn your data a pandas DataFrame then those can also be written as CSV with a single function call,There is also a package which will allow you to create xlsx files directly from python, if that is actually what you want. Pandas can also create an a xlsx file directly with one function call.

I love pandas for that reason. You can write the code that arrange the data the way you like it once which saves lots of time on larger projects

Edit: there is also a numpy random function which creates an array of random numbers for you

 

[Arman777]

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()

Using ";" instead of "," works.

 

[cgrie]

yes, your code looks like it does the job and it seems like you know that the delimiter separates things left to right, and a new line starts a new row. It's a good practice to keep the delimiter as "," though

It might be worthwhile looking into pandas, because it does everything excel does and more.

 

[FactChecker]

There are many ways to skin a cat. VS and EXCEL can do this job. There is no reason to be changing programming languages and spreadsheet software, especially if one is working with others who may be much more familiar with EXCEL.

 

[cgrie]

There are many ways to skin a cat. VS and EXCEL can do this job. There is no reason to be changing programming languages and spreadsheet software, especially if one is working with others who may be much more familiar with EXCEL.

That's a good point, for this job VS and Excel is probably faster.
It looks like OP knows some python already, and later down the road, pandas would give more options.

Edit: Personally, I use both pandas and excel. Usually I first clean up a file in excel, then I move it to pandas to do more of the filtering and computation, then I put it back into a csv format

 

[lomidrevo]

Using ";" instead of "," works.

It's a good practice to keep the delimiter as "," though

The behavior of Excel depends on the regional settings in Windows. In some countries, comma "," is used as decimal separator instead of point "." character. In such case, comma "," cannot be used to separate values and Excel uses semicolon ";" to separate the values instead.

@Arman777, that would explain why the standard CSV is not automatically loaded into table format when you open it. Instead of overwriting the regional settings you can explicitly tell Excel what character should be used as separator as already mentioned in previous posts. Or you can keep using semicolon if it works well with your settings, but you should keep in mind that it might cause troubles to other users with different settings, in case you plan to distribute the file.

 

[sbrothy]

Why reinvent the wheel? There is a python library specifically for writing CSV files.

Also, for more more complex data you can use SQL over ODBC to access CSV files.

 

[rbelli1]

I am just clicking data.csv to open the file.

This tells excel to load the text file into the first column of the spreadsheet rather than import the text as a spreadsheet. This is a spectacularly bad and frustrating mode of operation. I would consider it a bug. I usually use LibreOffice when working with text type spreadsheets as it imports by deafult when you open them and it also handles them better in my opinion. Pasting into it also works better than Excel.

Follow the instructions Ibix posted in #16 and you will be good to go.

BoB

 

[FactChecker]

I always hesitate to recommend a complete change of tools/language used because I believe that is usually not the answer asked for or desired. I have not investigated a solution, but I believe that there are methods using OLE to get the job done. I did determine to my satisfaction that there are no command-line flags that could be set to tell EXCEL to separate the data at commas.

 

[PeterDonis]

I believe that there are methods using OLE to get the job done

Since the starting point is a Python program, OLE, if Python libraries for it even exist, would be at least as much of a change of tools as pandas. For one thing, if Excel is going to use OLE to grab the file data, it will expect to do it from an OLE server, which means the Python program would have to be a server, not just a script. That opens a whole new can of worms that is much better left alone. If pandas can simply write the data as an xlsx file, that would be a lot simpler.

 

[FactChecker]

Since the starting point is a Python program, OLE, if Python libraries for it even exist, would be at least as much of a change of tools as pandas. For one thing, if Excel is going to use OLE to grab the file data, it will expect to do it from an OLE server, which means the Python program would have to be a server, not just a script. That opens a whole new can of worms that is much better left alone. If pandas can simply write the data as an xlsx file, that would be a lot simpler.

That is a good point, and I think it is one possible trade-off that needs to be discussed when one recommends switching to a different language or toolset. For instance, if there were command-line switches that would make EXCEL behave correctly, it would be a relatively simple solution. I think EXCEL does not have those switches. There might be other options that would be simple to implement without switching away from EXCEL. I did all my work in Perl, which had convenient methods for writing directly to EXCEL spreadsheets. I would be surprised if Python did not have similar methods. This looks fairly convenient.

 

[PeterDonis]

I would be surprised if Python did not have similar methods.

Pandas, which is a Python library that has precisely such methods, has already been referred to several times in this thread.

 

[FactChecker]

Pandas, which is a Python library that has precisely such methods, has already been referred to several times in this thread.

Oh, sorry! I stand corrected. I did not realize that it was a Python library. I thought that an entirely different language was being recommended.

 

[harborsparrow]

Speaking from extensive experience in exporting data into .csv files, I would not use a library or try to automate Excel. Versions of Excel change, but a .csv file (always openable by Excel) is plain text. You'll want to be careful about encoding--use at least UTF-8 and make it explicit. You'll want to check each cell's worth of data for commas, and if you surround each cell with ", you'll want to watch for and handle that in each data item also. It's not that hard to do--takes a bit of tinkering--and if you treat it like a plain text file and handle it yourself, it will continue to work no matter what version of libraries or Excel comes along in a few years.

I could give you C# code that does this in one instance (try the link here--it's a list of algae taxa):

http://diatom.ansp.org/taxaservice/ExcelExportNawqaTaxaWithFlags.ashx?taxonomy_id=21&diatom=0

The above file comes from a REST web service (ASP.NET) written in C#; I just looked at the code. It pulls data from a database, converts everything to text and stuffs it into a "StringBuilder" object (I imagine Python may have something similar), thus assembling the entire file in memory, then converts it to a String and writes the text to the output.

Once someone downloads the file, they can open it in Excel (whatever version) and save it as any version of Excel they want. They will not be able to edit the .csv file in Excel, but it should always open. You can look at its contents in detail using any text editor.

 

[FactChecker]

You'll want to check each cell's worth of data for commas, and if you surround each cell with ", you'll want to watch for and handle that in each data item also. It's not that hard to do--takes a bit of tinkering--and if you treat it like a plain text file and handle it yourself, it will continue to work no matter what version of libraries or Excel comes along in a few years.

If the cell might contain general text that may include commas and quotes, it gets messy.

 

[harborsparrow]

As noted, for comma-delimited quoted data cells, you'll have to do something about , and " in the actual data. There should be a way to "escape" those characters in Excel (as it is a database first and foremost). Once you know the escape sequence, it's a simple string substitution before writing data into a cell.

"cell data", "cell2 data", "cell3 data", "data with , is fine", "data with a quote "" requires that the quote be doubled", "another data item"

It's not that bad--just check each data item and escape any double quotes as "" first, THEN add it to the current line of text. Delimit as always. Each line starts and ends with " and "," goes between each data item.