Converting .txt file to .xls in Python

Question

I have a file that is really in csv format but is being handed to me as a .txt. I need to take this file and transfer it to .xls format so I can upload it into Google Docs. Currently I have the code below but it is spitting out the file with everything on the line into the first cell instead of breaking out different cells for every comma value. Does anyone know how to solve this?

Thanks!

import os

fin = open(r"C:\Users\JohnDoe\Downloads\Report.txt", "r")
data_list = fin.readlines()
fin.close() # closes file

fout = open(r"C:\Users\JohnDoe\Desktop\Report.xls", "w")
fout.writelines(data_list)
fout.flush()
fout.close()

It's not clear from your question why you're converting it to xls as Google Docs can certainly handle a .csv. If my assumption that you don't actually need a .xls is wrong, let me know. — agf
– agf, Commented Nov 10, 2011 at 3:36
Renaming the file to YourOriginalFileName.csv and then opening it in Excel and save it as xls file. Will this not achieve what you want? Or are you looking for ways to automate it? — yasouser
– yasouser, Commented Nov 10, 2011 at 3:44
@yasouser Who says he has a copy of Excel? What if it needs to be automated? — agf
– agf, Commented Nov 10, 2011 at 3:52

agf · Accepted Answer · 2011-11-10 03:38:55Z

6

Just renaming it to .xls doesn't convert it to an Excel spreadsheet. But regardless, why would you convert it to .xls just to upload it to Google Docs?

If it's a .csv, just rename the file to .csv and upload it and it'll properly be detected as a spreadsheet.

Use os.rename:

import os

os.rename("C:\Users\JohnDoe\Downloads\Report.txt", 
          "C:\Users\JohnDoe\Downloads\Report.cvs")

if you really need to do it with Python.

If you need to get actual rows from the file instead of lines:

from csv import reader

for row in reader(open(r"C:\Users\JohnDoe\Downloads\Report.txt", 'rb')):
    # do something with row

will give you a list of cells for each line.

If you actually do need to work with an Excel spreadsheet, see http://www.python-excel.org/ and the xlwt package.

edited Nov 10, 2011 at 3:38

answered Nov 10, 2011 at 3:28

agf

178k45 gold badges300 silver badges241 bronze badges

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

Comments

teambob · Accepted Answer · 2011-11-10 03:31:42Z

0

Your code is simply copying Report.txt into Report.xls

When opening Report.xls Excel is recognising it as a csv file, however it is not recognising the commas as delimiters.

You will need to use some kind of library to write proper Excel files. Look at Word's COM interface for example.

answered Nov 10, 2011 at 3:31

teambob

2,09215 silver badges19 bronze badges

2 Comments

agf Over a year ago

"recognising it as a csv file, however it is not recognising the commas as delimiters." doesn't make any sense. Either it knows it's a .csv file, in which case it knows the commas are delimiters, or it doesn't know it's a .csv file.

teambob Over a year ago

Excel could recognise it as a delimiter separated values but be expecting a tab delimiter or other delimiter. At least Openoffice remembers the delimiter that was selected last time or autodetects the delimieter. I do not use Excel itself but I assume it does something similar.

Alvin K. · Accepted Answer · 2011-11-10 06:51:26Z

0

Try http://packages.python.org/openpyxl/ or for older excel files, check out this help page using pyExcelerator.

answered Nov 10, 2011 at 6:51

Alvin K.

4,37923 silver badges25 bronze badges

1 Comment

John Machin Over a year ago

pyExcelerator is extinct. xlwt is a fork of pyExcelerator with bug fixes and enhancements. See the answer by @agf

Collectives™ on Stack Overflow

Converting .txt file to .xls in Python

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related