Hello, I hoping to reach out for some help with a Python/Excel task that I cannot figure out.
Here it is summed up:
I have a big excel workbook with multiple sheets and many formulas, but ultimately the workflow is simple.
The first sheet is a data entry sheet. You paste output data into this data entry sheet from a csv file.
The rest of the sheets with formulas are referenced to this data entry sheet.
Once you paste the data into the data entry sheet, the data is run through all the excel formulas in the workbook, and then your final value calculation is displayed in the final results sheet.
I will call this work book a model. Essentially I am taking a CSV file and pasting all of the data in that CSV file into the data entry sheet of the model to get my results in the results sheet. If I just have a few CSV files, then I can just do that manually and it’s no big deal. However, I will be working with hundreds of CSV files, and individually copy and pasting the data from each CSV file into the model would be a nightmare, so I am trying to avoid this task with a Python script. I have tried searching for instructions on how to do this, but am not sure what to even call this method. I will be given a zipfile/folder that contains let’s say 100 CSV files, referenced as let’s say “CSV1”, “CSV2”, “CSV3”, CSV4”, etc.
How do I write a Python script that pastes each csv file into the model?
The end product I am looking for is a spreadsheet that lists the model results for each CSV file put into the model. So for example:
CSV1: 54
CSV2: 44
CSV3: 65
CSV4: 34
etc…
Is this doable with Python? I am guessing I will need to combine the use of pandas and openpyxl. While I am sure all of the formulas in my model can be reproduced through python functions, I think it will just be easier to use the excel model as is. It’s just a matter of how to automate all of the copying and pasting that I am stuck with.
Thank you!
Some easy tips for solution to your proble are:
You don't need Python to do this. There are lots of ways to get data into Excel. Querytables , SQL, Import Wizard etc. A simple VBA macro will do eg. https://stackoverflow.com/questions/12197274/is-there-a-way-to-import-data-from-csv-to-active-excel-sheet
https://realpython.com/openpyxl-excel-spreadsheets-python/
https://www.datacamp.com/community/tutorials/power-spreadsheets-python
https://www.datacamp.com/community/tutorials/python-excel-tutorial
I can suggest to avoid excell calculations and processes all.your csv files into pandas data frame.... perform your operations with numpy and at the end create a result file...
- Badges
- Science topic
- Similar topics
- Computer Science and Engineering
- Programming Languages
- Python