Search for specific string in multiple excel files

Question

I'm new to python, so bear with me on this one. I want to make a script that searches within all excel files in a specific folder, to see if they contain an '@' sign. If it finds a match it should write to a log.txt files with the name of the excel file. I don't have the name of the excel files in advance.

So far I'm stuck at just read in one file, and return true if it finds the string. What I got is

import pandas as pd

df = pd.read_excel(open('test.xlsx','rb'), sheetname=0)
print(df)

if '@' in df:
    print("true")
else:
    print("false")

This returns the content of sheet1 in the excel file correctly, but the search to find a match, doesn't seem to work. Any ideas? Maybe Im doing it all wrong.

Grr · Accepted Answer · 2018-01-03 21:08:37Z

3

You should read through Pandas - Working with Text Data specifically the section on strings that match or contain a pattern.

Unfortunately you cannot search a DataFrame with the string methods common to core Python. Instead you would use a StringMethod, like so:

out = False
for col in df.columns:
    if df[col].str.contains('@').any():
        out = 'True'
        break
print(out)

Also, I don't believe it is necessary to use open when using pd.read_excel. E.g

df = pd.read_excel(open('test.xlsx','rb'), sheetname=0)

Can be rewritten:

df = pd.read_excel('test.xlsx', sheetname=0)

As far as iterating over files in a folder take a look at the builtin module glob.

answered Jan 3, 2018 at 21:08

Grr

16.2k7 gold badges72 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ninjasoup · Accepted Answer · 2018-01-04 16:38:05Z

1

This an answer without using pandas

import logging

LOG_FILENAME = r'file_output_location_path\log.txt'
logging.basicConfig(filename=LOG_FILENAME,
                    level=logging.WARNING,
                    format='%(asctime)s %(message)s'
                    )


source_folder = r'excel_file_folder_path'

def containsString():
    for somefile in os.listdir(source_folder):
        if'@' in somefile:
            logging.warning(somefile)

answered Jan 4, 2018 at 16:38

Ninjasoup

1731 silver badge14 bronze badges

2 Comments

Bidstrup Over a year ago

Beginner questions: what the r' stand do? and 'excel_file_folder_path' should that be './foldername'?

Bidstrup Over a year ago

Dosnt seems like this is working, it can find the files, but it dosnt find any @'s when the xlsx file contains it.

Collectives™ on Stack Overflow

Search for specific string in multiple excel files

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related