[NEWBIE] csv to excel format problem

[MM]

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

 

[MM]

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

I've found the answer by myself...
Maybe for you it would be simple!


for line in csvreader:
writeExcelRow(worksheet,nrow,map(float,line))
nrow += 1


Thank you anyway

 

[John Machin]

Consider using xlwt instead ... see http://pypi.python.org/pypi/xlwt/

xlwt is an actively-maintained (as recently as yesterday) fork of
pyExcelerator ... bugs fixed, functionality enhancements, speed-ups.

Also consider reading/joining the python-excel newsgroup/list at
http://groups.google.com.au/group/python-excel

It is generally a good idea NOT to do things once per iteration when
you can do it only once; you save CPU time and maybe memory. In the
case of XFs in Excel spreadsheets, it's a VERY good idea ... there's a
maximum of about 4000 XFs in Excel (up to Excel 2003, at least).
pyExcelerator's method of avoiding creating unwanted XFs depends
partly on address comparison instead of value comparison, and can thus
go pear-shaped in scenarios more complicated (and thus harder to
debug) than yours.

Another good habit to acquire: always use 'rb' to ensure that the file
is opened in binary mode.

I've found the answer by myself...
Maybe for you it would be simple!

for line in csvreader:
    writeExcelRow(worksheet,nrow,map(float,line))
    nrow += 1

.... or a more general solution if you have a mixture of numbers,
dates, text

Cheers,
John

 

[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