Python Forum
Looping .xlsx files in folder/subfolders copy pasting currentregion
Thread Rating:
  • 2 Vote(s) - 3.5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Looping .xlsx files in folder/subfolders copy pasting currentregion
#1
How to loop folders and subfolders selecting currentregions associated with a key word?

I want to loop excel (.xlsx) files in a folder and subfolders, searching for a key word ('trust') in column A, of worksheet 'data', and selecting the currentregion with offset 1 row, 0 columns.

I am from a VB background hence the term 'currentregion', so if the word 'trust' was in cell ("A2"), I want the currentregion (contiguous populated area from cell ("A3").

I want to copy paste these data ranges into a master workbook called 'master.xlsx', pasting the data to sheet 'm_data', into the next available empty row in column "A".

I know I can import os for looping, presumably with a file mask, and openpyxl to read and write with .xlsx, I am not sure how to select currentregion in each file and copy to next empty row in the master file.

Any help/pointers would be most helpful.

I am using the latest python3 interpreter and openpyxl module in pycharm/ Cygwin with python on windows 7 x64.

Thanks, 

Q

*edit has failed.

I also wanted to know if I could use shelve and a shelfFile to maintain a list of the processed files.
I want to skip a file if it has been processed before. This would require filepath and filename as files in different subfolders may have the same name.
Reply
#2
Use os.walk() for iteration over directory tree. The rest - show some effort
Test everything in a Python shell (iPython, Azure Notebook, etc.)
  • Someone gave you an advice you liked? Test it - maybe the advice was actually bad.
  • Someone gave you an advice you think is bad? Test it before arguing - maybe it was good.
  • You posted a claim that something you did not test works? Be prepared to eat your hat.
Reply
#3
Thanks. It is the text search with selection of currentregion offset (1,0) that i am particularly interested in if you have any suggestions regarding this?
Google with python and currentregion keeps returning VB references, perhaps unsurprisingly. 

Q
Reply
#4
I barely use openpyxl, but since cells of worksheet are addressable by column and row - see this example, iterating over row indices like that will give you cell values 
ws['A{}'.format(row_index)].value
PS Using other language terminology - who the f*ck is Alice, sorry VB Tongue does not help to understand you. Just talk cells, rows and columns - those are clear enough  Wink
Test everything in a Python shell (iPython, Azure Notebook, etc.)
  • Someone gave you an advice you liked? Test it - maybe the advice was actually bad.
  • Someone gave you an advice you think is bad? Test it before arguing - maybe it was good.
  • You posted a claim that something you did not test works? Be prepared to eat your hat.
Reply
#5
Thanks
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Copy Paste excel files based on the first letters of the file name Viento 2 346 Feb-07-2024, 12:24 PM
Last Post: Viento
  Compare folder A and subfolder B and display files that are in folder A but not in su Melcu54 3 465 Jan-05-2024, 05:16 PM
Last Post: Pedroski55
  Rename files in a folder named using windows explorer hitoxman 3 692 Aug-02-2023, 04:08 PM
Last Post: deanhystad
  Rename all files in a folder hitoxman 9 1,384 Jun-30-2023, 12:19 AM
Last Post: Pedroski55
  .py pandas matplotlib .xlsx files QubeStory 1 756 Mar-23-2023, 09:38 AM
Last Post: buran
  Create new folders and copy files cocobolli 3 1,330 Mar-22-2023, 10:23 AM
Last Post: Gribouillis
  Copy only hidden files and folders with rsync Cannondale 2 951 Mar-04-2023, 02:48 PM
Last Post: Cannondale
  How to loop through all excel files and sheets in folder jadelola 1 4,327 Dec-01-2022, 06:12 PM
Last Post: deanhystad
  python move folders and subfolders not working mg24 5 2,070 Nov-09-2022, 02:24 PM
Last Post: Larz60+
  python gzip all files from a folder mg24 3 3,807 Oct-28-2022, 03:59 PM
Last Post: mg24

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020