Process TXT like excel column

[Terenz]

Hello guys,

Maybe the title ins't explanatory, i have a TXT with this style:

Value - Another value
Word - Another word
Flash - Flash back

etc.

I want to process this value like a column, example add 1 to the first column and add 2 to the second:

Value1 - Another value2
Word1 - Another word2
Flash1 - Flash back2

Or adding another column:

Value - Another value - Third value
Word - Another word - Third word
Flash - Flash back - Flash re-back

Or delete it

Value
Word
Flash

Ok, i need to use StringSplit but i don't know how to continue. Suggestion? Thanks

[water]

You could import the file to Excel, do whatever you need to do and then rewrite the text file from Excel.

[orbs]

what you're looking at is a 2D array. for reading the text file into 2D array, and writing it back, start here:

'?do=embed' frameborder='0' data-embedContent>>

once in array, the mid-processing should not pose a problem: loop the array to change cells by index, use ReDim to add or remove columns, etc.

Excel looks a bit overkill to me for this task.

[water]

Excel looks a bit overkill to me for this task.

Maybe, but inserting columns is much easier with Excel than with a 2D array.

[orbs]

Maybe, but inserting columns is much easier with Excel than with a 2D array.

 

very true, but the re-saving multiple columns into text file, with the {space}{hyphen}{space} separator format... well, i wouldn't tip my toe in Excel here, unless i really really have to - i fear that will require extra scripting, or at least some elaborate string manipulation formula.

anyway @water i'm glad to see you back from your vacation. i didn't know programmers had any of those 

[water]

To easily work with Excel I would try to change the input file to true CSV format. Three characters as delimiter makes the task too complex.

Luckily I'm no programmer! Which can easily be seen when looking at the code I release

I really needed this vacation after having completed a big IT-Security project!

[Terenz]

Sorry guys but i can't use Excel for this task. Orbs please, if you want, can provide an example based on my post so i can work on it?

Thanks

[orbs]

@Terenz, i really hope that by now you started coding it by yourself, rather than waiting for my reply. if you did, you probably already have it working, and you won't be needing this:

#include <Array.au3>
#include "_filearray2d.au3"

; declare variables
Dim $aData[1]
Global $sFile=@ScriptDir&'\data.txt'

_FileToArray2D($sFile,$aData,' - ') ; get initial data
_ArrayDisplay($aData)   ; display it for verification

; add index
For $i=1 To $aData[0][0]
    $aData[$i][0]&=$i
    $aData[$i][1]&=$i
Next
_ArrayDisplay($aData)   ; display the result

; add column with values
ReDim $aData[$aData[0][0]+1][3]
For $i=1 To $aData[0][0]
    $aData[$i][2]=$aData[$i][1]&' in 3rd column'
Next
_ArrayDisplay($aData)   ; display the result

; remove columns except 1st
ReDim $aData[$aData[0][0]+1][1]
_ArrayDisplay($aData)   ; display the result

this is based on the functions i mentioned earlier, but slightly modified. save this code by the name "_filearray2d.au3" in the same folder as the script above:

expandcollapse popup

#include-Once

#include <File.au3>
#include <Array.au3>

; #INDEX# =======================================================================================================================
; Title .........: FileArray2D
; AutoIt Version : 3.2.10++
; Language ......: English
; Description ...: Functions for creating en reading 2D arrays.
; Author(s) .....: Frans (SnArF) Ordelman
; ===============================================================================================================================

; #NO_DOC_FUNCTION# =============================================================================================================
; Not working/documented/implemented at this time
; ===============================================================================================================================

; #CURRENT# =====================================================================================================================
;_FileToArray2D
;_FileFromArray2D
; ===============================================================================================================================

; #INTERNAL_USE_ONLY# ===========================================================================================================
; ===============================================================================================================================

; #FUNCTION# ====================================================================================================================
; Name...........: _FileToArray2D
; Description ...: Creates a 2D array from a file.
; Syntax.........: _FileToArray2D($aFile, ByRef $aArray, $aDelim = ";", $aStartRow = 1, $aEndRow = 0)
; Parameters ....: $aFile - File to read
;                  $aArray  - 2D Array to create
;                  $aDelim - [Optional] String delimiter, default is ";"
;                  $aStartRow - [Optional] Row to start, default is 1
;                  $aEndRow = [Optional] Row to end, default is 0 (all)
; Return values .: Success - Index of last added item
;                  Failure - -1, sets @error
; Author ........: Frans (SnArF) Ordelman
; Modified.......: orbs (31/12/2013): added flag = 1 to StringSplit (2 calls)
; Remarks .......: $aEndRow positive stops importing at file-line nr. in file,
;                  a negative value will import all lines - last nr off lines
; Related .......: _FileFromArray2D
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _FileToArray2D($aFile, ByRef $aArray, $aDelim = ";", $aStartRow = 1, $aEndRow = 0)
    Local $aRead, $aRows, $aColums, $aString, $i, $k
    $aRead = FileOpen($aFile)
    If $aStartRow < 1 Then $aStartRow = 1
    $aRows = _FileCountLines($aFile) + 1 - $aStartRow
    If $aEndRow > 0 Then $aRows = $aRows - ($aRows - $aEndRow + $aStartRow - 1)
    If $aEndRow < 0 Then $aRows = $aRows + $aEndRow

    $aColums = UBound(StringSplit(FileReadLine($aRead, $aStartRow), $aDelim,1)) - 1

    FileSetPos($aRead, 0, $FILE_BEGIN)
    FileReadLine($aRead, -1 + $aStartRow)

    Local $aArrayTemp[$aRows + 1][$aColums]
    $aArrayTemp[0][0] = $aRows

    For $i = 1 To $aRows
        $aString = StringSplit(FileReadLine($aRead), $aDelim,1)
        For $k = 0 To $aColums - 1
            $aArrayTemp[$i][$k] = $aString[$k + 1]
        Next
    Next

    FileClose($aRead)
    $aArray = $aArrayTemp
EndFunc   ;==>_FileToArray2D

; #FUNCTION# ====================================================================================================================
; Name...........: _FileFromArray2D
; Description ...: Creates a file from a 2D array.
; Syntax.........: _FileFromArray2d($aFile, $aArray, $aDelim = ";", $aWriteMode = 2, $aStartRow = 0, $aEndRow = 0, $aStartCol = 0, $aEndCol = -1)
; Parameters ....: $aFile - File to create
;                  $aArray  - 2D Array to read
;                  $aDelim - [Optional] String delimiter, default is ";"
;                  $aWriteMode - [Optional] Mode to open the file in, default is 2
;                  $aStartRow - [Optional] Row to start, default is 0
;                  $aEndRow = [Optional] Row to end, default is 0 (all)
;                  $aStartCol = [Optional] Colum to start, default is 0
;                  $aEndCol = [Optional] Column to end, default is -1 (all)
; Return values .: Success - Index of last added item
;                  Failure - -1, sets @error
; Author ........: Frans (SnArF) Ordelman
; Modified.......:
; Remarks .......: $aEndRow positive stops importing at row nr. in array,
;                  a negative value will import all rows - last nr off rows
; Related .......: _FileToArray2D
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _FileFromArray2d($aFile, $aArray, $aDelim = ";", $aWriteMode = 2, $aStartRow = 0, $aEndRow = 0, $aStartCol = 0, $aEndCol = -1)
    Local $aWrite, $aUboundRow, $aUboundCol, $aLine, $aDelimNr, $i, $k

    If Not IsArray($aArray) Then
        SetError(1)
        Return 0
    EndIf

    $aWrite = FileOpen($aFile, $aWriteMode)
    If $aWrite = -1 Then
        SetError(2)
        Return 0
    EndIf

    $aUboundRow = UBound($aArray, 1) - 1
    $aUboundCol = UBound($aArray, 2) - 1
    $aDelimNr = StringLen($aDelim)

    If $aEndRow > $aUboundRow Or $aEndRow = 0 Then $aEndRow = $aUboundRow
    If $aEndRow < 0 Then $aEndRow = $aUboundRow + $aEndRow
    If $aEndCol > $aUboundCol Or $aEndCol = -1 Then $aEndCol = $aUboundCol
    If $aEndRow < $aStartRow Then
        SetError(3)
        Return 0
    EndIf

    For $i = $aStartRow To $aEndRow
        $aLine = ""
        For $k = $aStartCol To $aEndCol
            $aLine &= $aArray[$i][$k] & $aDelim
        Next
        $aLine = StringTrimRight($aLine, $aDelimNr)
        FileWriteLine($aWrite, $aLine)
    Next

    FileClose($aWrite)
EndFunc   ;==>_FileFromArray2d

save your data in the text file "data.txt" in the same folder as the script, then launch the script. enjoy!

[mikell]

A similar way using the funcs from the Autoit UDF File.au3

#include <Array.au3>
#include <File.au3>

Global $sFile = @ScriptDir &' \data.txt'

; create the array
Local $aTemp
_FileReadToArray ($sFile, $aTemp)
StringReplace(FileReadLine($sFile, 1), " - ", "")
$iCols = @extended+1
Dim $aData[$aTemp[0]+1][$iCols]
$aData[0][0] = $aTemp[0]
For $i = 1 to $aTemp[0]
   $split = StringSplit($aTemp[$i], " - ", 3)
   For $j = 0 to UBound($split)-1
      $aData[$i][$j] = $split[$j]
   Next
Next
_ArrayDisplay($aData)   ; display it for verification

; add column index
For $j = 1 to $iCols
   For $i = 1 To $aData[0][0]
      $aData[$i][$j-1] &= $j
   Next
Next
_ArrayDisplay($aData)   ; display the result

_FileWriteFromArray(@ScriptDir & '\data2.txt', $aData, 1, Default, " - ")

[BrewManNH]

Your stringreplace isn't doing anything in that script.

[mikell]

It gives the number of columns needed to declare the array by the use of  @extended

[BrewManNH]

Ubound($aTemp, 2) would be a lot easier and a lot faster. Rube Goldberg would be proud though.

[mikell]

Obvious... sometimes I have memory blanks 

[jdelaney]

Regexp route:

#include <Array.au3>
$sfileRead = "Value - Another value - Third value" & @CRLF & _
"Word - Another word - Third word" & @CRLF & _
"Flash - Flash back - Flash re-back"

$sDelimiter = " - "
$sCurrent = "([^\r\n]+)"
$sLastWorking=""
While StringRegExp($sfileRead,$sCurrent,0)
    $sLastWorking = $sCurrent
    $sCurrent = "([^\r\n]+)" & $sDelimiter & $sCurrent
WEnd
$a = StringRegExp($sfileRead,$sLastWorking,4)

For $i = 0 To UBound($a)-1
    _ArrayDisplay($a[$i])
Next

Had to continually testing additions of collecting groups, because I can't figure out how to dynmaically do that with one regexp (hence, they while/wend)

; Example adding 1 to each 2nd item:
$aTemp = $a
ConsoleWrite("Example adding 1 to each 2nd item:" & @CRLF)
For $i = 0 To UBound($aTemp)-1
    $aTemp2 = $aTemp[$i]
    $aTemp2[2] &= 1
    ConsoleWrite(_ArrayToString($aTemp2,$sDelimiter,1) & @CRLF)
Next
ConsoleWrite(@CRLF)

$aTemp = $a
ConsoleWrite("Example removing the 1st element from each:" & @CRLF)
For $i = 0 To UBound($aTemp)-1
    $aTemp2 = $aTemp[$i]
    _ArrayDelete($aTemp2,1)
    ConsoleWrite(_ArrayToString($aTemp2,$sDelimiter,1) & @CRLF)
Next
ConsoleWrite(@CRLF)

Edited December 31, 2013 by jdelaney

[mikell]

BrewManNH,

Rube Goldberg would certainly have found how to get Ubound($aArray, 2) from a 1-dimensional array, as for instance the one returned by _FileReadToArray -- point on which I fairly admit give up

Happy new year 

[BrewManNH]

Yes, but eventually you have to convert the lines to a multidimensional array, otherwise it's not going to do you any good. You just did it earlier in the script than it was useful for the purpose of the text.

[mikell]

It is useful because it allows to fully declare the $aData array before populate it
But effectively here the best choice seems to place a ReDim inside the loop, mainly to face the possibility that the lines of the text have a different number of elements

expandcollapse popup

#cs - data.txt :
Value - Another value
Word - Another word
Flash - Flash back - Other flash
#ce
#include <Array.au3>
#include <File.au3>

$sFile = @ScriptDir & '\data.txt'

; create the array
Local $aData, $iFlag = 0
_FileReadToArray2D($sFile, $aData, " - ", $iFlag)
_ArrayDisplay($aData)   ; display the result

; add column index
For $j = 1 to UBound($aData, 2)
   For $i = $iFlag To UBound($aData)-1  ;$aData[0][0]
      If $aData[$i][$j-1] <>"" Then $aData[$i][$j-1] &= $j
   Next
Next
_ArrayDisplay($aData)   ; display the result

;_FileWriteFromArray(@ScriptDir & '\data2.txt', $aData, 1, Default, " - ")

;=====================================================
Func _FileReadToArray2D($sFile, ByRef $aArray, $aDelim = ";", $iFlag = 1)
  ; $iFlag = 0 : disable the return count in the first element
   Local $aTemp, $aSplit, $iRows, $iCols
   _FileReadToArray($sFile, $aTemp, $iFlag)
   $iRows = UBound($aTemp)
   $iCols = 1
   Dim $aArray[$iRows][$iCols]
   For $i = 0 to $iRows-1
       $aSplit = StringSplit($aTemp[$i], $aDelim, 3)
       If $iCols < UBound($aSplit) Then   
             $iCols = UBound($aSplit)
             Redim $aArray[$iRows][$iCols]
       EndIf
       For $j = 0 to UBound($aSplit)-1
             $aArray[$i][$j] = $aSplit[$j]
       Next
   Next
EndFunc

Edit : typos

Edited January 1, 2014 by mikell

[Malkey]

Hello guys,

Maybe the title ins't explanatory, i have a TXT with this style:

Value - Another value
Word - Another word
Flash - Flash back

etc.

 

I want to process this value like a column, example add 1 to the first column and add 2 to the second:

Value1 - Another value2
Word1 - Another word2
Flash1 - Flash back2

Or adding another column:

Value - Another value - Third value
Word - Another word - Third word
Flash - Flash back - Flash re-back

Or delete it

Value
Word
Flash

Ok, i need to use StringSplit but i don't know how to continue. Suggestion? Thanks

 

 

It may be  easier and faster using arrays. But, to show arrays are not necessary, this example is proof of concept.

With a little work, functions could be made from this example.

expandcollapse popup

Local $sText = "Value - Another value" & @CRLF & _
        "Word - Another word" & @CRLF & _
        "Flash - Flash back"
ConsoleWrite("------- Original Text --------------------" & @LF)
ConsoleWrite($sText & @LF)

ConsoleWrite(@LF & "----- Example add 1 to the first column and add 2 to the second. ---------" & @LF)
;I want to process this value like a column, example add 1 to the first column and add 2 to the second:
;Value1 - Another value2
;Word1 - Another word2
;Flash1 - Flash back2
Local $sAppendCol1 = 1
Local $sAppendCol2 = 2
Local $sText2 = StringRegExpReplace($sText, "(?m)^([^-\v]+?)(\s*-\s*)([^-\v]+)(\v*)", "${1}" & $sAppendCol1 & "${2}${3}" & $sAppendCol2 & "${4}")
ConsoleWrite($sText2 & @LF)

ConsoleWrite(@LF & "---------- Adding another column. ------------------" & @LF)
;Or adding another column:
;Value - Another value - Third value
;Word - Another word - Third word
;Flash - Flash back - Flash re-back
Local $sAnotherCol = "Third value" & @CRLF & "Third word" & @CRLF & "Flash re-back" ; The column to be added.
StringRegExpReplace($sText, "(\V+)", "")
Local $iLines = @extended ; Number of lines.  Trailing newlines are ignored.
ConsoleWrite("No of Lines: " & $iLines & @LF)
Local $sText3 = StringRegExpReplace($sText, "(?s)(^\V+)(.*)$", "\1 - " & StringRegExpReplace($sAnotherCol, "(?s)(^\V+)(.*)$", "\1") & "\2") ; First line of text.
For $i = 1 To ($iLines - 1)
    $sText3 = StringRegExpReplace($sText3, "(?m)((?:^.+\v+){" & $i & "})(\V+)", "\1\2 - " & StringRegExpReplace($sAnotherCol, "(?s)((?:.+?\v+){" & $i & "})(\V+)(.*)$", "\2"))
Next
ConsoleWrite($sText3 & @LF)

ConsoleWrite(@LF & "------- Delete a column. -------------------" & @LF)
;Or delete it
;Value
;Word
;Flash
ConsoleWrite("---Delete first column---" & @LF)
ConsoleWrite(StringRegExpReplace($sText, "(?m)^([^-\v]+\s*-?\s*)", "") & @LF) ; Deletes first column
ConsoleWrite("---Delete last column---" & @LF)
ConsoleWrite(StringRegExpReplace($sText, "(?m)(\s*-\s*[^-\v]+)$", "") & @LF) ; Deletes last column
ConsoleWrite("------------------------------------" & @LF)

Results:-

------- Original Text --------------------
Value - Another value
Word - Another word
Flash - Flash back

----- Example add 1 to the first column and add 2 to the second. ---------
Value1 - Another value2
Word1 - Another word2
Flash1 - Flash back2

---------- Adding another column. ------------------
No of Lines: 3
Value - Another value - Third value
Word - Another word - Third word
Flash - Flash back - Flash re-back

------- Delete a column. -------------------
---Delete first column---
Another value
Another word
Flash back
---Delete last column---
Value
Word
Flash
------------------------------------

[mikell]

Malkey,

Though I love regexes and commend your patterns, in this case they do not seem to be the appropriate way

If the text looks like this

Value - Another value
Word - Another word - Other word
Flash - Flash back

It becomes a lot more difficult and complicated, while remaining very easy using the usual _Array* funcs

expandcollapse popup

#cs - data.txt :
Value - Another value
Word - Another word - Other word
Flash - Flash back
#ce
#include <Array.au3>
#include <File.au3>

$sFile = @ScriptDir & '\data.txt'

; create the array
Local $aData, $iFlag = 1    ;<< return count in the first element
If $iFlag <> 1 Then $iFlag = 0
_FileReadToArray2D($sFile, $aData, " - ", $iFlag)
_ArrayDisplay($aData)   ; display the result

; add column index in cells
For $j = 1 to UBound($aData, 2)
   For $i = $iFlag To UBound($aData)-1 
      If $aData[$i][$j-1] <>"" Then $aData[$i][$j-1] &= $j
   Next
Next
_ArrayDisplay($aData)   ; display the result

; add a new column
$Cols = UBound($aData, 2)
Redim $aData[UBound($aData)][$Cols+1]
For $i = $iFlag To UBound($aData)-1  
    $aData[$i][$Cols] = "new_cell" & $Cols+1
Next
_ArrayDisplay($aData)   ; display the result

; delete column number $n (1-based, first column #1))
$n = 1    ; example
If $n = 1 Then $aData[0][$iFlag] = $aData[0][0]
_ArrayTranspose($aData)
_ArrayDelete($aData, $n-1)
_ArrayTranspose($aData)
_ArrayDisplay($aData)   ; display the result


;_FileWriteFromArray(@ScriptDir & '\data2.txt', $aData, 1, Default, " - ")

;=====================================================
Func _FileReadToArray2D($sFile, ByRef $aArray, $aDelim = ";", $iFlag = 1)
  ; $iFlag = 0 : disable the return count in the first element
   Local $aTemp, $aSplit, $iRows, $iCols
   _FileReadToArray($sFile, $aTemp, $iFlag)
   $iRows = UBound($aTemp)
   $iCols = 1
   Dim $aArray[$iRows][$iCols]
   For $i = 0 to $iRows-1
       $aSplit = StringSplit($aTemp[$i], $aDelim, 3)
       If $iCols < UBound($aSplit) Then   
             $iCols = UBound($aSplit)
             Redim $aArray[$iRows][$iCols]
       EndIf
       For $j = 0 to UBound($aSplit)-1
             $aArray[$i][$j] = $aSplit[$j]
       Next
   Next
EndFunc

Edited January 1, 2014 by mikell

[Terenz]

Many solution, all very intresting. I'm reading and tring to understand all. Happy 2014

P.S. The text will never be in this way:

Value - Another value
Word - Another word - Other word

Because i'm starting from one column, the second column take information from the first ( "another value" is based on "value" etc. ), the same for the third. Impossible to have "empty" result

Edited January 1, 2014 by Terenz