Looping .xlsx files in folder/subfolders copy pasting currentregion - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: Looping .xlsx files in folder/subfolders copy pasting currentregion (/thread-2868.html) |
Looping .xlsx files in folder/subfolders copy pasting currentregion - HarrisQ - Apr-16-2017 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. RE: Looping .xlsx files in folder/subfolders copy pasting currentregion - volcano63 - Apr-16-2017 Use os.walk() for iteration over directory tree. The rest - show some effort RE: Looping .xlsx files in folder/subfolders copy pasting currentregion - HarrisQ - Apr-16-2017 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 RE: Looping .xlsx files in folder/subfolders copy pasting currentregion - volcano63 - Apr-16-2017 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)].valuePS 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 RE: Looping .xlsx files in folder/subfolders copy pasting currentregion - HarrisQ - Apr-17-2017 Thanks |