I have a txt file that looks like this:

 category test_1

      aaa.com; test info - tw

      bbb.com; test info - al

 category test_2

      ccc.com; test info - al

      ddd.com; test info - tw

      eee.com; test info - tw

 category test_3

      fff.com; test info - tw

      ggg.com; test info - al

      hhh.com; test info - tw

      iii.com; test info - al

I need help creating a Python script that pulls a portion of the txt file and exports it to an excel file. For example, if I want to export the entries in category 'test_1', the script would produce the following output in an excel file.

      |    A.   |       B.       |   C.  |
   ---------------------------------------
   1. | aaa.com | test info - tw |       |
   ---------------------------------------
   2. | bbb.com | test info - al |       |
   ---------------------------------------
   3. |         |                |       |

I have tried to use the code below

My txt file is saved on my desktop as autotest.txt

 import pandas as pd

 df = pd.read_csv(‘C:\Users\A12345\Desktop\autotest.txt’)

 df.to_excel(‘output.xlsx’, ‘Sheet1’)

When I run this code, it doesn’t create an excel file. I’ve also tried to add an excel file named ‘output.xlsx’ on my desktop and when I ran the script it didn’t add the text to the excel file either.

CodePudding user response：

Its possible to convert that unique format to csv with 'category' as a keyword

file=open("text_file.txt",'r')
data=file.read()
categories=data.split('category')#One approach, if a 'category' string is present
dict_format={}
for categor_data in categories:
    items=categor_data.split('\n') #split to lines
    dict_format[items[0].replace(" ", "")]=items[1:]#removes spaces from name of categories
    
for name in dict_format:
    print(name)

print("which category to export to.csv format?")
answer=input()

with open(answer ".csv",'w') as csv:
    for row in dict_format[answer][:-1]:
        if row != "": #if not empty.
            csv.write(row.replace(";",",") "\n")
            
    csv.write(dict_format[answer][-1].replace(";",","))
    csv.close()

#Now you should be able convert that csv file to xlsx using pandas
    

The Console Window:

>>>run.py
test_1
test_2
test_3
which category to export to.csv format?
test_1
>>> 

The test_1 .csv file looks like in text format:

      aaa.com, test info - tw
      bbb.com, test info - al
 

CodePudding user response：

I used the module XlsxWriter; you can install it with pip3 install XlsxWriter. The code i wrote works as expected:

import xlsxwriter 

# this is used to filter. The code expect for the category num, such as 1, 2 or 3
num = input('Give me category number: ')
# you can do checks here if input should be something different
num = int(num)

start_portion_line = 'category test_{}'.format(num) 
end_portion_line = 'category test_{}'.format(num   1) 

start_index = 0
end_index = 0
with open('path/to/your/txt/file', 'r') as f:
    lines = f.readlines()
    # find indexes that define the wanted portion
    for i,line in zip(range(len(lines)), lines):
        if line.strip() == start_portion_line:
            start_index = i
        elif line.strip() == end_portion_line:
            end_index = i - 1
if end_index == 0:
    end_index = len(lines)

# getting only the wanted lines
lines = lines[start_index:end_index]
# removing blank lines
while '\n' in lines:
    lines.remove('\n')

workbook = xlsxwriter.Workbook('output.xlsx')
worksheet = workbook.add_worksheet()
for i,line in zip(range(len(lines)), lines):
    # removing initial spaces
    line = line.strip()
    # separating tokens
    columns = line.split(';')
    # writing
    for col,j in zip(columns, range(len(columns))):
        worksheet.write(i, j, col)

workbook.close()