How to Write Python Output into an Excel CSV File?

In summary, the code generates a list of random numbers and prints them out to a text file. The code then uses a dictionary to match the numbers with the letters of a survey.
  • #1
Arman777
Insights Author
Gold Member
2,168
192
I have a code like

Python:
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

Code:
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 ?
 
Technology news on Phys.org
  • #2
Arman777 said:
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.)
 
  • #3
PeterDonis said:
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
Code:
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

1612646492486.png
 
  • #4
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?
 
  • #5
Arman777 said:
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.)
 
  • #6
FactChecker said:
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 ?
 
  • #7
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.
 
  • #8
I am using windows.
 
  • #9
Ibix said:
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.
 
Last edited:
  • #10
Arman777 said:
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?
 
  • Like
Likes Dale and Vanadium 50
  • #11
1612648529443.png


It should be like this..but yeah maybe my excel settings are wrong
 
  • #12
Ibix said:
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.
 
  • #13
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.
 
  • #14
Ibix said:
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
 
  • #15
Arman777 said:
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.
 
  • #17
hmm that's the problem I guess. So I am using vs code and it looks like this

1612649497304.png


as you can see it says data.cvs

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

1612649562070.png


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

1612649600569.png
 
  • #18
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.
 
  • #19
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.
 
  • Like
Likes Locrian, Nugatory, lomidrevo and 1 other person
  • #20
pasmith said:
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
 
  • #21
Python:
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.
 
  • #22
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.
 
  • #23
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.
 
  • #24
FactChecker said:
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
 
Last edited:
  • Like
Likes FactChecker
  • #25
Arman777 said:
Using ";" instead of "," works.
cgrie said:
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.
 
  • Like
Likes cgrie, FactChecker, Arman777 and 1 other person
  • #26
pasmith said:
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.
 
  • #27
Arman777 said:
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
 
  • Like
Likes PeterDonis
  • #28
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.
 
  • #29
FactChecker said:
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.
 
  • Like
Likes FactChecker
  • #30
PeterDonis said:
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.
 
  • #31
FactChecker said:
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.
 
  • Like
Likes FactChecker
  • #32
PeterDonis said:
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.
 
  • #33
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.
 
Last edited:
  • #34
harborsparrow said:
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.
 
  • #35
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.
 
Last edited:
  • Like
Likes FactChecker

What is a CSV file?

A CSV (Comma Separated Values) file is a plain text file that stores data in a tabular format, with each row representing a record and each column representing a field. It is commonly used for storing and exchanging data between different software applications.

How do I write data into a CSV file?

To write data into a CSV file, you can use a programming language like Python or a spreadsheet program like Microsoft Excel. In Python, you can use the built-in csv module to create and write data into a CSV file. In Excel, you can simply save your data as a CSV file by choosing the "CSV (Comma delimited) (*.csv)" option when saving.

What are the benefits of using a CSV file?

CSV files are lightweight, easy to read and write, and can be opened by a variety of software programs. They are also compatible with most operating systems and can be easily shared and transferred between different devices and platforms.

Can I edit a CSV file after it has been created?

Yes, you can edit a CSV file after it has been created. In Python, you can use the csv module to read and modify existing CSV files. In Excel, you can open the CSV file and make changes to the data as you would with any other spreadsheet.

Is there a limit to the amount of data that can be stored in a CSV file?

The size of a CSV file is limited by the storage capacity of your device. However, most software programs and operating systems have a maximum file size limit, which can vary depending on the platform. It is always a good idea to check the file size limit before creating a large CSV file.

Similar threads

  • Programming and Computer Science
Replies
9
Views
1K
  • Programming and Computer Science
Replies
23
Views
2K
  • Programming and Computer Science
Replies
4
Views
854
  • Programming and Computer Science
Replies
1
Views
526
  • Programming and Computer Science
Replies
17
Views
2K
  • Programming and Computer Science
Replies
2
Views
2K
  • Nuclear Engineering
Replies
5
Views
1K
Replies
6
Views
2K
  • Programming and Computer Science
Replies
1
Views
2K
Replies
6
Views
1K
Back
Top