search string in Excel and save output to text

import xlrd

sheet_data = []

wb = xlrd.open_workbook('/Users/linda/PycharmProjects/hello/list_OpenStack_Azure_VMC-Mar2019.xlsx')

p = wb.sheet_names()

for y in p:

sh = wb.sheet_by_name(y)

for rownum in range(sh.nrows):

sheet_data.append((sh.row_values(rownum)))

found_list = []

rows_to_be_saved = []

for i in sheet_data:

if i[4] == "USSS" and (i[2] == "PAT" or i[2] == "PROD"):

found_list.append(i)

print(i)

else:

rows_to_be_saved.append(i)

text_file = open("Output.txt", "w")

text_file.write(str(found_list))

text_file.close()

============================

you can remove the last 3 lines and replace with below 4 lines, so you can send the output to an Excel file with pandas.

df = pd.DataFrame(found_list)

writer =pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

df.to_excel(writer, sheet_name='welcome', index=False)

writer.save()

Here is another solution to filter to a new Excel file:

==============================================================

file_name = "list_OpenStack_Azure_VMC-Mar2019.xlsx"

import pandas as pd

xl_workbook = pd.ExcelFile(file_name) # Load the excel workbook

df = xl_workbook.parse("OpenStack") # Parse the sheet into a dataframe

is_USSS = df['Deployment Application Code'] == 'USSS'

is_PATPROD = df['OS Environment'].isin(['PAT', 'PROD'])

df_USSS = df[is_USSS]

df_final = df_USSS[is_PATPROD]

df_final.to_excel(r'USSS.xlsx', index=False)