Transcode single column date to multiple columns

[Myicq]

I have an interesting project, where I need to convert a text file. Essentially I need to take 1 column and turn it into multiple columns. The purpose is to convert a single col. text file database into a "real" database using 1 row per record.

Example:

1
2
3
4
5
6

becomes

(using 3 *n format)
1,2,3
4,5,6
or
(using 4*n format)
1,2,3,4
5,6,,

the no. of columns is variable. Data is always left-right, top-bottom. Missing data is empty as per last example.

Excel can do this via formulas, but if of course limited to 65535 rows (XLS format)

I have the basic idea of how to do, but I stumble into a problem when the no. of rows is not equal to cols*rows (second example 4*2 = 8 which is > 6.)

My idea something like

column =1
for each line
   while column =< max_cols
      print line
      print separator
      column = column +1
   wend
   column= 1
   print linefeed
next line

I have also considered putting all lines into a large array (1 * n), but not sure about if I meed some boundaries here ? And what improvements this would give me.

I would prefer using AutoIT, since I need to add a small GUI on top. That part is simple.

Thankful for any input. Complete example will be posted here once done.

[Malkey]

Here are two ways.

expandcollapse popup

Local $sStr = "12" & @CRLF & "34" & @CRLF & "56" & @CRLF & "7" & @CRLF & "89" & @CRLF & "10" & _
        @CRLF & "11" & @CRLF & "13" & @CRLF & "14" & @CRLF & "15" & @CRLF & "16" & @CRLF & "99" & @CRLF
Local $iCols = 5

ConsoleWrite(_StringDisplayRE($sStr, $iCols) & @LF)
ConsoleWrite(_StringColumnDisplay($sStr, $iCols) & @LF)

MsgBox(0, "RE Display in " & $iCols & " columns.", $sStr & @LF & _StringDisplayRE($sStr, $iCols))
MsgBox(0, "Text Display in " & $iCols & " columns.", $sStr & @LF & _StringColumnDisplay($sStr, $iCols))


Func _StringDisplayRE($sStr, $iNumOfCol = 1)
    If $iNumOfCol < 1 Then $iNumOfCol = 1
    Local $a = StringRegExpReplace($sStr, "(.*[^v])(?:v+)", "${1},")
    Local $iNum = @extended
    Local $extra = Mod($iNumOfCol - Mod($iNum, $iNumOfCol), $iNumOfCol)
    If $extra <> 0 Then $a &= StringRegExpReplace(StringFormat("%" & $extra & "s", " "), ".", ",")
    Return StringTrimRight(StringRegExpReplace($a, "((?:[^,]*,){" & ($iNumOfCol - 1) & "}(?:[^,]*))+?,", "$1$2" & @LF), 1)
EndFunc   ;==>_StringDisplayRE

Func _StringColumnDisplay($sStr, $iNumOfCol = 1)
    If $iNumOfCol < 1 Then $iNumOfCol = 1
    Local $sRet
    Local $aLines = StringRegExp($sStr, "(?:([^v]+)(?:v+|$))", 3)
    Local $iNumLines = UBound($aLines)
    Local $extra = Mod($iNumOfCol - Mod($iNumLines, $iNumOfCol), $iNumOfCol)
    For $i = 1 To $iNumLines + $extra
        If $i < $iNumLines + 1 Then
            If Mod($i, $iNumOfCol) Then
                $sRet &= $aLines[$i - 1] & ","
            Else
                $sRet &= $aLines[$i - 1]
                If $i < $iNumLines + $extra Then $sRet &= @LF
            EndIf
        Else
            If $i < $iNumLines + $extra Then $sRet &= ","
        EndIf
    Next
    Return $sRet
EndFunc   ;==>_StringColumnDisplay

[water]

Something like this?

Global $aArrayIn[10] = [1,2,3,4,5,6,7,8,9,10]
Global $iColumns = 4
Global $sOutRec = ""
For $i = 0 To UBound($aArrayIn) -1
    If Mod($i+1, $iColumns) = 0 Then
        $sOutRec &= $aArrayIn[$i] & ","
        ConsoleWrite(StringLeft($sOutrec, StringLen($sOutRec)-1) & @CRLF)
        $sOutRec = ""
    Else
        $sOutRec &= $aArrayIn[$i] & ","
    Endif
Next
If $sOutRec <> "" Then ConsoleWrite(StringLeft($sOutrec, StringLen($sOutRec)-1) & @CRLF)

[jchd]

If your aim is a real DB, then you may have to reconsider grouping entries. It all depends on the semantics of your data and the nature or structure of the queries you're going to run against it.

[Myicq]

Malkey, thanks for these methods. Man you saved me countless hours of programming this algorithm. Thanks!

For some reason, your second method fails when colno. = 3, returning only 1 char of last line.

I modified the first method a bit to have userselectable separator.

Tested so far on 3 mio. lines, converted to 120.000 x 25, in 23 secs. (slow-mo computer). Not sure if file-based attempt compared to slurping files into memory would improve performance.

Real aim is for larger datafiles, but for now this is OK.

Func _StringDisplayRE2($sStr, $iNumOfCol = 1, $iSep = ";")
If $iNumOfCol < 1 Then $iNumOfCol = 1
Local $a = StringRegExpReplace($sStr, "(.*[^v])(?:v+)", _
   "${1}" & $iSep)
Local $iNum = @extended
Local $extra = Mod($iNumOfCol - Mod($iNum, $iNumOfCol), $iNumOfCol)
If $extra <> 0 Then $a &= StringRegExpReplace( _
   StringFormat("%" & $extra & "s", _
   " "), _
   ".", _
   $iSep)
Return StringTrimRight( _
   StringRegExpReplace($a, _
   "((?:[^" & $iSep & "]*" & $iSep & "){" & ($iNumOfCol - 1) & "}(?:[^" & $iSep & "]*))+?" & $iSep, _
   "$1$2" & @LF), _
   1)
EndFunc   ;==>_StringDisplayRE2