Python Forum

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.

Use os.walk() for iteration over directory tree. The rest - show some effort

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

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  does not help to understand you. Just talk cells, rows and columns - those are clear enough 

Thanks