replacing characters in a string

[Grantson]

Hi Guys

Im working on a script to manipulate a log file

Ive got it working and doing what I need to but I need a couple of tweeks

Log file

Appliance        Date      Site             Location         User Overall-Status
Earth       Insulation   Sub-Leak  Flash     Leakage   Tch-Leak  Load   Polarity
--------------------------------------------------------------------------------
0001             04/06/11  OFFICE           ROOM 1          GRANT S        PASS
      SKIP   >99.99MOhm      SKIP      SKIP      SKIP   <0.10mA      SKIP   SKIP
--------------------------------------------------------------------------------
0002             04/06/11  OFFICE           ROOM 1          GRANT S        PASS
      SKIP   >99.99MOhm      SKIP      SKIP      SKIP   <0.10mA      SKIP   SKIP
--------------------------------------------------------------------------------
0003             04/06/11  OFFICE           ROOM 1          GRANT S        PASS
      SKIP   >99.99MOhm      SKIP      SKIP      SKIP      SKIP      SKIP   SKIP
--------------------------------------------------------------------------------

End result - CSV

Appliance        Date      Site             Location         User Overall-Status,Earth,Insulation,Sub-Leak,Flash,Leakage,Tch-Leak,Load,Polarity
0001             04/06/11  OFFICE           ROOM 1          GRANT S        PASS,SKIP,>99.99MOhm,SKIP,SKIP,SKIP,<0.10mA,SKIP,SKIP
0002             04/06/11  OFFICE           ROOM 1          GRANT S        PASS,SKIP,>99.99MOhm,SKIP,SKIP,SKIP,<0.10mA,SKIP,SKIP
0003             04/06/11  OFFICE           ROOM 1          GRANT S        PASS,SKIP,>99.99MOhm,SKIP,SKIP,SKIP,SKIP,SKIP,SKIP

If i use StringstripWS to clear out extra spaces and then stringreplace to insert commas the then the spaces in the location, username & potentially site which can contain multiple spaces they become multiple entries in the CSV File

can anyone suggest a way of handling the spaces which need to be kept?

Dim $file
            $i = 1
            if not _FileReadToArray(GUICtrlRead($iptFile),$file) Then
                MsgBox(4096,"Error", " Error reading log to Array     error:" & @error)
                Exit
            EndIf
            dim $output[$file[0]]
            For $x = 1 to $file[0]
                $string1 = $file[$x]
                $x=$x+1
                $string2 = StringStripWS($file[$x],4)
                $string2 = StringReplace($string2," ",",")

                if $i = 1 Then
                    $string = $string1 & "," & $string2
                Else
                    $string = $string1 & $string2
                EndIf

                $output[$i] = $string
                $i = $i +1
                $x=$x+1
            Next
            _FileWriteFromArray("output.csv",$output)

[MvGulik]

Don't strip white spaces until you have secured the field data.

If the log is always using fixed-length records (looks that way). Use that information, for example, to add unambitious field delimiters.

0001             04/06/11  OFFICE           ROOM 1          GRANT S        PASS ... etc
---
0001            ;04/06/11 ;OFFICE          ;ROOM 1         ;GRANT S       ;PASS ... etc

see http://en.wikipedia.org/wiki/Comma-separated_values for more CSV info and trick.

Edited June 28, 2011 by iEvKI3gv9Wrkd41u

[Grantson]

thats what i was thinking but how do I go about inserting the commas in the right place is there a better way that using stringexplode to break the string down then piece it back together?

Don't strip white spaces until you have secured the field data.

If the log is always using fixed-length records (looks that way). Use that information, for example, to add unambitious field delimiters.

0001             04/06/11  OFFICE           ROOM 1          GRANT S        PASS ... etc
---
0001            ;04/06/11 ;OFFICE          ;ROOM 1         ;GRANT S       ;PASS ... etc

see http://en.wikipedia.org/wiki/Comma-separated_values for more CSV info and trick.

[MvGulik]

One possible way.

test()
Exit

Func test()
    ;; setup
    Local Enum $_ind_
    Local Const $SPACE = ' ', $DELIM = ';'

    ;; input
    Local $sLineIn = '0001             04/06/11  OFFICE           ROOM 1          GRANT S        PASS'
    Local $aiPositions[6] = [5, 17, 27, 44, 60, 75] ;; indexed array.

    ;; process
    Local $sTMP = StringMid($sLineIn, 1, $aiPositions[1] - 1) & $DELIM
    For $i = 2 To $aiPositions[$_ind_]
        If StringMid($sLineIn, $aiPositions[$i], 1) <> $SPACE Then Return SetError(1) ;; test, assuming field data are always seperated with a mimimum of a single space.
        $sTMP &= StringMid($sLineIn, $aiPositions[$i - 1] + 1, ($aiPositions[$i] - $aiPositions[$i - 1] - 1)) & $DELIM
    Next
    $sTMP &= StringMid($sLineIn, $aiPositions[$i - 1] + 1)

    ;; output
    Local $aLine = StringSplit($sTMP, $DELIM)

    #cs
        ;; Local (to me) code.
        DebugOut('$sLineIn', $sLineIn) ;### Debug DebugOut.
        DebugOut('$sTMP', $sTMP) ;### Debug DebugOut.
        DebugOut('$aLine', $aLine) ;### Debug DebugOut.
        $sLineIn = "0001             04/06/11  OFFICE           ROOM 1          GRANT S        PASS"  [String]
        $sTMP = "0001            ;04/06/11 ;OFFICE          ;ROOM 1         ;GRANT S       ;PASS"  [String]
        $aLine = [6,"0001            ","04/06/11 ","OFFICE          ","ROOM 1         ","GRANT S       ","PASS"]  [Array|7]
    #ce

EndFunc

---

Or, just drop the field-data directly into a array.

Edited June 28, 2011 by iEvKI3gv9Wrkd41u

[Malkey]

Try this.

;Local $str = FileRead("Name_of_Log_File.log")
; or
Local $str = "Appliance     Date    Site            Location        User Overall-Status" & @TAB & "Earth    Insulation   Sub-Leak  Flash    Leakage   Tch-Leak  Load   Polarity" & @CRLF & _
        "--------------------------------------------------------------------------------" & @CRLF & _
        "0001           04/06/11  OFFICE        ROOM 1          GRANT S     PASS        SKIP   >99.99MOhm   SKIP    SKIP    SKIP   <0.10mA      SKIP   SKIP" & @CRLF & _
        "--------------------------------------------------------------------------------" & @CRLF & _
        "0002           04/06/11  OFFICE        ROOM 1          GRANT S     PASS        SKIP   >99.99MOhm   SKIP    SKIP    SKIP   <0.10mA      SKIP   SKIP" & @CRLF & _
        "--------------------------------------------------------------------------------" & @CRLF & _
        "0003           04/06/11  OFFICE        ROOM 1          GRANT S     PASS        SKIP   >99.99MOhm   SKIP    SKIP    SKIP    SKIP    SKIP   SKIP" & @CRLF & _
        "--------------------------------------------------------------------------------"

; Remove more than 2 adjacent dash-lines & trailing white spaces.
$str = StringStripWS(StringRegExpReplace($str, "(-{2,}\v*)", ""), 2)

;Replace 2 or more adjacent spaces or a TAB with a coma.
$str = StringRegExpReplace($str, "( {2,}|\t)", ",")

; Specifically replace anything or nothing between User and Overall-Status with a coma.
$str = StringRegExpReplace($str, "(?i)(?<=user)(.*)(?=over)", ",")

ConsoleWrite($str & @CRLF)
;FileWrite("LogTest.csv", $str) ; Write to file.

[MvGulik]

;Replace 2 or more adjacent spaces (or a TAB with a coma).

That will glue two fields, where the data is only separated by a single space, together as one data element. (unless the logger is specifically adding 2 additional spaces between the (filled-out) field data.)

(And if one space is allowed inside the data, two spaces also might be allowed. Unlikely, but still.)

Edited June 29, 2011 by iEvKI3gv9Wrkd41u

[Grantson]

Thanks Malkey

Thats done the trick

Rattled off a 200 line log file with one single missed comma easily fixed by injecting an extra space in between the line break

Nearly done and for once i might actually finish a project i start lol

Try this.

;Local $str = FileRead("Name_of_Log_File.log")
; or
Local $str = "Appliance     Date    Site            Location        User Overall-Status" & @TAB & "Earth    Insulation   Sub-Leak  Flash    Leakage   Tch-Leak  Load   Polarity" & @CRLF & _
        "--------------------------------------------------------------------------------" & @CRLF & _
        "0001           04/06/11  OFFICE        ROOM 1          GRANT S     PASS        SKIP   >99.99MOhm   SKIP    SKIP    SKIP   <0.10mA      SKIP   SKIP" & @CRLF & _
        "--------------------------------------------------------------------------------" & @CRLF & _
        "0002           04/06/11  OFFICE        ROOM 1          GRANT S     PASS        SKIP   >99.99MOhm   SKIP    SKIP    SKIP   <0.10mA      SKIP   SKIP" & @CRLF & _
        "--------------------------------------------------------------------------------" & @CRLF & _
        "0003           04/06/11  OFFICE        ROOM 1          GRANT S     PASS        SKIP   >99.99MOhm   SKIP    SKIP    SKIP    SKIP    SKIP   SKIP" & @CRLF & _
        "--------------------------------------------------------------------------------"

; Remove more than 2 adjacent dash-lines & trailing white spaces.
$str = StringStripWS(StringRegExpReplace($str, "(-{2,}\v*)", ""), 2)

;Replace 2 or more adjacent spaces or a TAB with a coma.
$str = StringRegExpReplace($str, "( {2,}|\t)", ",")

; Specifically replace anything or nothing between User and Overall-Status with a coma.
$str = StringRegExpReplace($str, "(?i)(?<=user)(.*)(?=over)", ",")

ConsoleWrite($str & @CRLF)
;FileWrite("LogTest.csv", $str) ; Write to file.