Python Forum

Hi, I have many text files with similar format as below.

Output:
       Apples:       1596.403 +-    10.651
       Oranges:         10.164 +-     0.685
       Bananas:          0.879 +-     0.200
         Pears:          0.006 +-     0.047
 Pears/Bananas:         -0.021 +-     0.101

I would like to import them into one excel spreadsheet with Apples, Oranges and Bananas as the column headers and the corresponding value from each file in the respective column like so:

Output:
Apples       oranges     Bananas
1596.403      10.164      0.879
1467.234      12.345      0.461
1234.456      14.658      0.592
    ....So on and so on.....

Here is my code:It creates the spreadsheet "summary" with no contents.All suggestions would be greatly appreciated.Thank you.

import pandas as pd
import numpy as np
import glob

txt_list = [] # empty list
sheet_list = [] # empty list

# a for loop through filenames matching a specified pattern (.txt) in the current directory
for infile in glob.glob("test-1.txt"): 
    outfile = infile.replace('.txt', '') #removing '.txt' for excel sheet names
    sheet_list.append(outfile) #appending for excel sheet name to sheet_list
    txt_list.append(infile) #appending for '...txt' to txtt_list

writer = pd.ExcelWriter('summary.xlsx', engine='xlsxwriter')

# a for loop through all elements in txt_list
for i in range(0, len(txt_list)):
    df = pd.read_csv('%s' % (txt_list[i])) #reading element from txt_list at index = i 
    df.to_excel(writer, sheet_name='%s' % (sheet_list[i]), index=False) #reading element from sheet_list at index = i 

writer.save()

Below is the output.

runfile('C:/Users/137678/.spyder-py3/TNC/textfiles_to_excel_try.py', wdir='C:/Users/137678/.spyder-py3/TNC')

It creates the empty spreadsheet "Summary.xlsx".