Convert multiple input files into one single Excel file, with Python

Question

as I do not have much knowledge about Python I would like to ask how can I save multiple input files to one single Excel file in different columns? I have a python script which takes as input a single log file:

file = 'output_1.log'

and outputs in an Excel file some results of this log file in a single column like this:

data = open(file, 'r')

How can I change these commands in order to read multiple text files (*.log) and print the results in a single Excel file in different columns for each input file?

Thank you...

1. What are you logging? 2. What are you trying to accomplish by condensing them in to 1 file? 3. What sort of collation do you need done on the log messages from the different logs? — Silas Ray
– Silas Ray, Commented Jul 23, 2012 at 16:32
I have already a python script which logs speed and acceleration of vehicles and outputs the speed and acceleration of a vehicle in 2 excel files one for speed and one for acceleration. But this script only reads a specific file with this command: file = 'output_1.log'. I want to read the logs of e.g. 20 vehicles and output the speed and acceleration in 2 different excel files, one column for each vehicle. — athpapa
– athpapa, Commented Jul 23, 2012 at 16:37
General overview is: 1) Get a list of the files you want to parse, 2) loop through the files and read them while writting out to Excel. I'm not sure what you're using to currently write to Excel, but that step shouldn't change. — ernie
– ernie, Commented Jul 23, 2012 at 16:51
All log files are guaranteed to be the same length? And do you want a header row saying which column corresponds to which file? — David Robinson
– David Robinson, Commented Jul 23, 2012 at 16:52
No the log files do not have the same length and the script already has a header saying which column corresponds to which file. The only change that I want is that the script now works only for specific log file. As I wrote above "file = 'output_1.log'" and uses this: "data = open(file, 'r')" to read the file and extract the results in excel. As I do not have experience with Python is any way to change these commands to read multiple log files and print the results in the same excel file in different rows? — athpapa
– athpapa, Commented Jul 23, 2012 at 17:00

David Robinson · Accepted Answer · 2012-07-23 16:58:37Z

1

This would do what you want. It reads each file in individually to create a dictionary mapping each filename to its list.

import glob

data = {}
for infile in glob.glob("*.log"):
    with open(infile) as inf:
        data[infile] = [l[:-1] for l in inf]

with open("summary.xls", "w") as outf:
    outf.write("\t".join(data.keys()) + "\n")
    for sublst in zip(*data.values()):
        outf.write("\t".join(sublst) + "\n")

answered Jul 23, 2012 at 16:58

David Robinson

78.8k16 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TimothyAWiseman · Accepted Answer · 2012-07-23 17:06:45Z

0

David's answer is a fine option.

Personally, I would be more inclined to iterate through all of the log files using something like os.walk (if pulling all from a single directory) or iterating over them from the user supplied list.

As for writing the XLS file, you certainly can do it as a TSV using join as David pointing out. However, using a library like XLWT can give you more control and access to more of excels features. If you do want to do it as a TSV, you still may want to look at the CSV library with its writer objects to help with things like text quoting.

answered Jul 23, 2012 at 17:06

TimothyAWiseman

15k12 gold badges42 silver badges49 bronze badges

Collectives™ on Stack Overflow

Convert multiple input files into one single Excel file, with Python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related