MarcelPhy MarcelPhy - 3 months ago 17
Python Question

Python Extract Text between two strings into Excel

I have a text file like this

blablablabla
blablablabla
Start
Hello
World
End
blablabla


I want to extract the strings between Start and End and write them into an Excel cell. My code thus far looks like this:

import xlsxwriter
workbook = xlsxwriter.Workbook("Test1.xlsx")
worksheet = workbook.add_worksheet()

flist = open("TextTest.txt").readlines()

parsing = False
for line in flist:

if line.startswith("End"):
parsing = False
if parsing:
worksheet.write(1,1,line)
if line.startswith("Start"):
parsing = True

workbook.close()


However it returns only an empty workbook. What am I doing wrong?

Answer

I don't have much of a experience with excel stuff in python but you can try openpyxl, I found it much easier to understand.

Solution to your problem:

import openpyxl
wb = openpyxl.Workbook()
destination_filename = "my.xlsx"
ws = wb.active
ws.title = "sheet1"
flist = open("text.txt").readlines()
row = 1
collum = 'A'
parsing = False

for i in flist:      

    if i.startswith("End"):
        parsing = False
    if parsing:
        coord = collum + str(row)
        ws[coord] = i
        row += 1        
    if i.startswith("Start"):
        parsing = True

wb.save(filename = destination_filename)

Edit(Writing all lines in one cell):

You have to create new variable to which you can add your lines, and at the end you will assign the string variable to cell in worksheet.

String=""
for i in flist:

    if i.startswith("End"):
        parsing = False    
    if parsing:
        i = i.strip("\n")
        String += str(i) + ","
    if i.startswith("Start"):
        parsing = True

ws['A1'] = String
wb.save(filename = destination_filename)
Comments