How can i expand a line in csv file like...

ratakantez · January 3, 2020

Hi this is my first post and i am realy realy newbie around here..

And Happy new year first..

Now i have a csv file like this

GENDER    |NAME      |MONTHS    |UNIT      |PAYEMENT  |STATUS    |
----------|----------|----------|----------|----------|----------|
MR        |BROWN     |2,4,5,11  |DOLLAR    |100       |DONE      |
MRS       |BROWN     |1,4,7     |DOLLAR    |200       |DONE      |
MR        |KELLER    |10        |DOLAR     |140       |DONE      |
MRS       |KELLER    |3-6       |EURO      |145       |UNDONE    |

but i want to convert it and expand the months like this.. each line will explain the months

GENDER    |NAME      |MONTHS    |UNIT      |PAYEMENT  |STATUS    |
----------|----------|----------|----------|----------|----------|
MR        |BROWN     |2         |DOLLAR    |100       |DONE      |
MR        |BROWN     |4         |DOLLAR    |100       |DONE      |
MR        |BROWN     |5         |DOLLAR    |100       |DONE      |
MR        |BROWN     |11        |DOLLAR    |100       |DONE      |

GENDER    |NAME      |MONTHS    |UNIT      |PAYEMENT  |STATUS    |
----------|----------|----------|----------|----------|----------|
MRS       |BROWN     |1         |DOLLAR    |200       |DONE      |
MRS       |BROWN     |4         |DOLLAR    |200       |DONE      |
MRS       |BROWN     |7         |DOLLAR    |200       |DONE      |

GENDER    |NAME      |MONTHS    |UNIT      |PAYEMENT  |STATUS    |
----------|----------|----------|----------|----------|----------|
MR        |KELLER    |10        |DOLAR     |140       |DONE      |

GENDER    |NAME      |MONTHS    |UNIT      |PAYEMENT  |STATUS    |
----------|----------|----------|----------|----------|----------|
MRS       |KELLER    |3         |EURO      |145       |UNDONE    |
MRS       |KELLER    |4         |EURO      |145       |UNDONE    |
MRS       |KELLER    |5         |EURO      |145       |UNDONE    |
MRS       |KELLER    |6         |EURO      |145       |UNDONE    |

I Used

$ReadNote = ....
            $SplitTheNote = StringSplit($ReadNote, @CRLF)
            ReDim $CSVNOTE[UBound($SplitTheNote)]
            $CSVNOTE = $SplitTheNote
            For $i = 0 To UBound($CSVNOTE) - 1
            
              $GENDER =  ___????____ ;Get GENDER PartWillBeHere 
              $NAME =  ___????____ ; Get NAME PartWillBeHere 
              $MONTHS =  ___????____ ;Get  Months PartWillBeHere 
              $UNIT =  ___????____ ;Get  UNIT PartWillBeHere 
              $PAYMENT = ___????____ ;Get  PAYMENT PartWillBeHere 
              $STATUS = ___????____ ;Get  STATUS PartWillBeHere 
                
                if $GENDER = "MR" Then
                    $WriteCSVGENDER = "MR        |"
                elseif $GENDER = "MRS" Then
                    $WriteCSVGENDER = "MRS       |"
                endif
                    
                if $NAME =  Bla bla bla  THEN
                    $WriteCSVNAME = "Bla Bla Bla |" ; How can we make a Name list?
                endif
                
                if $MONTH has numbers THEN
                    StringSplit($Months, ",")
                    .....
                    .
                    .
                    .
                    .
                    .

Something like this but i failed...

How can i do it?

Yes now it seems i want all the codes from you but, i just want help how to compare each split and how to expand.. Which String functions should i use??

Thanks for your help..

Have a good day

Marc · January 3, 2020

Stringsplit is the right way to go.

My approach to convert the lines would be this one:

$header = "GENDER    |NAME      |MONTHS    |UNIT      |PAYEMENT  |STATUS    |" & @CRLF & "----------|----------|----------|----------|----------|----------|" & @CRLF
$result = ""

$result = extract("MR        |BROWN     |2,4,5,11  |DOLLAR    |100       |DONE      |")
$result = extract("MRS       |BROWN     |1,4,7     |DOLLAR    |200       |DONE      |")
ConsoleWrite($result)
Func extract($line)
    $cells = StringSplit($line, "|")
    ; ConsoleWrite($cells[3])
    $months = StringSplit($cells[3], ",")
    If @error Then
        $result &= $header & $line
    Else
        $result &= $header
        For $i = 1 To $months[0]
            $result &= $cells[1] & "|" & $cells[2] & "|" & StringFormat("%10s", StringStripWS($months[$i], 3)) & "|" & $cells[4] & "|" & $cells[5] & "|" & $cells[6] & @CRLF
        Next
    EndIf

    Return $result & @CRLF & @CRLF
EndFunc   ;==>extraxt

FrancescoDiMuro · January 3, 2020

Hi @ratakantez, and welcome to the AutoIt forums :welcome:
Study the example script below, which is quite commented, and make questions if you need them.
AutoIt is such a powerful language; all you need to know, is look at the Help file, try and study all the samples provided there and here on the Forums, and, if you feel the need, make questions here, always providing a script that can be ran, and explaining in details what you are trying to do.

Spoiler

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

Global $strFileName = "", _
       $arrFileContent, _
       $arrFileContentNew[0][7], _
       $arrMonths[1], _
       $arrLimits, _
       $strHeader = "GENDER|NAME|MONTHS|UNIT|PAYEMENT|STATUS|" & @CRLF & _
                    "----------|----------|----------|----------|----------|----------|----------", _
       $j = 0

$strFileName = @ScriptDir & "\SampleFile.csv"

; Reading the file into an array, with the pipe as separator character
_FileReadToArray($strFileName, $arrFileContent, $FRTA_COUNT, "|")
If @error Then
    ConsoleWrite("!_FileReadToArray ERR: " & @error & @CRLF)
    Exit
Else

    ; Adding the first header to the new array
    _ArrayAdd($arrFileContentNew, $strHeader)

    ; For each element of the array (starting from 2 since the first two elements are the number of rows and columns in the array,
    ; and the second one is the header of the file), scan the file
    For $i = 2 To $arrFileContent[0][0] Step 1

        ; If the MONTHS column contains a comma
        If StringInStr($arrFileContent[$i][2], ",") Then

            ; Splitting the MONTHS column with the comma
            $arrMonths = StringSplit($arrFileContent[$i][2], ",", $STR_NOCOUNT)

            ; For each month in the array, add the corresponding row from the original file content array
            For $x = 0 To UBound($arrMonths) - 1 Step 1
                _ArrayAdd($arrFileContentNew, $arrFileContent[$i][0] & "|" & _
                                              $arrFileContent[$i][1] & "|" & _
                                              $arrMonths[$x] & "|" & _
                                              $arrFileContent[$i][3] & "|" & _
                                              $arrFileContent[$i][4] & "|" & _
                                              $arrFileContent[$i][5] & "|" & _
                                              $arrFileContent[$i][6])
            Next

        ; If the MONTHS column contains a minus sign
        ElseIf StringInStr($arrFileContent[$i][2], "-") Then

            ; Extract the limits from the MONTHS column, as Min-Max
            $arrLimits = StringRegExp($arrFileContent[$i][2], "(\d+)\-(\d+)", $STR_REGEXPARRAYMATCH)

            ; Initialize the first element of the $arrMonths with the lower limit, from which it has to start counting
            $arrMonths[0] = $arrLimits[0]

            ; Build the array starting from Min to Max
            For $k = $arrLimits[0] To $arrLimits[1] - 1 Step 1
                _ArrayAdd($arrMonths, $k + 1)
                $j += 1
            Next

            ; For each month in the array, add the corresponding row from the original file content array
            For $x = 0 To UBound($arrMonths) - 1 Step 1
                _ArrayAdd($arrFileContentNew, $arrFileContent[$i][0] & "|" & _
                                              $arrFileContent[$i][1] & "|" & _
                                              $arrMonths[$x] & "|" & _
                                              $arrFileContent[$i][3] & "|" & _
                                              $arrFileContent[$i][4] & "|" & _
                                              $arrFileContent[$i][5] & "|" & _
                                              $arrFileContent[$i][6])
            Next

        ; If MONTHS is just one number
        Else
            _ArrayAdd($arrFileContentNew, $arrFileContent[$i][0] & "|" & _
                                          $arrFileContent[$i][1] & "|" & _
                                          $arrFileContent[$i][2] & "|" & _
                                          $arrFileContent[$i][3] & "|" & _
                                          $arrFileContent[$i][4] & "|" & _
                                          $arrFileContent[$i][5] & "|" & _
                                          $arrFileContent[$i][6])
        EndIf

        ; Re-declare the array, so its previous content is erased
        Global $arrMonths[1]

        ; Reset the Global counter
        $j = 0

        ; If $i is smaller than the upper bound - 1 of the array, add the header to the new array
        If $i < UBound($arrFileContent) - 1 Then _ArrayAdd($arrFileContentNew, @CRLF & $strHeader)

    Next

    ; Show the new array
    _ArrayDisplay($arrFileContentNew)

EndIf

SampleFile.csv:

Spoiler

Happy new year you too! :bye:

Malkey · January 4, 2020

Based on previous posts, there is this example which corresponds with the output in the opening post.

; https://www.autoitscript.com/forum/topic/201289-how-can-i-expand-a-line-in-csv-file-like/?do=findComment&comment=1444511

Local $result = ""

$result &= _Extract("MR        |BROWN     |2,4,5,11  |DOLLAR    |100       |DONE      |")
$result &= _Extract("MRS       |BROWN     |1,4,7     |DOLLAR    |200       |DONE      |")
$result &= _Extract("MR        |KELLER    |10        |DOLLAR    |140       |DONE      |")
$result &= _Extract("MRS       |KELLER    |3-6, 9    |DOLLAR    |145       |UNDONE    |")
ConsoleWrite($result)

Func _Extract($line)
    Local $header = "GENDER    |NAME      |MONTHS    |UNIT      |PAYEMENT  |STATUS    |" & @CRLF & _
                    "----------|----------|----------|----------|----------|----------|" & @CRLF
    Local $cells = StringSplit($line, "|")
    If StringInStr($cells[3], "-") Then $cells[3] = _Range($cells[3], 0)
    Local $months = StringSplit(StringStripWS($cells[3], 8), ",")
    Local $res = $header
    For $i = 1 To $months[0]
        ;$res &= $cells[1] & "|" & $cells[2] & "|" & StringFormat("%-10s", $months[$i] & "|" & $cells[4] & "|" & $cells[5] & "|" & $cells[6] & @CRLF
        $res &= StringFormat("%-s|%-s|%-10s|%-s|%-s|%-s|\n", $cells[1], $cells[2], $months[$i], $cells[4], $cells[5], $cells[6]) ; Same result as above line.
    Next
    Return $res & @CRLF & @CRLF
EndFunc   ;==>_Extract

; Note "-7" = 0,1,2,3,4,5,6,7
;      "7-" = 7,8,9,10,....etc. to $iMax parameter.
;      "3 - 5, 7" = 3,4,5,7
; $bRet 0 = Returns a coma separated string.
; $bRet 1 = Returns an array.
Func _Range($sRange, $bRet = 1, $iMax = "") ; default - returns an array.
    Local $a, $a1
    $a = StringSplit(StringStripWS($sRange, 8), ",", 3)
    Local $sR = ""
    For $i = 0 To UBound($a) - 1
        If StringInStr($a[$i], "-") Then
            $a1 = StringSplit($a[$i], "-", 3)
            If $a1[0] = "" Then $a1[0] = 0
            If $a1[1] = "" Then $a1[1] = $iMax
            For $j = $a1[0] To $a1[1]
                $sR &= $j & ","
            Next
        Else
            $sR &= $a[$i] & ","
        EndIf
    Next
    If $bRet Then
        Return StringSplit(StringTrimRight($sR, 1), ",", 3) ; An array
    Else
        Return StringTrimRight($sR, 1) ; A string
    EndIf
EndFunc   ;==>_Range

Edited January 4, 2020 by Malkey
Added a better visual, total StringFormat() function.

iamtheky · January 5, 2020

Taking a drunkardly direction and abusing the way the number function works :drinks:

#include<array.au3>

$sOut = ""
$sStr = "MR        |BROWN     |2,4,5,11  |DOLLAR    |100       |DONE      |"

$aStr = stringsplit($sStr , "|" , 2)

For $i = 0 to ubound(stringsplit($aStr[2], "," , 2)) - 1
   $sStr_og = $aStr[2]
   $aStr[2] = number($aStr[2])
   $sOut &= _ArrayToString($aStr , "|") & @LF
   $aStr[2] = stringtrimleft($sStr_og , stringinstr($sStr_og , ","))
Next

msgbox(0, '' , $sOut)

Edited January 5, 2020 by iamtheky

Sign In

How can i expand a line in csv file like...

Recommended Posts

ratakantez

Link to comment

Share on other sites

Marc

Link to comment

Share on other sites

FrancescoDiMuro

Link to comment

Share on other sites

Malkey

Link to comment

Share on other sites

iamtheky

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