Member Avatar for iamthwee

As the title suggests how?


I have successfully used pyExcelerator to convert an xls file to csv,


1) I downloaded the pyExcelerator file.
2) Extracted it and put the file under C:\Python25\Lib\site-packages
3) Then I ran the example script xls2csv.py example.xls > comma.csv

but I would like to know how to do the opposite, csv -> xls

Preferably in a portably manner that doesn't rely on windows.

I tried going here:
http://sourceforge.net/projects/py-csv2xls

and i used ultimatezip to unzip. but I can't get it to work. when i run the script it says:

C:\Documents and Settings\blah>csv2xls.py
Python module afm cannot be imported

Or is there an easier option I am missing?

Member Avatar for iamthwee

I found an answer in pyXlWriter.

There is a script that converts called tab2xls.py that i can use.

Off-topic, how can I join the F-ugly code club? Whenever someone touts a program written in "pure python" I wonder if that means that my programs are in impure python. Maybe a support group would help: "Hello, my name is Joe and I write ugly code"

Member Avatar for iamthwee

Ok everything is working well, now I just need to know how to change the python script into an executable and I'll be done.

Member Avatar for iamthwee

OK if I want to distribute this code will I have any legislation issues?

OK if I want to distribute this code will I have any legislation issues?

Send an envelope with 100 unmarked $100 bills to the political party in power and you wont have any legislation issues.

Thanks for the nice info anyway!

Member Avatar for iamthwee

Hmm, ok there seems to be too many problems with this.

1) The dates come out as floats, and you need to use something to convert it.


I have decided to download the latest version of xlrd and am experimenting with a script in the folder "c:python25\scripts"

http://pypi.python.org/pypi/xlrd

runxlrd.py show C:\test.xls

Member Avatar for iamthwee

OK, so far I've managed to use xlrd to parse the excel file and then convert it to a
tab delimited file.

I have attached a zip file, which is a free xls to tab delimited text file convertor for windows.


But I'm still having issues with saving it as an excel file.

Although it saves to an excel file it doesn't save it in the correct format, like dates etc.

Member Avatar for iamthwee

OK my xls to tab convertor is working now!

1.0							
	2.0			0			
		3.0					
	1		4.0				
				5.0			
					6.0		
						7.0	
							
							
							
	20-60-20083	02/05/2008

All I need to do is create a tab to xls convertor and I'll be done.

Underneath is code with writes to an excel file using the module pyXLWriter.
How do I change it so it reads in the tab file as above and change the values?

import pyXLWriter as xl
import datetime 

# Create a new workbook called simple.xls and add a worksheet
workbook  = xl.Writer("simple.xls")
worksheet = workbook.add_worksheet()

# The general syntax is write(row, column, token). Note that row and
# column are zero indexed

# Write some text
#worksheet.write([0, 0],  "Hi Excel!")

# Write some numbers
worksheet.write([2, 0],  3)          # Writes 3
worksheet.write([3, 6],  3.00000)    # Writes 3
worksheet.write([4, 0],  datetime.date(2004,04,15)) #write date 15/04/2004
worksheet.write([5, 0],  "hi")    # Write string

# Write some formulas
#worksheet.write([7, 0],  '=A3 + A6')
#worksheet.write([8, 0],  '=IF(A5>3,"Yes", "No")')

# Write a hyperlink
#worksheet.write([10, 0], 'http://www.perl.com/')

workbook.close()

I did try using

#!/usr/bin/env python
# This example script was ported from Perl Spreadsheet::WriteExcel module.
# The author of the Spreadsheet::WriteExcel module is John McNamara
# <jmcnamara@cpan.org>

__revision__ = """$Id: tab2xls.py,v 1.1 2004/01/31 18:57:53 fufff Exp $"""

######################################################################
#
# Example of how to use the WriteExcel module
#
# The following converts a tab separated file into an Excel file
#
# Usage: tab2xls.pl tabfile.txt newfile.xls
#
# reverse('(c)'), March 2001, John McNamara, jmcnamara@cpan.org
#

import sys
import pyXLWriter as xl

# Check for valid number of arguments
if len(sys.argv) != 3:
    print "Usage: tab2xls tabfile.txt newfile.xls"
    sys.exit(1)
else:
    tabfilename, xlsfilename = sys.argv[1:3]

# Open the tab delimited file
tabfile = file(tabfilename, "r")

# Create a new Excel workbook
workbook = xl.Writer(xlsfilename)
worksheet = workbook.add_worksheet()

# Row and column are zero indexed
nrow = 0

for line in tabfile.xreadlines():
    # Split on single tab
    row = line.split('\t')
    for ncol, cell in enumerate(row):
        worksheet.write([nrow, ncol], cell.strip())
    nrow += 1
tabfile.close()
workbook.close()

And that does write to excel but it doesn't recognise dates as being dates and numbers being numbers so it is more or less useless.

sorry to revive an old thread here, but this is the closest thing to what I want.

Instead of simply writing a whole csv --> xls, what I'd like to do is different because it will:

a) open multiple files
b) only write a set # of lines (I want ~15 lines written to a excel file
c) I want each file to become a row in the excel doc, and the next file to become the following row. Can I get some help on doing this?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.