Win a copy of Real-World Software Development: A Project-Driven Guide to Fundamentals in Java this week in the Agile and Other Processes forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Bear Bibeault
  • Liutauras Vilda
  • Devaka Cooray
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • Henry Wong
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
Bartenders:
  • salvin francis
  • Frits Walraven
  • Piet Souris

How Can I Copy And Paste Data From Text File Into An Excel Sheet?

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Iirsh,

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:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!