Si289 Posted April 22, 2010 Share Posted April 22, 2010 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 Link to comment Share on other sites More sharing options...
PsaltyDS Posted April 22, 2010 Share Posted April 22, 2010 This seems to work, depending on what you mean by "adding the columns up": expandcollapse popupGlobal $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. 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 Link to comment Share on other sites More sharing options...
jchd Posted April 22, 2010 Share Posted April 22, 2010 (edited) You need a n+½ loop (à la Dijkstra). Does that fit your bill? expandcollapse popup#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 April 22, 2010 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
PsaltyDS Posted April 22, 2010 Share Posted April 22, 2010 You need a n+½ loop (à la Dijkstra).OK, fess up. How long have been WAITING to say that on the forum somewhere? 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 Link to comment Share on other sites More sharing options...
jchd Posted April 22, 2010 Share Posted April 22, 2010 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
PsaltyDS Posted April 22, 2010 Share Posted April 22, 2010 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. 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 Link to comment Share on other sites More sharing options...
jchd Posted April 22, 2010 Share Posted April 22, 2010 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 hereRegExp tutorial: enough to get startedPCRE 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) Link to comment Share on other sites More sharing options...
Si289 Posted April 23, 2010 Author Share Posted April 23, 2010 (edited) 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. expandcollapse popup#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 April 23, 2010 by Si289 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now