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.
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.