rhiridflaidd Posted June 26, 2011 Share Posted June 26, 2011 I've got a 3d array (20x50x3) That I want to output into 20 individual CSV's. Is there a quick way, or do I just loop through the 3d array? Thanks. Link to comment Share on other sites More sharing options...
monoscout999 Posted June 26, 2011 Share Posted June 26, 2011 The loop thing. dim $aArray[20][50][3] for $a = 0 to ubound($aArray,1) - 1 for $b = 0 to ubound($aArray,2) - 1 for $c = 0 to ubound($aArray,3) -1 $aArray[$a][$b][$c] = $a&" - "&$b&" - "&$c consolewrite($aArray[$a][$b][$c]&@CRLF) Next Next Next Link to comment Share on other sites More sharing options...
rhiridflaidd Posted June 26, 2011 Author Share Posted June 26, 2011 Looping it is- but I'm doing something wrong- the 30 files are being created fine but are all empty!! for $a = 0 to ubound($output,1) - 1 local $ofilename=$workingdir & "\output\"&$aUnique[$a]&".csv" FileOpen($ofilename, 1) for $b = 0 to ubound($output,2) - 1 for $c = 0 to ubound($output,3) -1 FileWriteLine($ofilename, $output[$a][$b][$c] & "," ) Next FileWriteLine($ofilename, @CRLF ) Next FileClose ($ofilename) Next It'll be something stupid. I 'm a really bad coder!!! Link to comment Share on other sites More sharing options...
monoscout999 Posted June 26, 2011 Share Posted June 26, 2011 (edited) first, you dont have a return for filopen, so you dont have a file handle... check it dim $aArray[20][50][3] for $a = 0 to ubound($aArray,1) - 1 for $b = 0 to ubound($aArray,2) - 1 for $c = 0 to ubound($aArray,3) -1 $aArray[$a][$b][$c] = $a&" - "&$b&" - "&$c consolewrite($aArray[$a][$b][$c]&@CRLF) Next Next Next local $ofilename= "Test.csv" $FileHndl = FileOpen($ofilename, 1) for $a = 0 to ubound($aArray,1) - 1 for $b = 0 to ubound($aArray,2) - 1 for $c = 0 to ubound($aArray,3) -1 FileWriteLine($FileHndl, $aArray[$a][$b][$c] & "," ) Next FileWriteLine($FileHndl, @CRLF ) Next Next FileClose ($FileHndl) EDITED: You are looping the fileopen function totally unnecesary, maybe you want do FileOpen before enter in the loop and closing when you exit the loop. Edited June 26, 2011 by monoscout999 Link to comment Share on other sites More sharing options...
rhiridflaidd Posted June 26, 2011 Author Share Posted June 26, 2011 (edited) Thanks. Needs to be Filewrite not filewriteline too, to work. But I'm getting some data out now which is good. I got data over 3 worksheets in 50 seperate spreadsheets, and I needed to parse it all into one! p.s. The fileopen is supposed to be looped because I'm writing into separate CSV'. Edited June 26, 2011 by rhiridflaidd Link to comment Share on other sites More sharing options...
monoscout999 Posted June 26, 2011 Share Posted June 26, 2011 (edited) p.s.The fileopen is supposed to be looped because I'm writing into separate CSV'.you are right, because i made my test script i forget your original idea... good luck Edited June 26, 2011 by monoscout999 Link to comment Share on other sites More sharing options...
czardas Posted June 26, 2011 Share Posted June 26, 2011 You can avoid the trailing comma at the end of each line by doing it slightly differently. It might be useful to you. Local $output[2][2][2], $x = 0, $workingdir = @ScriptDir, $aUnique[2] = ["file1","file2"] For $i = 0 To 1 For $j = 0 To 1 For $k = 0 To 1 $output[$i][$j][$k] = $x $x += 1 Next Next Next for $a = 0 to ubound($output,1) - 1 local $ofilename=$workingdir & "\output\"&$aUnique[$a]&".csv" FileOpen($ofilename, 1) for $b = 0 to ubound($output,2) - 1 for $c = 0 to ubound($output,3) -2 ; Avoiding the end comma FileWrite($ofilename, $output[$a][$b][$c] & "," ) Next FileWrite($ofilename, $output[$a][$b][$c] ) ; Avoiding the end comma FileWrite($ofilename, @CRLF ) Next FileClose ($ofilename) Next operator64 ArrayWorkshop Link to comment Share on other sites More sharing options...
rhiridflaidd Posted June 26, 2011 Author Share Posted June 26, 2011 (edited) Thanks all expandcollapse popup#include <File.au3> #include <Array.au3> #include <Excel.au3> #include <GuiConstants.au3> Global $workingdir Local $names[1] $workingdir = FileSelectFolder("Choose a folder.", "c:", 2, "c:\temp\test") FileChangeDir($workingdir) $FileList = _FileListToArray($workingdir, "*.xls", 1) If @error = 1 Then MsgBox(0, "", "Folder has somehow dissapeared.") Exit EndIf If @error = 4 Then MsgBox(0, "", "No XLS files found in this directory.") Exit EndIf ;_ArrayDisplay($FileList,"$FileList") Dim $worksheets If Not _FileReadToArray("worksheets.txt", $worksheets) Then MsgBox(4096, "Error", " Error reading log to Array error:" & @error) Exit EndIf ;_ArrayDisplay($worksheets,"$worksheets") ;Start Excel and open workbook $oExcel = ObjCreate("Excel.Application") $oExcel.Visible = 0 Local $iMax = UBound($FileList) Local $loop Global $main[1000][4][40][50] For $go = 1 To $iMax-1 $oExcel.WorkBooks.Open($workingdir & "\" & $FileList[$go]) For $n = 1 To 3 Step 1 $oExcel.Sheets($worksheets[$n] ).Select() Local $sheetname = $worksheets[$n] ;MsgBox (0,"","worksheet is " & $worksheets[1]) ; _ExcelSheetActivate($worksheets[1], "Sheet1") ; If @error Then ; MsgBox(0,"ERROR","Failed to change worksheet " & $oExcel & @LF) ; EndIf Dim $database $database = _ExcelReadSheetToArray($oExcel) If @error Then MsgBox(0, "ERROR", "Failed to read Excel sheet to array" & @CRLF & "ERROR: " & @error & @CRLF & "Extended: " & @extended) EndIf ;_ArrayDisplay($database, "$Database") $dataname = $database[1][1] & $database[1][3] Local $temp[40][50] Local $i, $j Local $write = 0 Local $hosp = 10000 For $i = 0 To UBound($database, 1) - 1 For $j = 0 To UBound($database, 2) - 1 ; ConsoleWrite("$database[" & $i & "][" & $j & "]:=" & $database[$i][$j] & @LF) If $write = 0 Then If $database[$i][$j] = "Specialty" Then Local $initx = $i Local $inity = $j $write = 1 EndIf EndIf If $database[$i][$j] = "All hospitals*" Then $hosp = $j Local $gap = $hosp - $inity - 1 EndIf If $write = 1 Then If $i >= $initx Then If $j = $inity Then $main[$go][$n][$i - $initx][$j - $inity + 1] = $database[$i][$j] $temp[$i - $initx][$j - $inity + 1] = $database[$i][$j] _ArrayAdd($names, $database[$i][$j]) consoleWrite("input" & $go &"-" &$n&"-"&$i-$initx&"-"&$j - $inity + 1&"-"&$database[$i][$j] & @LF) EndIf If $j = $hosp Then $main[$go][$n][$i - $initx][$j - $inity + 1 - $gap] = $database[$i][$j] $temp[$i - $initx][$j - $inity + 1 - $gap] = $database[$i][$j] consoleWrite("input" & $go &"-" &$n&"-"&$i-$initx&"-"&$j - $inity + 1 - $gap&"-"&$database[$i][$j] & @LF) EndIf EndIf EndIf Next Next $main[$go][$n][0][2] = $worksheets[$n] $temp[0][2] = $worksheets[$n] Local $practicename = StringTrimRight($FileList[$go], 4) $main[$go][$n][0][1] = $practicename $temp[0][1] = $practicename ;_ArrayDisplay($temp, "$temp") ;_ArrayDisplay($names, "$names") ;ConsoleWrite("$database[" & 1 & "][" & 1 & "]:=" & $main[$go][$n][1][1] & @LF) Next ;oExcel.WorkBooks.Close($workingdir & "\" & $FileList[$go]) Next $oExcel.Quit $aUnique = _ArrayUnique($names) _ArrayDisplay($aUnique, "$names") local $output[$aUnique[0]+1][$imax+2][5] local $iIndex local $sSearch local $tema[40][50] For $go = 1 To $iMax-1 Step 1 For $n = 1 To 3 Step 1 ConsoleWrite("no of elements" & UBound($database, 2)& @LF) For $i = 1 To UBound($main, 3) - 1 $sSearch =$main[$go][$n][$i][1] ConsoleWrite("output" & $go &"-" &$n&"-"&$i&"-"&$main[$go][$n][$i][1] & @LF) $iIndex = _ArraySearch($aUnique, $sSearch, 0, 0, 0, 1) If $sSearch = "All Specialties" Then ExitLoop Else ;If @error Then ; MsgBox(0, "Not Found", '"' & $sSearch & '" was not found on column ' & "rubish"& '.') ;Else ; MsgBox(0, "Found", '"' & $sSearch & '" was found in the array at position ' & $iIndex & ' on column ' & "rubish"& '.') ;tema is just a bugfixing array ;EndIf $output[$iIndex][$go][1]=$main[$go][$n][0][1] ;set surgery name at col 1 $tema[$go][1]=$main[$go][$n][0][1] $output[$iIndex][0][1]=$main[$go][$n][$i][1] $tema[0][1]=$main[$go][$n][$i][1] ;$output[$iIndex][$go][1]=$main[$go][$n][1][1] ;$tema[$go][1]=$main[$go][$n][1][1] $output[$iIndex][$go][$n+1]=$main[$go][$n][$i][2] $tema[$go][$n+1]=$main[$go][$n][$i][2] $output[$iIndex][0][$n+1]=$main[$go][$n][0][2] ;set column labels $tema[0][$n+1]=$main[$go][$n][0][2] endif Next ;_ArrayDisplay ($tema,"is output") Next next ; ;create a directory and output the files as an XLS DirCreate($workingdir & "\output") for $a = 4 to ubound($output,1) - 1 local $ofilename=$workingdir & "\output\"&$aUnique[$a]&".csv" $FileHndl = FileOpen($ofilename, 1) for $b = 0 to ubound($output,2) - 1 for $c = 0 to ubound($output,3) -1 FileWrite($FileHndl, $output[$a][$b][$c] & "," ) ConsoleWrite($output[$a][$b][$c] & "," ) Next FileWriteLine($FileHndl, @CRLF ) ConsoleWrite( @CRLF ) Next FileClose ($FileHndl) Next ;local $printout [$imax+2][5] ;for $go = 3 to $aUnique; ;for $n= 0 to $imax ; for $i= 0 to 4 ; $printout[$n][$i]=$output[$go][$n][$i] ; Next ;Next ;_FileWriteFromArray($workingdir & "\output\"&$aUnique[$go]&".csv", $printout) ;next Well the data is now successfully parsed. It came in the format Spreadsheet for institution Worksheet 1 Activity in field A 10 Activity in Field B 20 And so on for 30 other items - and some would be missing and not necessarily in the same order. Worksheet 2 Other Activity in field A 20 OtherActivity in Field B 30 Worksheet 3 Other Activity in field A 30 OtherActivity in Field B 40 And so on for 50 spreadsheets. I wanted to compare how each institution compared in each field. So I read all the data into one 4 dimensional array. Then organised it into a 3d array Then printed it out from there. I am not a coder as you can guess from the dire code. It's an utter bodge, but the code might be useful to somebody messing with excel at some time so might as well show it. Edited June 26, 2011 by rhiridflaidd Link to comment Share on other sites More sharing options...
czardas Posted June 26, 2011 Share Posted June 26, 2011 Glad you got it working. operator64 ArrayWorkshop 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