CODEWITHSUNDEEP
pythonexcelloopstext-filesopenpyxl
A.R
A.R

Reputation: 23

How to read multiple text files line by line and send to excel moving to a new column after every file?

Im building a script to run duplicate tests each with a log file that will be read and compiled into a spreadsheet.

The number of files and lines in each will vary depending on time and desired itterations. I have a basic script to read one file line by line and paste the data into a seperate excel document in successive rows. 

from openpyxl import load_workbook
from Test_Parameters import Results_Name
from Downstream import Log_Angle

wb = load_workbook(filename= Results_Name +'.xlsm', read_only=False, keep_vba=True)
ws7 = wb['timeData']

FILE = open('0_Downstream.txt', 'r+')
line = FILE.readline()
N = '2'


while line !="":
    print(line)
    ws7['A'+N] = line
    line = FILE.readline()
        N = float(N)
        N = (N+1)
        N = "%g" % N
    wb.save(Results_Name+'.xlsm')

    FILE.close()

I need to be able to get this to cycle through multiple files in the same directory and put the results in a seperate column than the last. Similar to the sheet below:Excel Layout

Thanks for any help.

Upvotes: 1

Views: 1272

Answers (4)

stovfl
stovfl

Reputation: 15533

  1. This Sample reads all files in test/txt
  2. Uses for every file a new Column
  3. Filename are assigned to Row 1

  4. Appends all reading lines, begining at Row 2

    # List all files in test/txt'
dirName = os.path.join('test', 'txt')

for column,fname in enumerate(os.listdir(dirName),1):
  # Column Title = Filename
  ws.cell(row=1, column=column, value=fname)

  # My Sample Text files are utf-8 encoded
  with io.open( os.path.join(dirName, fname),'r', encoding='utf8') as fh:
    # Column Data begins on Row 2
    for row,line in enumerate(fh,2):
      ws.cell(row=row, column=column, value=line)
  #end with = closing fh
#end for

    Tested with Python:3.4.2 - openpyxl:2.4.1 - LibreOffice: 4.3.3.2*

Upvotes: 1

Charlie Clark
Charlie Clark

Reputation: 19527

Don't use ws['A'+N] for programmatic access.

I think the following is probably close to what you want:

col_idx = ws.max_column + 1
for row_idx, line in enumerate(file, 1):
    if line == "":
        break
    ws.cell(row=row_idx, col=col_idx, value=line)
wb.save(…)

Upvotes: 0

Kelvin
Kelvin

Reputation: 1367

I was thinking along the same terms as Luke (just took a bit longer typing):

from openpyxl import load_workbook
from Test_Parameters import Results_Name
from Downstream import Log_Angle
import os



def good_name():
    wb = load_workbook(filename=Results_Name + '.xlsm', read_only=False, keep_vba=True)
    ws7 = wb['timeData']

    for path in os.listdir('dir_path'):
        # You can use glob here if you have other extensions than .txt
        ws = load_sheet(path, ws7)
    wb.save(Results_Name + '.xlsm')

def load_sheet(file_path, ws):
    with open(file_path, 'r+') as FILE:
        # This context manager is a cleaner way to open/close our file handle
        line = FILE.readline()
        N = '2'
        while line !="":
            print(line)
            ws['A'+N] = line
            line = FILE.readline()
            N = float(N)
            N = (N+1)
            N = "%g" % N
    return ws

Upvotes: 0

Luke.py
Luke.py

Reputation: 1005

import os
cwd = os.getcwd()

for filename in os.listdir(cwd):
    if filename.endswith(".txt"):
        with open('%s\\%s' % (cwd, filename), 'r+') as file:
            [DO STUFF]

The above code allows you to iterate over multiple files (of a specified type) in the current working directory.

Does this help?

Upvotes: 0

Related Questions