Python Forum
String search in different excel
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
String search in different excel
#1
Hello,

I'm working on code in between the ###### symbols near the bottom. I read in 'TN' column B only, and then I read in TDetail and made TDetail into a string. Cell A1 inside TDetail has a word that I want to find inside TN (word Exampee A1150). The word in cell A1 (A1150) changes within each file so I can't just search for A1150 as the script loops through each file that word will change thats why I made TDetail into string. The variable window shows 'TDetail str 1 A1150'. When it loops to the next file it shows A1560. I want to be able to locate that string each time it loops inside TN.




import openpyxl as xl 
import os
import pandas as pd
import xlsxwriter
import xlrd


input_dir = 'C:\\work\\comparison\\NNM'
Summary = 'C:\\\\comparison\\Summary.xlsx'
TN = 'C:\\work\\comparison\\TN.xlsx'
ND = 'C:\\\work\\comparison\\ND.xlsx'
newFile = 'C:\\work\\comparison\\Comparison.xlsx'




files = [file for file in os.listdir(input_dir)
         if os.path.isfile(file) and file.endswith(".xlsx")]

i=0
ii=0

wb3=xlsxwriter.Workbook(newFile)
ws3=wb3.add_worksheet('Comparison')


format = wb3.add_format()
format.set_align('center')
format.set_align('vcenter')
format.set_bold(True)
format.set_font_size(14)
format.set_bg_color('#C9C9C9')
format.set_border(style=1)


#ws3.write('F2:', '=C2-E2')

ws3.write_row("A1:Q1", ['Name','Segment','Chainage(ft)','Segment','Chainage(ft)','Difference(ft)','Segment','Chainage(ft)','Segment',' Chainage(ft)','Chainage Difference(ft)','Stops','Stops','Distance','Distance','Distance Difference (ft)','Comments'], format)
ws3.set_column(1, 17, 35)
wb3.close()

wb3 = xl.load_workbook(newFile) 
ws3 = wb3.worksheets[0]

wb2 = xl.load_workbook(Summary) 
ws2 = wb2.worksheets[1]

wb4 = xl.load_workbook(TN) 
ws4 = wb4.worksheets[0]

wb5 = xl.load_workbook(ND) 
ws5 = wb5.worksheets[0]


for file in files: 
   input_file =  os.path.join(input_dir, file)
   wb1=xl.load_workbook(input_file)
   ws1=wb1.worksheets[0]
   

   series = pd.read_excel(file,usecols = "F",squeeze = True)
   counts = series.value_counts()
   z = counts[0]/2
###########################################

   TFile=pd.read_excel(TN, usecols="B")
   TDetail=pd.read_excel(input_file, usecols="A",nrows=1,header=None,squeeze=True).str.slice(start=28,stop=-2).to_string(index=False).strip()
   TFile[TFile['Name'].str.contains("TDetail")==True]



###################################################
   ws3[f'A{i+2}']=ws1['A1'].value[28:]
   ws3[f'D{i+2}']=ws1['B4'].value
   ws3[f'E{i+2}']=ws1['D4'].value
   ws3[f'I{i+2}']=ws1['B'][-1].value
   ws3[f'J{i+2}']=ws1['D'][-1].value
   ws3[f'O{i+2}']=ws1['E'][-1].value 
   ws3[f'N{i+2}']=ws2[f'I{ii+6}'].value   
   ws3[f'M{i+2}']=z
   
   i += 1
   ii +=1          
   


   wb3.save(newFile)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Writing a Linear Search algorithm - malformed string representation Drone4four 10 833 Jan-10-2024, 08:39 AM
Last Post: gulshan212
  Search Excel File with a list of values huzzug 4 1,147 Nov-03-2023, 05:35 PM
Last Post: huzzug
  trouble reading string/module from excel as a list popular_dog 0 384 Oct-04-2023, 01:07 PM
Last Post: popular_dog
  Search multiple CSV files for a string or strings cubangt 7 7,842 Feb-23-2022, 12:53 AM
Last Post: Pedroski55
  Search string in mutliple .gz files SARAOOF 10 6,775 Aug-26-2021, 01:47 PM
Last Post: SARAOOF
  fuzzywuzzy search string in text file marfer 9 4,432 Aug-03-2021, 02:41 AM
Last Post: deanhystad
  I want to search a variable for a string D90 lostbit 3 2,582 Mar-31-2021, 07:14 PM
Last Post: lostbit
  Openpyxl tkinter search a value in Excel column Heathcliff_1 0 3,212 Dec-02-2020, 04:35 PM
Last Post: Heathcliff_1
  Interactive Menu, String Search? maggotspawn 3 2,540 May-11-2020, 05:25 PM
Last Post: menator01
  Search Excel column values jonzee 1 2,568 Dec-21-2019, 02:38 AM
Last Post: Clunk_Head

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020