Saltar al contenido

~ / archivo / stackoverflow / 49946165

Efficient way to Identify xlsx & csv files in a directory, which contain specific string in the data

StackOverflow · python, string, file, xlsx · ayudé
  1. joseph stern pregunta +1 2018-04-20 16:38

    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?
  2. Miguel Ortiz yo respuesta 2018-04-20 20:05

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

    There'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].xml     
    

    By 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 97
    

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

    Grep will work well with xml, csv, xls or plain text files, that's why we need to unzip or convert the xlsx :)

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

← todos los hilos de StackOverflow