How to import data from .txt file to a specifc excel sheet with Python?

Question

I am trying to automate a process that basically reads in values from text files into certain excel cells. I have a template in excel that will read data from various sheets under certain names. For example, the template will read in data from "Video scores". Video scores is a .txt file that I copy and paste into excel. There are 5 different text files used in each project so it gets tedious after a while and when there are a lot of projects to complete.

How can I import or copy and paste these .txt files into excel to a specified sheet? I have been using openpyxl for the other parts of this project, but I am open to using another library if it can't be done with openpxl.

I've also tried opening and reading a file, but I couldn't figure out how to do what I want with that either. I have found a list of all the files I need, its just a matter of getting them into excel.

Thanks in advance for anyone who helps.

So, your requirement is to read multiple files into same Excel sheet....Is there any separator in the files? — Harinie R, Mar 18 '20 at 15:15
Yes, the .txt files are automatically created. I can do them one by one in a for loop or any other loop its no problem. I just need to find a way to read the .txt file into a new excel sheet using Python. Once I figure that out I can repeat it for the other files. — Alan Piggott, Mar 18 '20 at 15:17

score 0 · Accepted Answer · answered Mar 18 '20 at 15:17

0

First, import the TXT file into a list in python, i'm asumming the TXT file is like this

1

2

3

4

....

with open(path_txt, "r") as e:
    list1 = [i for i in e]

then, we paste the values of the list on the worksheet you need

from openpyxl import load_workbook

wb = load_workbook(path_xlsx)
ws = wb[sheet_name]
ws["A1"] = "values" #just a header
row = 2 #represent the 2 row of the sheet
column = 1 #represent the column "A" of the sheet

for i in list1:
    ws.cell(row=row, column=column).value = i #getting the current cell, and writing the value of the list
    row += 1 #just setting the current to the next

wb.save(path_xlsx)

Hope this works for you.

answered Mar 18 '20 at 15:17

Matthias Almonacid

41
3

Hey! Thanks for the answer. Im getting the following error however, im not sure why. AttributeError: 'MergedCell' object attribute 'value' is read-only – Alan Piggott Mar 18 '20 at 15:45
Are you trying to write a cell that is merged, but not the left most one?, for example, if i've the cell A1:A2 merged, and i'm trying to write A2 – Matthias Almonacid Mar 18 '20 at 15:50
I refreshed my program and it seemed to fix itself.. weird. However a new error has found its way into my code. AttributeError: 'NoneType' object has no attribute 'read'. This occurs on the line when I am trying to save the file. – Alan Piggott Mar 18 '20 at 15:55
weird, this usually happens when calling a function or class method and its not returning anything or returning None. Can you add the full error log? – Matthias Almonacid Mar 18 '20 at 16:10
Sorry for the late reply! So for whatever reason, It just writes the lines A,B,C,D,E,F,G,H,I,K,L to A1. The file I am trying to write from is this https://filebin.net/lz2ws3avz34bgy3n – Alan Piggott Mar 19 '20 at 16:52
So, the code is working now, you're getting the values of the txt in the xlsx? And you want to write every column of the txt to its respective column in the xlsx file? – Matthias Almonacid Mar 19 '20 at 19:24

Harinie R · Answer 2 · 2020-03-18T17:45:20.427

Pandas would do the trick!

Approach: Have a sheet containing path to your files, separator, the corresponding target sheet names

Now read this excel sheet using pandas and iterate over each row for each file details, read the data, write it to new excel sheet of same workbook.

import pandas as pd

file_details_path = r"/Users/path for xl sheet/file details/File2XlDetails.xlsx"
target_sheet_path = r"/Users/path to target xl sheet/File samples/FiletoXl.xlsx"

# create a writer to save the file content in excel
writer = pd.ExcelWriter(target_sheet_path, engine='xlsxwriter')


file_details = pd.read_excel(file_details_path,
                             dtype = str,
                             index_col = False
                             )


def write_to_excel(file, trg_sheet_name):
    # writes it to excel
    file.to_excel(writer,
                  sheet_name = trg_sheet_name,
                  index = False,
                  )

# loop through each file record
for index, file_dtl in file_details.iterrows():

    # you can print and check the row content for reference
    print(file_dtl['File_path'])
    print(file_dtl['Separator'])
    print(file_dtl['Target_sheet_name'])

    # reads file
    file = pd.read_csv(file_dtl['File_path'],
                       sep = file_dtl['Separator'],
                       dtype = str,
                       index_col = False,
                       )
    write_to_excel(file, file_dtl['Target_sheet_name'])

writer.save()

Hope this helps! Let me know if you run into any issues...

Oops ! I just found that it is not writing to multiple sheets but always into same sheet....I have updated my code now for the same — Harinie R, Mar 18 '20 at 17:41

How to import data from .txt file to a specifc excel sheet with Python?

2 Answers2