[NEWBIE] csv to excel format problem

[Anders Eriksson]

Hello Marco and welcome to the wonderful world of Python,

Your problem is that the file is a text file so the values you are reading
are text which you then write to the Excel sheet.

So you need to convert the text value to a float value. Now the Big Cahonas
has already been there so it's included: 

float( [x]) 

Convert a string or a number to floating point. If the argument is a
string, it must contain a possibly signed decimal or floating point number,
possibly embedded in whitespace. Otherwise, the argument may be a plain or
long integer or a floating point number, and a floating point number with
the same value (within Python's floating point precision) is returned. If
no argument is given, returns 0.0. 
Note: When passing in a string, values for NaN and Infinity may be
returned, depending on the underlying C library. The specific set of
strings accepted which cause these values to be returned depends entirely
on the C library and is known to vary

If you do this change it might work ;-)
Add these functions:
def isNaN(x):
    return isinstance(x, float) and x!=x
def isInf(x):
    return !isNaN(x) && isNaN( (x) - (x) )

in writeExcelRow change:
   for column in columns:
	fcolumn = float(column)
	if(isNaN(fcolumn) or isInf(fcolumn)):
		# do some error handling
	else:
		worksheet.write(lno, cno, column,style)
		cno = cno + 1

I'm sure that there are smarter ways of doing this and someone will
probably point them out ;-)

// Anders
-- 
English is not my first, or second, language
so anything strange, or insulting, is due to
the translation.
Please correct me so I may improve my English!

On Tue, 14 Oct 2008 03:03:52 -0700 (PDT), MM wrote:

> Hi to all,
> 
> I'm trying to import a tab separated values file onto Excel with the
> following script:
> 
> import csv
> from pyExcelerator import *
> 
> w = Workbook()
> worksheet = w.add_sheet('sim1')
> 
> def writeExcelRow(worksheet, lno, columns):
>   style = XFStyle()
>   style.num_format_str = '0.00E+00'
>   cno = 0
>   for column in columns:
>     worksheet.write(lno, cno, column,style)
>     cno = cno + 1
> 
> nrow = 0
> csvfile = file('res1.txt','r')
> csvreader = csv.reader(csvfile, delimiter='\t')
> 
> for line in csvreader:
>     writeExcelRow(worksheet,nrow,line)
>     nrow += 1
> 
> csvfile.close()
> w.save('numbers.xls')
> 
> All goes well and the resulting file "numbers.xls" has all the numbers
> in the right place....
> 
> The problem is that excel sees the numbers as text and gives the error
> "numbers stored as text" that I have to correct manually.
> 
> The file res1.txt has the structure of a tab separated values of
> floating point numbers.
> 
> Thank you for the help.
> 
> Marco