Jump to content
Sign in to follow this  
Si289

Array help

Recommended Posts

Si289

Hi all,

I am trying to loop through an array (See attached) and add columns up depending on the first two numbers in Column 2.

So for example

If the first two digits in column 2 match the previous row(s) then it adds the columns up and outputs to a file.

However, my brain can not seem to get around where to start lol.

I started off with the following code:

For $d = 1 to $cArray[0][0]
        If $Code == "" Then
            $Code = $cArray[$d][2]
        Else
            $CodeLen = StringLen($cArray[$d][2])
            If $CodeLen == 4 Then
                $Code = "0"&$cArray[$d][2]
            Else
                $Code = $cArray[$d][2]
            EndIf
            $DeptCode = StringLeft($Code, 2)

However I don't know how to go about adding the columns up of $DeptCodes that match?

Any help would by much appreciated, if I can get this working it will make my life easier lol.

cArray.txt

Share this post


Link to post
Share on other sites
PsaltyDS

This seems to work, depending on what you mean by "adding the columns up":

Global $cArray[20][7] = [[19,6], _
    ["","Dept - Name1",07142,6,0,244,47], _
    ["","Dept - Name2",11291,0,0,12,0], _
    ["","Dept - Name3",11406,0,0,68,62], _
    ["","Dept - Name4",11406,0,0,124,0], _
    ["","Dept - Name5",16546,0,0,4,14], _
    ["","Dept - Name6",16913,0,0,391,0], _
    ["","Dept - Name7",16917,0,0,71,0], _
    ["","Dept - Name8",17458,0,0,30,0], _
    ["","Dept - Name9",18411,0,0,52,0], _
    ["","Dept - Name10",18614,0,0,25,9], _
    ["","Dept - Name11",18614,0,0,91,0], _
    ["","Dept - Name12",18978,0,0,57,0], _
    ["","Dept - Name13",19795,0,0,41,0], _
    ["","Dept - Name14",20215,2,3,2,4], _
    ["","Dept - Name15",21028,0,0,6,0], _
    ["","Dept - Name16",21756,2,0,7,0], _
    ["","Dept - Name17",22597,0,0,2,0], _
    ["","Dept - Name18",22736,0,0,104,0], _
    ["","Dept - Name19",26470,0,0,0,3]]

Global $iTotal = 0
Global $sCode = "", $sPrevious = ""

For $d = 1 To $cArray[0][0]
    $sCode = StringLeft($cArray[$d][2], StringLen($cArray[$d][2]) - 3)
    If $sCode = $sPrevious Then
        For $c = 3 To 6
            $iTotal += $cArray[$d][$c]
        Next
        ConsoleWrite($d & ":  Matched, " & $sCode & " = " & $sPrevious & ", $iTotal = " & $iTotal & @LF)
    Else
        $iTotal = 0
        For $c = 3 To 6
            $iTotal += $cArray[$d][$c]
        Next
        ConsoleWrite($d & ":  Not matched, " & $sCode & " <> " & $sPrevious & ", $iTotal = " & $iTotal & @LF)
    EndIf
    $sPrevious = $sCode
Next

Should be easy to tweak to whatever you really wanted if that's not it.

:idea:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
jchd

You need a n+½ loop (à la Dijkstra). Does that fit your bill?

#include <Array.au3>

Local $hdl = FileOpen(@ScriptDir & "\cArray.txt")
; read headers
Local $line = FileReadLine($hdl)
; Lets see how many columns we have per line
Local $array = StringSplit($line, ',')
Local $nbcols = $array[0]
; read (and discard) second line as we don't seem to need it
FileReadLine($hdl)
; in each row, we don't sup up the first 4 columns
Local const $NoSumCols = 4
Local $sum[$nbcols]
$sum[3] = '**'                  ; make sure we don't match on first line
Local $fields
Local $output
$line = FileReadLine($hdl)
$fields = StringSplit($line, ',', 2)
While 1
    While StringLeft($fields[3], 2) = $sum[3]
        ; same deptcode, sum up remaining columns
        For $i = $NoSumCols To $nbcols - 1
            $sum[$i] += $fields[$i]
        Next
        $line = FileReadLine($hdl)
        If @error Then ExitLoop 2
        $fields = StringSplit($line, ',', 2)
    WEnd
    If $sum[3] <> '**' Then
;~      $sum[3] &= '***'
        $output = _ArrayToString($sum, @TAB, 3)
        ConsoleWrite("Department code " & $output & @LF)    ; you may need to format differently and write to file instead
    EndIf
    Dim $sum[$nbcols]
    $sum[3] = StringLeft($fields[3], 2)
WEnd
FileClose($hdl)

N.B. not much time to put error checking here, you're on your own!

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
PsaltyDS

You need a n+½ loop (à la Dijkstra).

OK, fess up. How long have been WAITING to say that on the forum somewhere?

:idea:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
jchd

But it's true my lovely Penguin!

You may read ahead the first element with or without consuming, you have to handle the read in two separate places or drag in a ugly logic more likely to confuse user/maintainer at some point.

It's not so if you use a dedicated language specialy tailored to manage N-levels of such splits (rupture is the correct word in French, but I'm not sure for english). RPG II (for Report Program Generator) was of this type (IBM/360, 370 and the like). It was a language originally created to deal with files as decks of punched cards, which you just can't read twice. So the language (called GAP II on AS/400) offered report declaratives setup to define splits like subtotals and the like at N-levels. It was very natural to code/read with no parasitic logic glue and you could programme incredibly complex reports with real ease.

I bet that if today's programmers had to code reports of the same complexity, say 5 or 6 levels such free-running totals with overlapping conditions, with some today's all-purpose language, most of them would take "some" time to get it correct...


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
PsaltyDS

But arrays, and computer storage in general, are random access now. To "define splits like subtotals" we simply declare a variable (or as many as required) and update while processing the data in any sequence we choose.

BTW, the format of the OP's data looks like it might be the result of _ExcelReadSheetToArray(). If so, these totals might have been generated with a few judicious formulas and/or macros right in Excel.

:idea:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
jchd

The n+½ is a rumor that Dijkstra hesitated to propose such a constuct in addition to the other loops in Pascal, but finaly decided it wasn't worth it. He nonetheless categorized it with the n+½ tag in his seminal papers/books.

Most probably, but that requires Office installed. And if ever there are more than one series like this in the file, Excel piloting is going to get more ... interesting.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Si289

Thank you for the help guys. Was able to modify the script you gave me to make it do what I want to.

I am new to this coding, so my code is not as efficient as it should be or as tidy as it should be lol.

#Include <Excel.au3>
#Include <Array.au3>

$var = FileOpenDialog("Please select the DEPT SMALL LARGE COUNT csv file for parsing.", "C:\WorkingDir", "Comma Seperated Values (*.csv)", 5)
If @error Then
    MsgBox(4096,"","No File(s) chosen")
    Exit
EndIf

$varArray = StringSplit( $var, "|")

If $varArray[0] = "1" Then
    ;MsgBox(0,"Executing First If","Executing the first if branch")
    $Today = @YEAR & @MON  & @MDAY

    Local $oExcel = _ExcelBookOpen($varArray[1], 0)
    $aArray = _ExcelReadSheetToArray($oExcel)

    _ExcelBookClose($oExcel, 1, 0)
    ;_ArrayDisplay($aArray, "Array using Default Parameters")


    $UserCodeFile = FileOpen("C:\PrintersUsage-Code-" & $Today &".csv", 2)
    FileWriteLine($UserCodeFile, "Name,Code,Colour A4,Colour A3,Black A4,Black A3")

    For $i = 2 to $aArray[0][0] - 2
    
        ;Work out the Colour cost PER User
        
        $ColourA4 = $aArray[$i][4] + $aArray[$i][6] + $aArray[$i][12] + $aArray[$i][14] + $aArray[$i][20] + $aArray[$i][22]
        $ColourA3 = $aArray[$i][5] + $aArray[$i][7] + $aArray[$i][13] + $aArray[$i][15] + $aArray[$i][21] + $aArray[$i][23]
        $BlackA4 = $aArray[$i][8] + $aArray[$i][16] + $aArray[$i][24]
        $BlackA3 = $aArray[$i][9] + $aArray[$i][17] + $aArray[$i][25]

        if ($ColourA3 <> "0" OR $ColourA4 <> "0" OR $BlackA3 <> "0" OR $BlackA4 <> "0") Then
            ;ConsoleWrite( "Name: "& $aArray[$i][2] & " Code: "& $aArray[$i][3] & " Colour A4: " & $ColourA4 &" Colour A3: " & $ColourA3 &" Black A4: " & $BlackA4 &" Black A3: " & $BlackA3 & @CRLF )
            FileWriteLine($UserCodeFile, $aArray[$i][2] & ","& $aArray[$i][3] & "," & $ColourA4 &"," & $ColourA3 &"," & $BlackA4 &"," & $BlackA3)

        EndIf
    Next
    
FileClose($UserCodeFile)
MsgBox(48, "Success", "User Code File has been created successfully!")

Else
    ;MsgBox(0,"Executing 2nd If","Executing the 2nd if branch")
    $Today = @YEAR & @MON  & @MDAY
    $UserCodeFile = FileOpen("C:\PrintersUsage-Code-" & $Today &".csv", 1)
    FileWriteLine($UserCodeFile, "Name,Code,Colour A4,Colour A3,Black A4,Black A3")
    
    For $b = 2 to $varArray[0]
        
        ;_ArrayDisplay($varArray)
    

        Local $oExcel = _ExcelBookOpen($varArray[1]&"\"&$varArray[$b], 0)
        $aArray = _ExcelReadSheetToArray($oExcel)
        _ExcelBookClose($oExcel, 1, 0)
        
        ;_ArrayDisplay($aArray, "Array using Default Parameters")
        
        FileWriteLine($UserCodeFile, $varArray[$b] &",,,,,,")


        For $i = 2 to $aArray[0][0] - 2
    
            ;Work out the Colour cost PER User

            $ColourA4 = $aArray[$i][4] + $aArray[$i][6] + $aArray[$i][12] + $aArray[$i][14] + $aArray[$i][20] + $aArray[$i][22]
            $ColourA3 = $aArray[$i][5] + $aArray[$i][7] + $aArray[$i][13] + $aArray[$i][15] + $aArray[$i][21] + $aArray[$i][23]
            $BlackA4 = $aArray[$i][8] + $aArray[$i][16] + $aArray[$i][24]
            $BlackA3 = $aArray[$i][9] + $aArray[$i][17] + $aArray[$i][25]
            
            If ($ColourA3 <> "0" OR $ColourA4 <> "0" OR $BlackA3 <> "0" OR $BlackA4 <> "0") Then
                ;ConsoleWrite( "Name: "& $aArray[$i][2] & " Code: "& $aArray[$i][3] & " Colour A4: " & $ColourA4 &" Colour A3: " & $ColourA3 &" Black A4: " & $BlackA4 &" Black A3: " & $BlackA3 & @CRLF )
                FileWriteLine($UserCodeFile, $aArray[$i][2] & ","& $aArray[$i][3] & "," & $ColourA4 &"," & $ColourA3 &"," & $BlackA4 &"," & $BlackA3)
            EndIf
        Next
        FileWriteLine($UserCodeFile, "-,-,-,-,-,-")
    Next
    FileClose($UserCodeFile)
MsgBox(48, "Success", "User Code File has been created successfully!")
    
    
$DepartmentCodeFile = FileOpen("C:\PrintersUsage-Dept-" & $Today &".csv", 1)
;_ArrayDisplay($varArray)
For $c = 2 to $varArray[0]
        
    Local $oExcel2 = _ExcelBookOpen($varArray[1]&"\"&$varArray[$c], 0)
    $bArray = _ExcelReadSheetToArray($oExcel2)
    _ExcelBookClose($oExcel2, 1, 0)
    ;_ArrayDisplay($bArray)
    For $i = 2 to $bArray[0][0] - 2
        $ColourA4 = $bArray[$i][4] + $bArray[$i][6] + $bArray[$i][12] + $bArray[$i][14] + $bArray[$i][20] + $bArray[$i][22]
        $ColourA3 = $bArray[$i][5] + $bArray[$i][7] + $bArray[$i][13] + $bArray[$i][15] + $bArray[$i][21] + $bArray[$i][23]
        $BlackA4 = $bArray[$i][8] + $bArray[$i][16] + $bArray[$i][24]
        $BlackA3 = $bArray[$i][9] + $bArray[$i][17] + $bArray[$i][25]
    
        If ($ColourA3 <> "0" OR $ColourA4 <> "0" OR $BlackA3 <> "0" OR $BlackA4 <> "0") Then
            ;ConsoleWrite( "Name: "& $aArray[$i][2] & " Code: "& $aArray[$i][3] & " Colour A4: " & $ColourA4 &" Colour A3: " & $ColourA3 &" Black A4: " & $BlackA4 &" Black A3: " & $BlackA3 & @CRLF )
            FileWriteLine($DepartmentCodeFile, $bArray[$i][2] & ","& $bArray[$i][3] & "," & $ColourA4 &"," & $ColourA3 &"," & $BlackA4 &"," & $BlackA3)
        EndIf
    Next
Next
FileClose($DepartmentCodeFile)

Local $oExcel3 = _ExcelBookOpen("C:\PrintersUsage-Dept-" & $Today &".csv", 0)
$cArray = _ExcelReadSheetToArray($oExcel3)
_ExcelBookClose($oExcel3, 1, 0) 

;_ArrayDisplay($cArray)
_ArraySort($cArray,0,0,0,2)
;_ArrayDisplay($cArray)
    ;Start to parse the Department code.
    ;First need to put the first line into a variable
Local $oExcel4 = _ExcelBookOpen("C:\PrintersUsage-Dept-" & $Today &".csv", 0)
_ExcelWriteSheetFromArray($oExcel4, $cArray)
_ExcelBookClose($oExcel4, 1, 0)

Local $hdl = FileOpen("\\strider\tech\Printers\PrintersUsage-Dept-"&$Today&".csv", 0)
; read headers
Local $line = FileReadLine($hdl,1)
; Lets see how many columns we have per line

Local $array = StringSplit($line, ',')
Local $nbcols = $array[0]
;_ArrayDisplay($array)
; read (and discard) second line as we don't seem to need it
;FileReadLine($hdl)
; in each row, we don't sup up the first 4 columns
Local const $NoSumCols = 2
Local $sum[$nbcols]
$sum[1] = '**'                  ; make sure we don't match on first line
;_ArrayDisplay($sum)
Local $fields
Local $output
$line = FileReadLine($hdl, 1)
$fields = StringSplit($line, ',', 2)
        $CodeLen = StringLen($fields[1])
        If $CodeLen == 4 Then
            $fields[1] = "0"&$fields[1]
        EndIf
;_ArrayDisplay($fields)
;Create the OutputFile
Local $hdl2 = FileOpen("C:\PrintersUsage-DeptTotal-"&$Today&".csv", 2)
FileWriteLine($hdl2, "Department Code,Colour A4,Colour A3,Black A4,Black A3")
While 1
    While StringLeft($fields[1], 2) = $sum[1]
        ; same deptcode, sum up remaining columns
        For $i = $NoSumCols To $nbcols - 1
            $sum[$i] += $fields[$i]
        Next
        $line = FileReadLine($hdl)
        If @error Then ExitLoop 2
        $fields = StringSplit($line, ',', 2)
        $CodeLen = StringLen($fields[1])
        If $CodeLen == 4 Then
            $fields[1] = "0"&$fields[1]
        EndIf
        
    WEnd
    If $sum[1] <> '**' Then
        
;~      $sum[3] &= '***'
        $output = _ArrayToString($sum, "," , 1)
        FileWriteLine($hdl2, $output)
        ;ConsoleWrite("Department code " & $output & @LF)    ; you may need to format differently and write to file instead
    EndIf
    Dim $sum[$nbcols]
    $sum[1] = StringLeft($fields[1], 2)
    ;_ArrayDisplay($sum)
WEnd
FileClose($hdl2)
FileClose($hdl)
FileDelete("C:\PrintersUsage-Dept-"&$Today&".csv")
MsgBox(48, "Success", "Department File has been created successfully!")

EndIf

However it works which is the main thing lol.

Edited by Si289

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.