Python Forum
Importing text file into excel spreadsheet with formatting
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Importing text file into excel spreadsheet with formatting
#1
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()
Larz60+ write Apr-03-2021, 10:11 PM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.

Fixed for you this time, please use bbcode tags on future posts.
Reply
#2
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".
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Python openyxl not updating Excel file MrBean12 1 250 Mar-03-2024, 12:16 AM
Last Post: MrBean12
  Copy Paste excel files based on the first letters of the file name Viento 2 348 Feb-07-2024, 12:24 PM
Last Post: Viento
  Search Excel File with a list of values huzzug 4 1,147 Nov-03-2023, 05:35 PM
Last Post: huzzug
  Updating sharepoint excel file odd results cubangt 1 754 Nov-03-2023, 05:13 PM
Last Post: noisefloor
  Returning Column and Row Data From Spreadsheet knight2000 0 413 Oct-22-2023, 07:07 AM
Last Post: knight2000
  Editing spreadsheet/csv BSDevo 6 969 Sep-01-2023, 05:47 PM
Last Post: BSDevo
  Formatting a date time string read from a csv file DosAtPython 5 1,161 Jun-19-2023, 02:12 PM
Last Post: DosAtPython
  Reading data from excel file –> process it >>then write to another excel output file Jennifer_Jone 0 1,046 Mar-14-2023, 07:59 PM
Last Post: Jennifer_Jone
  Save and Close Excel File avd88 0 2,840 Feb-20-2023, 07:19 PM
Last Post: avd88
  Trying to access excel file on our sharepoint server but getting errors cubangt 0 773 Feb-16-2023, 08:11 PM
Last Post: cubangt

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020