Posts: 10
Threads: 3
Joined: Feb 2024
Hello,
I'm working on automatizing one report using Python.
In general I have around 20 excel files (.xlsx and .xlms) that I filter base on some criteria and then copy&paste to final report. At the end I dropdown/autofill formulas in the final report (from first row till end) - I assume it will save some time in preparing report.
Is there any library/solution of copy&pasting data including formats? I have .xlsm file from SAP. In one column I have data that I need to extract (KG, PCs, EUR, USD ect). In "normal" process I copy&paste data to separte sheet and then use macro. I cannot use macro in original file as it has some "dummy" VBA code inside.
Also copy&paste solution with Python doesn't work as it miss formats between the files..
Posts: 832
Threads: 131
Joined: Jul 2017
Quite a lot of people seem to want to know how to get things from Excel, but they don't want to share what they have.
Nuclear Secrets!
You can check the openpyxl documentation here.
Or you could post a small example Excel file, then no one has to guess what you may, or may not, have.
Then you just need to make clear what, exactly, it is that you want to achieve, assuming you are clear about that.
Or, this 1-liner:
from magic import wand
resultXL = wand.wave("my_old_ excel", "my_total_solution_excel") 
Posts: 10
Threads: 3
Joined: Feb 2024
What I want to achive:
Either
1. Copy and paste data including formats into main report file. Next extracting format (KG, PCs, EUR, USD ect) in separate column
or
2. Extracting format (KG, PCs, EUR, USD ect) in separate column in source excel. Next copy and paste data into main report file.
or
3, Whatever activities that can be done to have data with extracted forma in main report file.
I'm testing possibilities using openpyxl using this documentation but without success.
Is there any additional information that would you like to have?
(Feb-23-2024, 01:05 PM)Pedroski55 Wrote: Quite a lot of people seem to want to know how to get things from Excel, but they don't want to share what they have.
Nuclear Secrets!
You can check the openpyxl documentation here.
Or you could post a small example Excel file, then no one has to guess what you may, or may not, have.
Then you just need to make clear what, exactly, it is that you want to achieve, assuming you are clear about that.
Or, this 1-liner:
from magic import wand
resultXL = wand.wave("my_old_ excel", "my_total_solution_excel")
Attached Files
test.xlsx (Size: 9.46 KB / Downloads: 12)
Posts: 832
Threads: 131
Joined: Jul 2017
Interesting, but not so challenging: your test.xlsx has 2 sheets, Sheet1 and Sheet2
Rows 1 and 2 of column A contain 2 4 digit numbers marked with an apostrophe, which marks them as strings, I believe.
A quick look tells me, this file contains no other data. Maybe you are CIA?
Either, these are coded Nuclear Secrets, masquerading as default formatted numbers or:
What do you want to do with this "data"?
Posts: 10
Threads: 3
Joined: Feb 2024
Feb-23-2024, 04:30 PM
(This post was last modified: Feb-23-2024, 04:31 PM by MasterOfDestr.)
(Feb-23-2024, 03:34 PM)Pedroski55 Wrote: What do you want to do with this "data"?
Answer is in post above....
What I want to achive:
Either
1. Copy and paste data including formats into main report file. Next extracting format (KG, PCs, EUR, USD ect) in separate column
or
2. Extracting format (KG, PCs, EUR, USD ect) in separate column in source excel. Next copy and paste data into main report file.
or
3, Whatever activities that can be done to have data with extracted forma in main report file.
I've tested copy&pasting -> it didn't work for me to keep format. If I do this I somehow "destroy" main report file. I cannot open it after launching Python script.
Launching VBA in original file to extract format (KG, PCs, EUR, USD ect) - not working due to already existing VBA code that stops using own VBA.
Questions?
Posts: 832
Threads: 131
Joined: Jul 2017
You want to write a programme to copy 4 default-formatted numbers from an Excel file to another Excel file?
Excuse me, but I hardly think such an effort is required, or worthwhile!
But if you look at the docs of openpyxl, they will show you how to do that quite easily.
Posts: 10
Threads: 3
Joined: Feb 2024
(Feb-23-2024, 04:53 PM)Pedroski55 Wrote: You want to write a programme to copy 4 default-formatted numbers from an Excel file to another Excel file?
Excuse me, but I hardly think such an effort is required, or worthwhile!
But if you look at the docs of openpyxl, they will show you how to do that quite easily.
First of all, provided file is a sample file. In process I have 3 files 40000 rows each.
Secondly, if I managed to work out something using docs of openpyxl I wouldn't post question on this forum.
Posts: 832
Threads: 131
Joined: Jul 2017
I'm sure there are a lot of people here who would like to help you, including me, but you really need to give us something to work with.
I don't want to guess what you have, where and how to get it, that makes it difficult to try things and difficult to help you.
People here have said they have 20GB Excel files to operate on! You don't need to post 20GB, even if you could!
You do not need to post 40000 rows, just enough for people to get a good idea of what you have. 100 rows is probably enough.
Help people to help you!
In openpyxl you just loop through the rows and columns searching for whatever it is you want and putting that data somewhere else. It's not rocket science!
|
