~ / archivo / stackoverflow / 49946165
Efficient way to Identify xlsx & csv files in a directory, which contain specific string in the data
-
For GDPR purposes, I am trying to identify all spreadsheets within a directory, containing specific strings in the data.
The following code has worked on small files, but takes too long to be practical with any larger spreadsheets (1000+ rows).
It's worth mentioning that I don't know which columns these strings will appear in, so can't use cell location to improve efficiency.
If there's a better way to do the following could you please share?
def list_files(dir): r = [] for root, dirs, files in os.walk(dir): for name in files: r.append(os.path.join(root, name)) return r all_files = list_files("filepath") filenames = [] for f in all_files: if not f.endswith((".xls", ".xlsx")): continue wb = load_workbook(f) for sheet in wb.worksheets: for a in range(1, sheet.max_row+1): for b in range(sheet.max_column): if sheet[a][b].value: if str(sheet[a][b].value).upper() in("STRING_1","STRING_2","STRING_3"): filenames.append(f) set(filenames)- tdelaney 2018-04-20 16:45 Can you fix the indentation for readability?
-
If there's a better way to do the following could you please share?
If you have Linux there is. And if you don't you'll only need a terminal emulator like Cygwin or Baboon in Windows.
I've created two files: example.xlsx (newer format of xls) and example.csv and added data into each file.
Inside those files I put some strings like: dodo, lolo, string1, string2 among others.
mortiz@alberta:~/Documents/test$ ls -ltr total 20 -rw-r--r-- 1 mortiz mortiz 4822 Apr 20 16:34 example.xlsx -rw-r--r-- 1 mortiz mortiz 68 Apr 20 16:34 example.csvThere's an utility in almost every linux distro called "grep" which let you search "strings" inside almost anything.
There are two easy ways:
Unziping the xlsx files and using grep
when you unzip the xlsx, you'll find a directory called "lx" inside it an xml file with the data called "sharedStrings.xml"
mortiz@alberta:~/Documents/test$ unzip example.xlsx Archive: example.xlsx inflating: _rels/.rels inflating: docProps/app.xml inflating: docProps/core.xml inflating: xl/_rels/workbook.xml.rels inflating: xl/workbook.xml inflating: xl/styles.xml inflating: xl/worksheets/sheet1.xml **inflating: xl/sharedStrings.xml** inflating: [Content_Types].xmlBy now we just unzipped the file, we'll see the output of grep below.
Converting xlsx to xls and using grep
The xls format is easier to read, so using this command we will generate a version of the xlsx in xls, then using strings to read its content.
mortiz@alberta:~/Documents/test$ libreoffice --headless --convert-to xls example.xlsx convert /home/mortiz/Documents/test/example.xlsx -> /home/mortiz/Documents/test/example.xls using filter : MS Excel 97Now using grep to find the files with specific strings
Finding the strings with this utility would be the easier and fastest way I know.
Because you need to know which files have those strings you must use grep, the -R (recursive) and i (insensitive, doesn't matter about camel case).
mortiz@alberta:~/Documents/test$ grep -Ri lolo * example.csv:no data,lolo,foo Binary file example.xls matches xl/sharedStrings.xml:<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="12" uniqueCount="12"><si><t xml:space="preserve">string1</t></si><si><t xml:space="preserve">string</t></si><si><t xml:space="preserve">string2</t></si><si><t xml:space="preserve">no data</t></si><si><t xml:space="preserve">lolo</t></si><si><t xml:space="preserve">foo</t></si><si><t xml:space="preserve">test</t></si><si><t xml:space="preserve">mia</t></si><si><t xml:space="preserve">ami</t></si><si><t xml:space="preserve">nono</t></si><si><t xml:space="preserve">toto</t></si><si><t xml:space="preserve">dodo</t></si></sst>To print only the filenames add the parameter "l", this will print each file with the string "lolo" inside:
mortiz@alberta:~/Documents/test$ grep -Ril lolo * example.csv example.xls xl/sharedStrings.xmlGrep will work well with xml, csv, xls or plain text files, that's why we need to unzip or convert the xlsx :)
-
joseph stern respuesta 2018-04-24 08:41
Thanks for the help Miguel, I would like to try your solution but am quite new to this kind of scripting, and don't have the tools on my work machine.
I was shown this Python code, which works on my xlsx files. (the field names i'm searching for are usually pasted in the first row of a spreadsheet & this code returns when a match is found).
The process still has room for improvement. Converting the files to csv (or xls?) and parsing as single string would speed things up.
import xlrd import glob def search_excel(path, phrases): try: wb = xlrd.open_workbook(path) except xlrd.biffh.XLRDError: # Assumed False if document fails to open (e.g. invalid format/corrupt) return False for sheet in wb.sheets(): for row in sheet.get_rows(): for cell in filter(lambda x: x.ctype==1, row): # Only operate on string typed cells try: if any(phrase in cell.value for phrase in phrases): # Return as soon as possible if criteria fulfilled return path except TypeError: pass if __name__ == "__main__": # Recursively searches all nested directories/files for files # Change the path here. file_paths = glob.glob("...path...", recursive=True) search_phrases = ['string_1','STRING_1','string_2','STRING_2'] list_comp = [search_excel(path, search_phrases) for path in file_paths]