getting data appended to a cell each time a blank data is received in _stringbetween.

Cyborg5000 · March 14, 2023

Unsure what iam doing wrong here, but iam getting data appended to a cell each time a blank data is received in _stringbetween.

Although i have tried
if @error then

and different methods to write to the colloumn as N/A.

#include <Excel.au3>
#include <WinAPIFiles.au3>
#include <Array.au3>
#include <File.au3>
#include <FileConstants.au3>
#include <MsgBoxConstants.au3>
#include <String.au3>


Global $sWorkbookPath = "C:\example.xlsx" ; Replace with your workbook path
Global $sWorksheetName = "Sheet1" ; Replace with your worksheet name
Global $xlUp = -4162
Global $sFolder = "D:\Test"
Global $oExcel = _Excel_Open() ; Open an instance of Excel
Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbookPath) ; Open the workbook

Global $GNCNAME = "GNC-NAME:"
Global $SGE = "SGE:"
Global $UGE = "UGE:"
Global $ElapsedTime = "Elapsed Time:"
Global $OutageDuration = "Outage Duration:"
Global $ImpactDuration = "Impact Duration:"

Global $ImpactSummary = "Impact Summary:"
Global $AffectedCI = "Affected CI:"
Global $UpdateCST = "Update(CST):"
Global $ReasonforImpact = "Reason for Impact:"
Global $RecoveryAction = "Recovery Action:"
Global $StartTimeCST = "Start Time (CST):"
Global $EndTimeCST = "End Time (CST):"
Global $VendorTicket = "Vendor Ticket:"
Global $Notification = "Notification:"
Global $Resources = "Resources:"
Global $GNCCONTACT = "GNC-CONTACT:"



Local $hSearch = FileFindFirstFile($sFolder & "\" & "*.txt")

; Check if the search was successful, if not display a message and return False.
If $hSearch = -1 Then
    MsgBox($MB_SYSTEMMODAL, "", "Error: No files/directories matched the search pattern.")
    ;Return False
EndIf

; Assign a Local variable the empty string which will contain the files names found.
Local $sFileName = "", $iResult = 0

While 1
    $sFileName = FileFindNextFile($hSearch)
    ; If there is no more file matching the search.
    If @error Then ExitLoop

    ; Display the file name.
    ;$iResult = MsgBox(($MB_OKCANCEL + $MB_SYSTEMMODAL), "", "File: " & $sFileName)
    ;If $iResult <> $IDOK Then ExitLoop             ; If the user clicks on the cancel/close button.
    ;MsgBox(0, "test", $sFolder & "\" & $sFileName)
    $tempfolder = "D:\temp"


    FileCopy($sFolder & "\" & $sFileName, $tempfolder & "\test.txt")


    Global $tempfolder = "D:\Temp"
    Local $aFiles = _FileListToArray($tempfolder, "*.txt", $FLTA_FILES)
    For $i = 0 To UBound($aFiles) - 1



        Local $sFilePath = $tempfolder & "\" & $aFiles[$i]
        Global $sFileContents = FileRead($sFilePath)
        FileDelete("D:\Temp\String.txt")

        Call("GNC_NAME_SGE", $sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook)
        Call("GNC_NAME_UGE", $sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook)
        Call("SGE_OutageDuration", $sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook)
        Call("Resources_GNCCONTACT", $sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook)
        Sleep(1900)

    Next
    FileDelete($tempfolder & "\test.txt")
    FileDelete("D:\Temp\String.txt")

WEnd

_Excel_BookSave($oWorkbook) ; Save the workbook
_Excel_Close($oExcel) ; Close the Excel instance

Func GNC_NAME_SGE($sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook)
    ;================================================================ GNC NAME - SGE
    ;If StringInStr($sFileContents, $GNCNAME) And StringInStr($sFileContents, $SGE) Then
    Local $aMatches = _StringBetween($sFileContents, $GNCNAME, $SGE)
    If IsArray($aMatches) Then
        ; String was found between the start and end strings
        Local $sMatch = $aMatches[0]
        Sleep(100)
        $filestring = ("D:\Temp\String.txt")
        FileWrite($filestring, $sMatch)


        Local $sFileContents1 = FileRead($filestring)
        Do
            Local $sNewContents = StringStripWS($sFileContents1, $STR_STRIPLEADING + $STR_STRIPTRAILING + $STR_STRIPSPACES)
        Until @extended = 0
        FileDelete($filestring)
        FileWrite($filestring, $sNewContents)


        $line1 = FileReadLine($filestring, 1)
        $line2 = FileReadLine($filestring, 2)
        $line3 = FileReadLine($filestring, 3)
        ;MsgBox(0, "zvzxc", $line1)
        Local $sColumnName = "B"                 ; Replace with your column name
        Local $oWorksheet = $oWorkbook.Sheets($sWorksheetName)         ; Select the worksheet
        Local $iLastRow = $oWorksheet.Range($sColumnName & $oWorksheet.Rows.Count).End($xlUp).Row         ; Find the last row in the column
        $oWorksheet.Cells($iLastRow + 1, "A").Value = "SGE"
        ;$oWorksheet.Cells($iLastRow + 1, $sColumnName).Value = $sMatch
        $oWorksheet.Cells($iLastRow + 1, "R").Value = $sFilePath
        $oWorksheet.Cells($iLastRow + 1, "B").Value = $line1
        $oWorksheet.Cells($iLastRow + 1, "C").Value = $line2
        $oWorksheet.Cells($iLastRow + 1, "D").Value = $line3
        Sleep(100)
        FileDelete("D:\Temp\String.txt")
    EndIf
EndFunc   ;==>GNC_NAME_SGE


Func GNC_NAME_UGE($sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook)
    ;================================================================ GNC NAME - UGE
    Local $aMatches = _StringBetween($sFileContents, $GNCNAME, $UGE)
    If IsArray($aMatches) Then
        ; String was found between the start and end strings
        ; String was found between the start and end strings
        Local $sMatch = $aMatches[0]

        Sleep(100)
        $filestring = ("D:\Temp\String.txt")
        FileWrite($filestring, $sMatch)


        Local $sFileContents1 = FileRead($filestring)
        Do
            Local $sNewContents = StringStripWS($sFileContents1, $STR_STRIPLEADING + $STR_STRIPTRAILING + $STR_STRIPSPACES)
        Until @extended = 0
        FileDelete($filestring)
        FileWrite($filestring, $sNewContents)


        $line1 = FileReadLine($filestring, 1)
        $line2 = FileReadLine($filestring, 2)
        $line3 = FileReadLine($filestring, 3)
        ;MsgBox(0, "zvzxc", $line1)
        Local $sColumnName = "B"                ; Replace with your column name
        Local $oWorksheet = $oWorkbook.Sheets($sWorksheetName)         ; Select the worksheet
        Local $iLastRow = $oWorksheet.Range($sColumnName & $oWorksheet.Rows.Count).End($xlUp).Row                 ; Find the last row in the column
        $oWorksheet.Cells($iLastRow + 1, "A").Value = "UGE"
        ;$oWorksheet.Cells($iLastRow + 1, $sColumnName).Value = $sMatch             ; Add the new data to the next row in the column
        $oWorksheet.Cells($iLastRow + 1, "R").Value = $sFilePath
        $oWorksheet.Cells($iLastRow + 1, "B").Value = $line1
        $oWorksheet.Cells($iLastRow + 1, "C").Value = $line2
        $oWorksheet.Cells($iLastRow + 1, "D").Value = $line3
        Sleep(100)
        FileDelete("D:\Temp\String.txt")
    EndIf
EndFunc   ;==>GNC_NAME_UGE

Func SGE_OutageDuration($sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook)
    ;================================================================ SGE - OutageDuration
    Local $aMatches = _StringBetween($sFileContents, $SGE, $OutageDuration)
    If IsArray($aMatches) Then
        ; String was found between the start and end strings
        Local $sMatch = $aMatches[0]
        Local $sColumnName = "E"                     ; Replace with your column name
        Local $oWorksheet = $oWorkbook.Sheets($sWorksheetName)                     ; Select the worksheet
        Local $iLastRow = $oWorksheet.Range($sColumnName & $oWorksheet.Rows.Count).End($xlUp).Row                     ; Find the last row in the column
        $oWorksheet.Cells($iLastRow + 1, $sColumnName).Value = $sMatch                     ; Add the new data to the next row in the column
        $oWorksheet.Cells($iLastRow + 1, "F").Value = "Outage Duration:"

        ;Sleep(5000)
    EndIf
EndFunc   ;==>SGE_OutageDuration

Func Resources_GNCCONTACT($sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook)
    ;================================================================ Resources - GNCCONTACT
    Local $aMatches = _StringBetween($sFileContents, $Resources, $GNCCONTACT)
    If IsArray($aMatches) Then
        ; String was found between the start and end strings
        Local $sMatch = $aMatches[0]
        Local $sColumnName = "Q"                                                                                                                     ;   ;Replace with your column name
        Local $oWorksheet = $oWorkbook.Sheets($sWorksheetName)                                                                                                                     ; Select the worksheet
        Local $iLastRow = $oWorksheet.Range($sColumnName & $oWorksheet.Rows.Count).End($xlUp).Row                                                                                                                     ; Find the last row in the column
        $oWorksheet.Cells($iLastRow + 1, $sColumnName).Value = $sMatch                                                                                                                     ; Add the new data to the next row in the column

        ;Sleep(5000)
    EndIf
EndFunc   ;==>Resources_GNCCONTACT

In below function or text "$Resources and $GNCCONTACT" there is sometimes a blank text and iam trying to write NA to "$oWorksheet.Cells($iLastRow + 1, $sColumnName).Value = $sMatch"

Func Resources_GNCCONTACT($sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook)
   ;================================================================ Resources - GNCCONTACT
   Local $aMatches = _StringBetween($sFileContents, $Resources, $GNCCONTACT)
   If IsArray($aMatches) Then
       ; String was found between the start and end strings
       Local $sMatch = $aMatches[0]
       Local $sColumnName = "Q"
       Local $oWorksheet = $oWorkbook.Sheets($sWorksheetName) ; Select the worksheet
       Local $iLastRow = $oWorksheet.Range($sColumnName & $oWorksheet.Rows.Count).End($xlUp).Row ; Find the last row in the column
       $oWorksheet.Cells($iLastRow + 1, $sColumnName).Value = $sMatch ; Add the new data to the next row in the column

;Sleep(5000)
EndIf

tried as below

Func Resources_GNCCONTACT($sFileContents, $sWorkbookPath, $sWorksheetName, $oExcel, $oWorkbook)
   ;================================================================ Resources - GNCCONTACT
   Local $aMatches = _StringBetween($sFileContents, $Resources, $GNCCONTACT)
   If @Error Then

Local $sColumnName = "Q"
       Local $oWorksheet = $oWorkbook.Sheets($sWorksheetName) ; Select the worksheet
       Local $iLastRow = $oWorksheet.Range($sColumnName & $oWorksheet.Rows.Count).End($xlUp).Row ; Find the last row in the column
       $oWorksheet.Cells($iLastRow + 1, $sColumnName).Value = "NA"; Add the new data to the next row in the column

else

Local $sColumnName = "Q"
       Local $oWorksheet = $oWorkbook.Sheets($sWorksheetName) ; Select the worksheet
       Local $iLastRow = $oWorksheet.Range($sColumnName & $oWorksheet.Rows.Count).End($xlUp).Row ; Find the last row in the column
       $oWorksheet.Cells($iLastRow + 1, $sColumnName).Value = $aMatches ; Add the new data to the next row in the column

;Sleep(5000)
EndIf

EndFunc ;==>Resources_GNCCONTACT

Edited March 14, 2023 by Cyborg5000

Cyborg5000 · March 16, 2023

ANy help?

ioa747 · March 16, 2023

put on top of your script

#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 ;-w 5 -w 6 -w 7

at least until comment ; he will point out mistakes

assuming you have it installed SciTE4AutoIt3.exe

Edited March 16, 2023 by ioa747
corrections

Cyborg5000 · March 16, 2023

yup the latest.

Sign In

getting data appended to a cell each time a blank data is received in _stringbetween.

Recommended Posts

Cyborg5000

Link to comment

Share on other sites

Cyborg5000

Link to comment

Share on other sites

ioa747

Link to comment

Share on other sites

Cyborg5000

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta