csv to excel format problem

[John Machin]

On Oct 15, 3:49 am, MM <marco.me... at gmail.com> wrote:
> On 14 Ott, 12:03, MM <marco.me... at gmail.com> wrote:

> > I'm trying to import a tab separated values file onto Excel with the
> > following script:
>
> > import csv
> > from pyExcelerator import *

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

>
> > w = Workbook()
> > worksheet = w.add_sheet('sim1')
>
> > def writeExcelRow(worksheet, lno, columns):
> >   style = XFStyle()
> >   style.num_format_str = '0.00E+00'

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.

> >   cno = 0
> >   for column in columns:
> >     worksheet.write(lno, cno, column,style)
> >     cno = cno + 1
>
> > nrow = 0
> > csvfile = file('res1.txt','r')

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

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

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

Cheers,
John