Kage Posted February 10, 2011 Share Posted February 10, 2011 (edited) Hello, i have found a somewhere else on the forum about putting a cvs in a 2d array.the code and all works partly.i just can't get the hang of this array logic so i was hoping you guys could help me abit with it.The thing is, i made a csv export from exchangepowershell with in it saying what mailbox holds howmuch Bytes.when i load it in the script i found it for somereason put in the first row first colum the amount of lines, by doing so it kicks of the second columm of data i want to read into it.o and if possible i would like to get somehow the first line of the CSV as colum nameswell enough explaining here is the code and a csv:#include <array.au3> Global $sString, $aLineArray, $aSplit, $nCount $sString = FileRead("CSV.csv") ;$sString = "bob,Bob" & @CRLF & "tim,Tim" & @CRLF & "neil*diamond,Neil Diamond" ;Create an array of each line $aLineArray = StringSplit(StringStripCR($sString), @LF) ;Create a 2 dimensional array Global $avArray[UBound($aLineArray)][2] $avArray[0][0] = UBound($aLineArray) -1 ;Parse our array and store in 2D array For $iCC = 1 To $avArray[0][0] ;Split each line of file with delimeter If StringInStr($aLineArray[$iCC], ",") Then $nCount += 1 $aSplit = StringSplit($aLineArray[$iCC], '"') $avArray[$nCount][0] = $aSplit[1] $avArray[$nCount][1] = $aSplit[2] $avArray[$nCount][1] = $aSplit[2] EndIf Next ;If a line was found with no delimeter, we Redim If $nCount <> $avArray[0][0] Then ReDim $avArray[$nCount + 1][2] $avArray[0][0] = $nCount EndIf ;[number][0] = First part before delimeter ;[number][1] = Second part after delimeter _ArrayDisplay($avArray, "Here is your 2 Dimensional array") Edited February 11, 2011 by Kage Link to comment Share on other sites More sharing options...
kylomas Posted February 10, 2011 Share Posted February 10, 2011 (edited) Kage, See the comments in the code below. This should get you started. expandcollapse popup#include <array.au3> Global $sString, $aLineArray, $aSplit, $nCount $sString = FileRead("C:\Documents and Settings\Dad\Desktop\CSV.csv") ;$sString = "bob,Bob" & @CRLF & "tim,Tim" & @CRLF & "neil*diamond,Neil Diamond" ;Create an array of each line $aLineArray = StringSplit(StringStripCR($sString), @LF) _arraydisplay($alinearray) ;Create a 2 dimensional array Global $avArray[UBound($aLineArray)][2] $avArray[0][0] = UBound($aLineArray) -1 ;Parse our array and store in 2D array For $iCC = 0 To ubound($avArray,1) - 1 ; <======= chg to 0 and ubound($avarray,1)-1 ;Split each line of file with delimeter If StringInStr($aLineArray[$iCC], ",") Then if $icc < ubound($avarray,1) then redim $avarray[ubound($avarray,1)*2][2] ; < ==== if at end of array then double it $aSplit = StringSplit($aLineArray[$iCC], ',') ; <======= changed split char to ','? $avArray[$icc+1][0] = $aSplit[1] $avArray[$icc+1][1] = $aSplit[2] ;$avArray[$nCount][1] = $aSplit[2] ; <======= not sure why you are doing this EndIf Next ;========================================================= ; always check for end of array BEFORE adding to the array ; see above comments ;========================================================= ;If a line was found with no delimeter, we Redim ;~ If $nCount <> $avArray[0][0] Then ;~ ReDim $avArray[$nCount + 1][2] ;~ $avArray[0][0] = $nCount ;~ EndIf ;[number][0] = First part before delimeter ;[number][1] = Second part after delimeter _ArrayDisplay($avArray, "Here is your 2 Dimensional array") kylomas Edit: addition - this code needs to be cleaned up...all I did was correct several errors...you may want to review the autoit doc on array management. Edited February 10, 2011 by kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
Kage Posted February 10, 2011 Author Share Posted February 10, 2011 (edited) Ah it seems like the attachment didnt come with the post.i attached the csv, atleast i thought i did.here it is anyway then"DisplayName","TotalItemSize""Lourens van Dijk","29.92 MB (31,370,477 bytes)""Reinier van Dijk","535.1 KB (547,900 bytes)""spam","59.03 KB (60,450 bytes)""webmaster","13.03 KB (13,346 bytes)""Administrator","4.077 KB (4,175 bytes)""besadmin","1.039 KB (1,064 bytes)""Microsoft Exchange","952 B (952 bytes)""SystemMailbox{b1aa72e7-402e-4b60-a92b-c88f6a52b6ab}","318 B (318 bytes)""Quotes","134 B (134 bytes)"as you see in this example, the export from the exchange shell also used ,, in the (13,346 bytes) partsthats one of the reasons why i changed ',' into '"'but i just noticed that when i do that, it messes up the layouthere are two screenshots. Edited February 10, 2011 by Kage Link to comment Share on other sites More sharing options...
kylomas Posted February 10, 2011 Share Posted February 10, 2011 Kage, The csv file notwithstanding there are/were other, more fundamental problems with this code. Most notably: - when and how you resize your array - how you split each record of the csv I believe that you have enough to go on. Keep in mind that the 2ND parm of ubound for a multi-dimenisonal array specifies the dimension that you are measuring. kylomas Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
UEZ Posted February 10, 2011 Share Posted February 10, 2011 (edited) As kylomas said, your code is not working properly and you have a delimiter problem!Try this csv (I changed the delimiter to ';'):"DisplayName";"TotalItemSize" "Lourens van Dijk";"29.92 MB (31,370,477 bytes)" "Reinier van Dijk";"535.1 KB (547,900 bytes)" "spam";"59.03 KB (60,450 bytes)" "webmaster";"13.03 KB (13,346 bytes)" "Administrator";"4.077 KB (4,175 bytes)" "besadmin";"1.039 KB (1,064 bytes)" "Microsoft Exchange";"952 B (952 bytes)" "SystemMailbox{b1aa72e7-402e-4b60-a92b-c88f6a52b6ab}";"318 B (318 bytes)" "Quotes";"134 B (134 bytes)"This code was written some long time ago but you can try it:expandcollapse popup#include <Array.au3> $file = FileOpenDialog("Select CSV", "", "File (*csv)") If @error Then Exit MsgBox(16, "Error", "No file selected", 10) $aCSV = CSV_to_2D_Array($file, ";") _ArrayDisplay($aCSV, "Array created from CSV file") Func CSV_to_2D_Array($file, $delimiter = ";") ;coded by UEZ 2009 (beta) If Not FileExists($file) Then Return 0 Local $line, $aTemp1, $aTemp2, $i, $j Local $hFile = FileRead($file) $aTemp1 = StringSplit($hFile, Chr(13), 2) If @error Then Return SetError(1, 0, 0) Local $columns = 0 Local $rows = 0 For $i = 0 To UBound($aTemp1) - 1 ;find 1st column and row indicies If $aTemp1[$i] <> Chr(13) Then $aTemp2 = StringSplit($aTemp1[$i], $delimiter, 2) If @error Then Return SetError(2, 0, 0) $columns += 1 If UBound($aTemp2) > $rows Then $rows = UBound($aTemp2) EndIf Next ;~ ConsoleWrite($columns & "," & $rows & @CRLF) Local $aCSV[$columns][$rows] ;create the array For $i = 0 To UBound($aTemp1) - 1 If $aTemp1[$i] <> Chr(13) Then $aTemp2 = StringSplit($aTemp1[$i], $delimiter, 2) If @error Then Return SetError(3, 0, 0) For $j = 0 To UBound($aTemp2) - 1 $aCSV[$i][$j] = $aTemp2[$j] Next EndIf Next If IsArray($aCSV) Then Return SetError(0, 0, $aCSV) Return SetError(4, 0, 0) EndFuncBr,UEZ Edited February 11, 2011 by UEZ Please don't send me any personal message and ask for support! I will not reply! Selection of finest graphical examples at Codepen.io The own fart smells best! ✌Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!¯\_(ツ)_/¯ ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ Link to comment Share on other sites More sharing options...
Kage Posted February 11, 2011 Author Share Posted February 11, 2011 UEZ im getting an error when i run your script. C:\Users\Administrator\Desktop\arraytest2.au3 (25) : ==> Array variable subscript badly formatted.: Local $aCSV[$columns][$rows] Local $aCSV[^ ERROR >Exit code: 1 Time: 5.122 Link to comment Share on other sites More sharing options...
UEZ Posted February 11, 2011 Share Posted February 11, 2011 Probably you have chosen a wrong delimiter for the csv file!When you save the text example in the code box and run the script, which I updated to handle errors better, it should work properly!When you have a different delimiter than ';' you must change the line appropriately.$aCSV = CSV_to_2D_Array($file, ";")Br,UEZ Please don't send me any personal message and ask for support! I will not reply! Selection of finest graphical examples at Codepen.io The own fart smells best! ✌Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!¯\_(ツ)_/¯ ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ Link to comment Share on other sites More sharing options...
Kage Posted February 11, 2011 Author Share Posted February 11, 2011 (edited) Thanks for the help all, somehow managed to do this.after trying alot, and having my hands in mt hair for quite some time i got it.i dont completely unstand arrays yet, but i have come pretty far i guess.this is my code now.expandcollapse popup#include <array.au3> #include <file.au3> #RequireAdmin Global $sString, $aLineArray, $aSplit, $nCount $mailboxservername = InputBox("Servername ?", "What is the servername ?", "", "", 200, 130) if Not FileExists( @appdatadir & "\Mailbox") Then DirCreate( @appdatadir & "\Mailbox") If FileExists("C:\Program Files\Microsoft\Exchange Server\V14") Then RunWait(@ComSpec & ' /c powershell -psconsolefile "C:\Program Files\Microsoft\Exchange Server\v14\Bin\exshell.psc1" -command "& {Get-MailboxStatistics -Server '&$mailboxservername&' | Sort-Object -Property TotalItemSize -Descending | Select-Object DisplayName,TotalItemSize,StorageGroupName | export-csv '&@AppDataDir&'\Mailbox\csv.csv -notype}"',"" , @SW_HIDE) Else RunWait(@ComSpec & ' /c powershell -psconsolefile "C:\Program Files\Microsoft\Exchange Server\Bin\exshell.psc1" -command "& {Get-MailboxStatistics -Server '&$mailboxservername&' | Sort-Object -Property TotalItemSize -Descending | Select-Object DisplayName,TotalItemSize,StorageGroupName | export-csv '&@AppDataDir&'\Mailbox\csv.csv -notype}"',"" , @SW_HIDE) EndIf $sString = FileRead(@AppDataDir & "\Mailbox\CSV.csv") $new = StringRegExpReplace($sstring, '","', '";"') $new2 = StringRegExpReplace($new, 'B";"', '";"') $new3 = StringRegExpReplace($new2, '",', ';') $new4 = StringRegExpReplace($new3, '"', '') FileWrite(@appdatadir &"\Mailbox\newcsv.csv", $new4) $newstring = FileRead(@appdatadir & "\Mailbox\newcsv.csv") $aLineArray = StringSplit(StringStripCR($newString), @LF) Global $avArray[UBound($aLineArray)][4] $avArray[0][0] = UBound($aLineArray) -1 For $iCC = 1 To $avArray[0][0] If StringInStr($aLineArray[$iCC], ";") Then $nCount += 1 $aSplit = StringSplit($aLineArray[$iCC], ';') $avArray[$nCount][0] = $aSplit[1] $avArray[$nCount][1] = $aSplit[2] $avArray[$nCount][2] = $aSplit[3] EndIf Next If $nCount <> $avArray[0][0] Then ReDim $avArray[$nCount + 1][3] ;~ $avArray[0][0] = $nCount EndIf _ArrayDisplay($avArray, "Mailbox Sizes:") FileDelete(@appdatadir & "\Mailbox\csv.csv") FileDelete(@appdatadir & "\Mailbox\newcsv.csv")if you run this (need to be run as 64bit on a exchange server (2007 and 2010 works, 2003 im not sure.)it will show you something like thispretty usefull when managing big corp'sAdded the .exe here:script is above for the curious one's Edited February 11, 2011 by Kage 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