0

Hi and thanks for reading.

I have a problem with a for loop in python. I'm trying to read my .txt file line by line and importing each line in excel using worksheet.write_row. I have tried many other ways to get this done but being pretty new to all of this, this one has been the easiest for me to understand.

It worked selecting just one line from the file so I'm pretty sure that something is wrong with the "for" loop I have written.

I have tried out all different kinds of editing the loop I can think of but nothing worked. I also researched the web but couldn't find any solutions which I could identity. So any help would be greatly appreciated.

import xlsxwriter

workbook = xlsxwriter.Workbook('pythonlinetest.xlsx')        #create file
worksheet = workbook.add_worksheet()                         #create worksheet
data = open('160919-001 14cts c133_vi.txt','r')                #loaddata

#count lines
linelist = data.readlines()
count = len(linelist)
print count          #check lines

#make each line and print in excel
for n in range (0, len(linelist))
    line = linelist[n]
    splitline = line.split("\t")
    worksheet.write_row(row, 0, splitline)
    row += 1

>>>> Error: File "<ipython-input-4-abc8f489522d>", line 2
    for n in range (0, len(linelist))
                                     ^
SyntaxError: invalid syntax


#close workbook
workbook.close()

Thanks a lot!

Norman R
  • 11
  • 1
  • 1
  • 5
    You're missing a `:` at the end of the line. – Baris Demiray Dec 19 '16 at 16:36
  • Oh thanks! I fixed that, seems that the for loop works now. Still I'm getting an error when I try to workbook.close(): – Norman R Dec 19 '16 at 16:50
  • I've been looking threw the error message I'm getting (it's too long to post here). Basically it's this: UnicodeDecodeError: 'ascii' codec can't decode byte 0xba in position 33: ordinal not in range(128) – Norman R Dec 19 '16 at 16:59
  • Ok I'm quite frustrated with this Decode Error, I have been looking threw my data and the only thing I see which might be a problem is the unit ºC. Could this be the problem? how can I decode my data from ascii to something else to avoid the decode problem? Thanks a lot – Norman R Dec 19 '16 at 17:28
  • Yes, that's the problem, see decimal 186 at http://lwp.interglacial.com/appf_01.htm, it's beyond standard ASCII. I guess you need to specify the encoding since that library is trying to decode it as ASCII (and apparently doing it in a buffered way so you have the problem at `close()`) and failing. See http://stackoverflow.com/questions/21129020/how-to-fix-unicodedecodeerror-ascii-codec-cant-decode-byte. Does doing `worksheet.write_row(row, 0, splitline.decode('latin1'))` help? – Baris Demiray Dec 20 '16 at 10:39
  • @BarisDemiray thanks a lot you confirmed my suspicion, I had also found the question you posted with the great explanation of Alaistar. – Norman R Dec 20 '16 at 15:28
  • @BarisDemiray As of your tipp: decoding in the worksheet.write command didn't work, but I decoded the string before and it worked like a charm! Thanks a lot for that. Now I just have one more little problem which you might have an easy fix for: How do I make excel recognize the numbers I imported as numbers? It seems like excel has everything written as text. If I double click a cell and press enter excel accepts it as a number before not. Thanks! – Norman R Dec 20 '16 at 15:35
  • It's time to read the docs :), see http://xlsxwriter.readthedocs.io/tutorial03.html. – Baris Demiray Dec 20 '16 at 15:52
  • @BarisDemiray Thanks, will do! – Norman R Dec 21 '16 at 11:29

2 Answers2

1

Writing up the discussion in comments,

  1. You are missing a : at the end of that line mentioned in the error message.
  2. You need to override the decoding behaviour if your input is not ascii.
  3. See http://xlsxwriter.readthedocs.io/tutorial03.html for overriding input format behaviour of xlsxwriter.
Baris Demiray
  • 1,539
  • 24
  • 35
  • I think this is probably a better item 3 example showing how to deal with encodings: http://xlsxwriter.readthedocs.io/example_unicode_shift_jis.html – jmcnamara Dec 21 '16 at 12:40
0

So thanks to the help of Baris Demiray I managed to make this line by line import work. I had a decode error caused by a "º" symbol. This is fixed now. I will share my code, if anyone is interested.

The only problem is, if your .txt file contains numbers, excel will not recognize these at first. If anybody has an idea how to edit this code so that excel will recognize the strings with numbers in please feel free to edit.

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx')        #create file
worksheet = workbook.add_worksheet()           #create worksheet
data = open('your.txt','r')                #loaddata

linelist = data.readlines()              #read each line
count = len(linelist)                 #count lines
print count                       #check number of lines

for num in range (0, count):         #create each line and print in excel
    line = linelist[num]            #load each line in variable
    line = line.decode('latin1')         #decode problem solution
    splitline = line.split("\t")          #split lines
    worksheet.write_row(num, 0, splitline)         #write each line in excel

workbook.close()            #close workbook

So that's it, maybe it helps someone maybe not. Keep in mind I'm new to this and haven't programmed anything for years, so I'm sure there are better ways to do this.

Norman R
  • 11
  • 1
  • 1