Python Forum
Complex word search multiple files
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Complex word search multiple files
#1
Hello,

This is probably going to be the hardest thing I've had to explain haha. This is what I need to be able to do:



Start with word inside ws1 ['A1][28:] (example word 'A115' this word always changes as the script loops through each file.)

Open ws4 search row B (no header) for ws1['A1'][28:] (A115)

Once word (A115) is located in ws4 row B locate word that is 9 cells below it (example 'N90111.90' word always changes as script loops) then take that word and

open ws5 and find that word (N90111.90) under column A (no header) once that word is found go horizontally from that word (N90111.90) to column L and copy that value (20.22) and convert it from Miles to Ft. (106761.6) and then paste that value inside
ws3 column O row 2 (106761.6).




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


input_dir = 'C:\\work\\comparison\\NNM'
Summary = 'C:\\work\\comparison\\Summary.xlsx'
TLL = 'C:\\work\\comparison\\TLL.xlsx'
NDE = 'C:\\work\\comparison\\NDE.xlsx'
newFile = '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", ['T','Segment','Chainage(ft)','Segment','Chainage(ft)','Start Chainage Difference(ft)','Segment','Chainage(ft)','Segment','Chainage(ft)','End 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(TLL) 
ws4 = wb4.worksheets[0]

wb5 = xl.load_workbook(NDE) 
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


   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
  Open and read multiple text files and match words kozaizsvemira 3 3,782 Jul-07-2021, 11:27 AM
Last Post: Larz60+
  Plotting Multiple files ! Helen_145 1 229 Jun-26-2021, 03:28 PM
Last Post: snippsat
  Reading Multiple text Files in pyhton Fatim 1 254 Jun-25-2021, 01:37 PM
Last Post: deanhystad
  Running script on multiple files Afrodizzyjack 1 387 May-14-2021, 10:49 PM
Last Post: Yoriz
  Load the data from multiple source files to one table amy83 2 418 Apr-27-2021, 12:33 AM
Last Post: Pedroski55
  how search files Ron_Crafter 4 553 Apr-17-2021, 11:19 AM
Last Post: Ron_Crafter
  Rename Multiple files in directory to remove special characters nyawadasi 9 796 Feb-16-2021, 09:49 PM
Last Post: BashBedlam
  Merging all file_name.log's files from directory to one and search “PerformanceINFO" sutra 0 381 Dec-09-2020, 05:14 PM
Last Post: sutra
  search for more than one word using lambda illmattic 2 540 Nov-13-2020, 11:44 AM
Last Post: illmattic
  Searching for specific word in text files. JellyCreeper6 1 500 Nov-03-2020, 01:52 PM
Last Post: DeaD_EyE

Forum Jump:

User Panel Messages

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