How to read multiple text files from a directory, convert them all to excel files

Question

I have 10s of tab delimeted text files in my local directory. When I copy and paste a text file into an excel sheet, it becomes a file having 100s of columns. Now, I would like to read all the text files and convert them to corresponding excel files.

If there was a single file, I would have done the following way:

import pandas as pd
df = pd.read_csv("H:\\Yugeen\\text1.txt", sep='\t')
df.to_excel('H:\\Yugeen\\output1.xlsx', 'Sheet1', index = False)

Is there any way to achive a solution that I am looking for ?

There certainly is. You should look into a programming construct called a `for`-loop. — Arya McCarthy, Sep 26 '20 at 01:01
@AryaMcCarthy I know for loop. I am not sure about the file handling, that is why I asked this question here. — ychaulagain, Sep 26 '20 at 01:09

score 1 · Answer 1 · answered Sep 26 '20 at 03:43

I use this function to list all files in a directory, along with their file path:

import os

def list_files_in_directory(path):
    '''docstring for list_files_in_directory'''
    x = []
    for root, dirs, files in os.walk('.'+path):
        for file in files:
            x.append(root+'/'+file)
    return x

Selecting for only text files:

files = list_files_in_directory('.')
filtered_files = [i for i in files if '.txt' in i]

Like Sophia demonstrated, you can use pandas to create a dataframe. I'm assuming you want to merge these files as well.

import pandas as pd

dfs = []
for file in filtered_files:
    df = pd.read_csv(file,sep='\t')
    dfs.append(df)

df_master = pd.concat(dfs,axis=1)
filename = 'master_dataframe.csv'
df_master.to_csv(filename,index=False)

The saved file can then be opened in Excel.

score 0 · Answer 2 · answered Sep 26 '20 at 01:21

Are you talking about how to get the filenames? You can use the glob library.

import glob
import pandas as pd

file_paths = glob.glob('your-directory\\*.txt')
for file in file_path:
    df = pd.read_csv(file,sep='\t')
    df.to_excel('output-directory\\filename.xlsx',index=False)

Does this answer your question?

How to read multiple text files from a directory, convert them all to excel files

2 Answers2