[Tutor] Saving Files for Spreadsheet Exporting

[Jeff Shannon]

"BELSEY, Dylan" wrote:

> Hi Alan,
>         I'm quite sure that you have considered this but the simplistic way
> is to loop through the list and append a tab "\t" or a ";" etc to the end of
> each string and write to a file.

Ashish Shrestha wrote:

> well something that works for smaller lists. may be not efficient for
> big lists!
>
>   >>> myList = [['a','b','c'],['d','e','f'],['g','h','i']]
>   >>> '\n'.join([','.join(sublist) for sublist in myList])
> 'a,b,c\nd,e,f\ng,h,i'

In fact, join() is probably the most efficient way possible to accomplish this
task.  Looping through the list and appending characters individually is *very*
slow -- you're creating a temporary string object for *each* append, and
throwing it away almost immediately.  All that allocation takes a lot of time.
In contrast, join() creates exactly one string object, no matter what the size
of the list.  Also, all the loop control is handled internally, which means at C
speed instead of Python speed.  Even if you were to pass your list of lists to a
custom C extension to write your file, it would be unlikely to be significantly
faster than using join() and file.write(), because those string methods have
been heavily optimized by some very talented programmers.  (I sure wouldn't bet
anything on *my* being able to write faster code than Tim Peters... ;) )

So, my choice for writing this file would be:

outfile = open('output.tsv', 'w')
output = '\n'.join( ['\t'.join(sublist) for sublist in MyList] )
outfile.write(output)
outfile.close()

Note that I use tabs ('\t') as column-separators, rather than commas.  While
comma-separated values are something of a standard, it leads to all sorts of
quoting headaches when commas may be a valid part of a string -- especially if
quote characters are also potentially valid.  Tabs are a lot less likely to be
valid data than commas are.  Whatever the separator of choice is, though, Excel
(or whatever other spreadsheet) should be able to import this file quite simply.

If you want to do anything fancier, or have a particular need to create XLS
files without human intervention, then look into controlling Excel through the
PythonCOM framework.

Jeff Shannon
Technician/Programmer
Credit International