CODEWITHSUNDEEP
pythonexcelxlsxwriter
Norman R
Norman R

Reputation: 11

Python: Import from txt into excel line by line using xlsxwriter

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!

Upvotes: 0

Views: 3206

Answers (3)

user3229980
user3229980

Reputation: 11

To get the Excel file to recognize numbers use:

workbook = xlsxwriter.Workbook('test.xlsx'', {'strings_to_numbers': True})

Upvotes: 1

Norman R
Norman R

Reputation: 11

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.

Upvotes: 0

Baris Demiray
Baris Demiray

Reputation: 1607

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.

Upvotes: 1

Related Questions