[solved]need some help with CSV + Array

[Kage]

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 names

well 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

[kylomas]

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

[Kage]

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) parts

thats one of the reasons why i changed ',' into '"'

but i just noticed that when i do that, it messes up the layout

here are two screenshots.

Edited February 10, 2011 by Kage

[kylomas]

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

[UEZ]

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)
EndFunc

Br,

UEZ

Edited February 11, 2011 by UEZ

[Kage]

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

[UEZ]

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

[Kage]

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 this

pretty usefull when managing big corp's

Added the .exe here:

script is above for the curious one's

Edited February 11, 2011 by Kage