[Tutor] How to extract data from text to excel?

[Joel Goldstick]

On Thu, Mar 3, 2011 at 9:10 AM, Steven D'Aprano <steve at pearwood.info> wrote:

> tee chwee liong wrote:
>
>> hi,
>>  i found a module called xlwt (http://www.python-excel.org/) that can
>> write to Excel. i want the code to read from a file (robert.txt) and then
>> write to excel in a column. for eg:
>> cell col0, row0 = 0
>> cell col0, row1 = 0
>> cell col0, row2 = 0
>> cell col0, row3 = 1
>> cell col0, row4 = 0
>> cell col0, row5 = 1
>>
>> however, it seems that the output from this code is that it only writes to
>> one cell in the excel.
>>
>
> That's because your data ("robert.txt") only contains one line with one
> field. You read that one field as a big chunk of text, and then write it to
> one cell, and then the loop ends.
>
>
>  import xlwt
>> # Create workbook and worksheet wbk = xlwt.Workbook() sheet =
>> wbk.add_sheet('python')
>> row = 0  # row counter
>> f = open('robert.txt')
>> for line in f:     # separate fields by commas
>>
>
> What does this comment mean? You don't have any commas in the file
> "robert.txt", and you don't actually do anything with or to commas in your
> code. I think that comment is false.
>
>
>     L = line.strip()        sheet.write(row,0,L)
>>
>
> Here you write the entire contents of the file into one cell. You need to
> iterate over L:
>
>    for c in L:
>        sheet.write(row, 0, c)
>        row += 1
>
>
>
> --
> Steven
>
> _______________________________________________
> Tutor maillist  -  Tutor at python.org
> To unsubscribe or change subscription options:
> http://mail.python.org/mailman/listinfo/tutor
>

I might not understand what you need to do, but it seems to me that  its
simpler to create a csv file that can be read by excel.  Actually, since you
have only one field per row, there are no commas.

Here is what I came up with:

#!/usr/bin/env python

"""
Read a file containing a long string of ones and zeros
Separate each bit with a comma, then write the whole csv thing
to an output file.
This can be read by an excel like application with one bit in each
row of the first column
"""

f = open('./robert.txt','r')
f_data = f.read()

list_of_bits = list(f_data)
csv_string = '\n'.join(list_of_bits[:-1])

# these 3 lines show what is going on
print f_data
print list_of_bits
print csv_string

f_out = open('./robert.csv', 'w')
f_out.write(csv_string)
f_out.close()


-- 
Joel Goldstick
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/tutor/attachments/20110303/df6038ac/attachment.html>