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.
It wouldnt allow me to add a txt file to the post, so here it is on Filebin.
I'd really appreciate anyones help on this because I tried all day yesterday and couldnt do it myself!
What I have tried:
One thing I have tried to do is user pyperclip to open and copy the text file. I have that saved to a variable, so if I could somehow open excel and hit "paste" it would format fine. Another route I tried was using PyPi to press keys, but I couldnt get that to work either.
If I were to manually copy all of the .txt file and paste it directly into excel, it would format absolutely perfectly. I just dont know how I can do this with Python. If I use the "Load Data from txt" feature in excel it will also format and work perfectly. It seems like this should be easy to do with Python, but I just cannt get it!
Instead of thinking in term of copy & paste you should think in term of reading the content of the text file read by line and converting each line into list of values and then use openpyxl to insert these values into the Excel spreadsheet.
I see that your text file contains tab separated values (TSV) so you could load it very easily like this:
Personally I prefer using the csv module for this kind of things:
Then a simple way to insert the rows into the Excel spreadsheet is to use the append method. Here is an example showing how to do this with a new spreadsheet. Adapting it to your use case should be trivial.
Hope this helps.
I claim this furniture in the name of The Ottoman Empire! You can keep this tiny ad: