#200153 - 2010-10-04 06:17 PM
ReadExcel2 - identifying unique values in column, and more...
|
Pax
Getting the hang of it
Registered: 2006-12-01
Posts: 51
|
OK Oracles and others,
There is a script I am using which was developed with help in http://www.kixtart.org/forums/ubbthreads.php?ubb=showflat&Number=199647 which I want to improve by fine tuning the destination.
I want to be able to copy to selective destinations. Given that the destination in this script lists all offices, which all have different brands.
Firstly, I want the script running to supply a "brand" as a list arguement, but to validate that arguement against the "brands" listed in the excel spreadsheet. I am guessing to read through the column in Excel with the brands and add the brand to an array if it doesn't exist, then run a match against the array. Is there an easier way?
Secondly, I want to copy the source to the destination only when the "brand" of the next directory in the while/loop is equal to the brand specified when the script is initiated. I am guessing that something like If $Brand = $brandshop[0,2] and $destdirs = $brandshop[0,1]
Pax
|
|
Top
|
|
|
|
|
#200159 - 2010-10-05 01:28 PM
Re: ReadExcel2 - identifying unique values in column, and more...
[Re: Pax]
|
Pax
Getting the hang of it
Registered: 2006-12-01
Posts: 51
|
Hmm... have I done somthing wrong with this code below?
call @ScriptDir + "\readexcel2.udf"
$Brandshop = readexcel2(@Scriptdir + '\pccs.xls')
If @ERROR = "2" ; pcc spreadsheet is required - die if not provided
? 'ERROR OCCURED: @serror (@error)' @CRLF @CRLF
Quit 1
EndIf
? 'Current error status is ' + @SERROR + " - " + @ERROR
? 'Rows in spreadsheet are ' + ubound($brandshop)
For $counter = 0 to ubound($brandshop)
? "PCC: " $brandshop[$counter,0]
? "Brand: " $brandshop[0,1]
Next
I get the response Current error status is The operation completed successfully. - 0
Rows in spreadsheet are -1
The file it is reading is a 97-2003 XLS file created in Excel 2007.
Anyone have any ideas?
Pax
|
|
Top
|
|
|
|
|
#200162 - 2010-10-05 05:21 PM
Re: ReadExcel2 - identifying unique values in column, and more...
[Re: Pax]
|
Pax
Getting the hang of it
Registered: 2006-12-01
Posts: 51
|
I'm getting somewhere now and have got my loops in place, so just need by Brand (--b:) input validation to get sorted out. the below is my current code
break on
call @ScriptDir + "\dirlist.udf"
call @ScriptDir + "\dirplus.udf"
call @ScriptDir + "\msg.udf"
call @ScriptDir + "\copyfiles.udf"
call @ScriptDir + "\readexcel2.udf"
$Brandshop = readexcel2(@Scriptdir + '\pccs.xlsx',,-1,-1)
If @ERROR = "2" ; pcc spreadsheet is required - die if not provided
? 'ERROR OCCURED: @serror (@error)' @CRLF @CRLF
Quit 1
EndIf
; Get the source, dest & brand args specified on the command-line
; get the command line as an array - elements 0 & 1 are the kix.exe and the script.kix items
$aCmdLine = GetCommandLine(1)
; scan the array looking for the source arg
$ = AScan($aCmdLine, '--s:', , , 1) ; get SOURCE arg
If $ > 1 ;
$Source = SubStr($aCmdLine[$], 5)
Else ; arg is required - die if not provided
'ERROR: Source was not specified!' @CRLF @CRLF
'Usage: myscript.kix --s:Source_path --d:destination_path --b:brand' @CRLF
Quit 1
EndIf
; scan the array looking for the destination arg
$ = AScan($aCmdLine, '--d:', , , 1) ; get destination arg
If $ > 1 ;
$Dest = SubStr($aCmdLine[$], 5)
Else ; arg is required - die if not provided
'ERROR: Destination was not specified!' @CRLF @CRLF
'Usage: myscript.kix --s:Source_path --d:destination_path --b:brand' @CRLF
Quit 1
EndIf
; scan the array looking for the brand arg
$ = AScan($aCmdLine, '--b:', , , 1) ; get brand type
If $ > 1 ;
$Brand = SubStr($aCmdLine[$], 5)
Else ; arg is required - die if not provided
'ERROR: Brand was not specified or was invalid!' @CRLF @CRLF
'Usage: myscript.kix --s:Source_path --d:destination_path --b:brand' @CRLF
Quit 1
EndIf
$destdirs = Dir($dest + "\*")
While $destdirs <> "" and @Error = 0
For $rowcount = 0 to ubound($Brandshop)
Select
Case $Brand = $Brandshop[$rowcount,1] and $destdirs = $Brandshop[$rowcount,0]
$copycentral = CopyFiles($Source,$Dest + "\" + $destdirs,0,1)
Case $Brand = "ALL" and $destdirs = $Brandshop[$rowcount,0]
$copycentral = CopyFiles($Source,$Dest + "\" + $destdirs,0,1)
Case 1
EndSelect
Next
$destdirs = Dir()
Loop
exit
The last thing here is to get in some input validation. I know that readexcel2() reads into an array. Should I verify the Brand input directly against the array as built, or is it better to build another array of unique elements?
My only problem is that the Brand for some branches is intentionally left blank and so I don't know what effect this might have. If I build an array of unique elements, then I can use that in the array in the error feedback to the script runner.
Anyone wondering about logging failures and successes, then I'll sort that out writing to a file which will then launch Excel when copy is finished and view results/errors/error codes, etc.
Pax
edit: oooo just found qsort() and uniq(). I will explore tomorrow.
Edited by Pax (2010-10-05 06:42 PM)
Edit Reason: research
|
|
Top
|
|
|
|
|
#200164 - 2010-10-06 09:16 AM
Re: ReadExcel2 - identifying unique values in column, and more...
[Re: Glenn Barnas]
|
Pax
Getting the hang of it
Registered: 2006-12-01
Posts: 51
|
Just a question - have you seen xlLib? It's a library of 25 functions used to interface with MS Excel, not just simple read/write but file open/save, format, sort, and even sheet manipulation.
Glenn
No I haven't. I will have to look it up, as it will probably do all my writing for logging and allow reporting for the script runner. I'll take a look.
Thanks for the suggestion.
Pax
|
|
Top
|
|
|
|
|
#200170 - 2010-10-06 01:58 PM
Re: ReadExcel2 - identifying unique values in column, and more...
[Re: Pax]
|
Pax
Getting the hang of it
Registered: 2006-12-01
Posts: 51
|
edit: oooo just found qsort() and uniq(). I will explore tomorrow.
How does one reference an array read in by ReadExcel2()? If I read in the excel spreadsheet with 2 columns, the first with office, and the second with brand using $Brandshop = readexcel2(@Scriptdir + '\pccs.xlsx',,-1,-1)
Ideally I could use nexted functions so that $validbrands = uniq(qsort($Brandshop[??,1],1))
I thought that possibly $brandshop[0,1] might reference they array iself, but when I printed the array - qsort($Brandshop[??,1],1) - to screen using the code $sortedbrands = qsort($Brandshop[0,1],1)
For $i = 0 to UBound($sortedbrands)
? $sortedbrands[$i] + ','
Next
Am I missing something obvious here?
Pax
|
|
Top
|
|
|
|
|
#200192 - 2010-10-07 02:12 AM
Re: ReadExcel2 - identifying unique values in column, and more...
[Re: Pax]
|
Shanee
Fresh Scripter
Registered: 2006-10-13
Posts: 39
Loc: Tulsa, OK
|
Have you tried displaying the excel array before the sort? That would atleast help determine where the problem is. And don't forget that readexcel2 returns a multiple dimension array.
$Brandshop = readexcel2(@Scriptdir + '\pccs.xlsx',,-1,-1)
For $i = 0 to UBound($brandshop)
For $j = 0 to UBound($brandshop,2)
? $brandshop[$i,$j]+@CRLF
Next
?
Next
(untested)
|
|
Top
|
|
|
|
|
#200203 - 2010-10-08 11:28 AM
Re: ReadExcel2 - identifying unique values in column, and more...
[Re: Shanee]
|
Pax
Getting the hang of it
Registered: 2006-12-01
Posts: 51
|
Have you tried displaying the excel array before the sort? That would atleast help determine where the problem is. And don't forget that readexcel2 returns a multiple dimension array.
$Brandshop = readexcel2(@Scriptdir + '\pccs.xlsx',,-1,-1)
For $i = 0 to UBound($brandshop)
For $j = 0 to UBound($brandshop,2)
? $brandshop[$i,$j]+@CRLF
Next
?
Next(untested)
I know that the array is returned correctly because I am using it's logic further down my script in matching not only the brand (second column) but values for each office (first column).
The section I am trying to add in now is input validation to ensure that the brand chosen in the '--b:' input is something which exists in the second column of the spreadsheet.
Although I am using IsInArray() for the input validation to ensure that the value is valid, because number of brands is greater than numbers of offices, there will be duplicates of brands, and I want to output to the user the valid list which they could choose from. This is why I need a uniq() and qsort() done.
In either case I need to know how to address the array even if I was referencing the array which is the second column directly into IsInArray().
Pax
edit: ran your code anyway and it outputs the array to screen fine.
Edited by Pax (2010-10-08 11:50 AM)
Edit Reason: I've done stuff
|
|
Top
|
|
|
|
|
#200206 - 2010-10-08 04:26 PM
Re: ReadExcel2 - identifying unique values in column, and more...
[Re: Richard H.]
|
Pax
Getting the hang of it
Registered: 2006-12-01
Posts: 51
|
I think that the "-1" you were seeing is just an untrapped status returned by the bit of code "$.workbooks.Close".
UNIQ() and QSORT() work only with single dimension arrays, so will never work with the two dimensional array that you are returning from ReadExcel2()
The simplest fix for you is to create a couple of single dimension arrays of the right size and copy each of the "columns" into them, after which you can pass them to UNIQ() / QSORT().
The complex solution is to update the UDFs to support multi-dimension arrays, but if you think about it a bit you'll realise that it's not what you want it to do - you actually want to treat the columns as seperate lists, so you might as well split them up.
So if I am to understand this, as far as how to proceed.
- Loop through the second column array and create a string - I think writing directly to a new array might be beyond me unless there is some template code to understand and accomodate to my needs.
- Convert string to array
- qsort() then uniq() results
- work with as previously discussed using isinarray() and join() to validate input and output valid results to user.
I do understand what you mean about the complexities of the 2D array and having it adapted to single dimension array functions like those I wish to use for my work.
Pax
|
|
Top
|
|
|
|
|
#200209 - 2010-10-08 04:59 PM
Re: ReadExcel2 - identifying unique values in column, and more...
[Re: Richard H.]
|
Richard H.
Administrator
Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
|
Here you go.
This script contains a simple UDF Array2Vector() which will take a two dimensional array and return a simple (1D array) each of whose elements is itself a simple array.
This means that you can reference and manage each of the arrays as an individual array, indeed they can be different sizes.
You refer to the individual elements slightly differently:
- $MultiArray[x,y]
- $ArrayOfArrays[x][y]
Be careful with the UDF though as I haven't built any error checking into it.
$=SetOption("Explicit","ON")
Dim $i,$ArrayOfArrays,$SimpleArray
Dim $MultiArray[2,5]
For $i=0 to 4
$MultiArray[0,$i]=SubStr("ABCDE",$i+1,1)
$MultiArray[1,$i]=SubStr("ZYXWV",$i+1,1)
Next
$ArrayOfArrays=Array2Vector($MultiArray)
"Array #1:"+@CRLF
$SimpleArray=$ArrayOfArrays[0]
For $i=0 To UBound($SimpleArray)-1
" Element # "+($i+1)+": "+$SimpleArray[$i]+@CRLF
Next
"Array #2:"+@CRLF
$SimpleArray=$ArrayOfArrays[1]
For $i=0 To UBound($SimpleArray)-1
" Element # "+($i+1)+": "+$SimpleArray[$i]+@CRLF
Next
; Convert a 2D array to an array of 1D arrays
Function Array2Vector($A)
Dim $i,$t,$e
Redim $Array2Vector[UBound($A,1)]
For $i = 0 To UBound($A,1)-1
Redim $e[UBound($A,2)]
For $t=0 To UBound($a,2)-1
$e[$t]=$A[$i,$t]
Next
$Array2Vector[$i]=$e
Next
Exit 0
EndFunction
|
|
Top
|
|
|
|
|
#200211 - 2010-10-08 06:38 PM
Re: ReadExcel2 - identifying unique values in column, and more...
[Re: Richard H.]
|
Pax
Getting the hang of it
Registered: 2006-12-01
Posts: 51
|
Hi Richard,
Thanks for that. I'll take a look at it Monday because right now for me (and I imagine you) it's home time.
Hmm another person down the road from me that I've found in the last month.
Pax
|
|
Top
|
|
|
|
|
#200233 - 2010-10-11 01:09 PM
Re: ReadExcel2 - identifying unique values in column, and more...
[Re: Richard H.]
|
Pax
Getting the hang of it
Registered: 2006-12-01
Posts: 51
|
Hi Richard,
Having a another look at your reply, post weekend, and I put the below code into and UDF and included into my code kix file.
; Convert a 2D array to an array of 1D arrays
Function Array2Vector($A)
Dim $i,$t,$e
Redim $Array2Vector[UBound($A,1)]
For $i = 0 To UBound($A,1)-1
Redim $e[UBound($A,2)]
For $t=0 To UBound($a,2)-1
$e[$t]=$A[$i,$t]
Next
$Array2Vector[$i]=$e
Next
Exit 0
EndFunction
I have added the line
$Brands = Array2Vector($Brandshop[1])
$Brandshop = readexcel2(@Scriptdir + '\pccs.xlsx',,-1,-1) ERROR : array reference out of bounds!
I don't know if it is something in the code, or the way it is referenced, but I think it is right.
Pax
Edited by Pax (2010-10-11 01:09 PM)
|
|
Top
|
|
|
|
|
#200235 - 2010-10-11 05:53 PM
Re: ReadExcel2 - identifying unique values in column, and more...
[Re: Pax]
|
Richard H.
Administrator
Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
|
No, you pass it the entire array and it returns a new array which contains a lot of sub-arrays. It's more difficult to explain that demonstrate but if you think of it as returning an array with each of your spreadsheet columns in each element you won't be far wrong.
There is a problem with my UDF though in that it expects the source array to be in one orientation, and if it isn't the results are not what you expect. I've updated it with an optional flag so that you can select the orientation as the ReadExcel2() returns the array in a counter-intuitive orientation.
; Convert a 2D array to an array of 1D arrays
Function Array2Vector($A,Optional $iSwitch)
Dim $i,$t,$e
If $iSwitch $X=2 $Y=1 Else $X=1 $Y=2 EndIf
Redim $Array2Vector[UBound($A,$X)]
For $i = 0 To UBound($A,$X)
Redim $e[UBound($A,$Y)]
For $t=0 To UBound($a,$Y)
If $iSwitch $e[$t]=$A[$t,$i] Else $e[$t]=$A[$i,$t] EndIf
Next
$Array2Vector[$i]=$e
Next
Exit 0
EndFunction
Paste that into your code and try this:
$NewArray = Array2Vector($Brandshop,1)
"Column A="+Join($NewArray[0],", ")+@CRLF
@CRLF
"Column B="+Join($NewArray[1],", ")+@CRLF
|
|
Top
|
|
|
|
|
#200243 - 2010-10-13 11:03 AM
Re: ReadExcel2 - identifying unique values in column, and more...
[Re: Richard H.]
|
Pax
Getting the hang of it
Registered: 2006-12-01
Posts: 51
|
I cannot figure out for the life of me why I rarely get notifications of replies to my watched threads, but that is something else to sort out.
I tried you suggestion with my code with removed commentsbreak on
call @ScriptDir + "\readexcel2.udf"
call @ScriptDir + "\isinarray.udf"
$Brandshop = readexcel2(@Scriptdir + '\pccs.xlsx',,-1,-1)
$NewArray = Array2Vector($Brandshop,1)
'Column A=' + Join($NewArray[0],', ') + @CRLF
@CRLF
'Column B=' + Join($NewArray[1],', ') + @CRLF
and the console returns C:\Stuff\CopyCentral>kix32 ccn.kix
-1
ERROR : expected ')'!
Script: C:\Stuff\CopyCentral\ccn.kix
Line : 28
Line 28 correlates to $NewArray = Array2Vector($Brandshop,1)
Is the response which gives as -1 interferring with your code asI don't know where this is being generated? I have used the same readexcel2.udf in the other code I am using to read in the spreadsheet.
I've attached the UDFs as they have been included.
Thanks for your help on this. I really appreciate it.
Pax
Attachments
array2vector.udf (285 downloads)
Description:
readexcel2.udf (294 downloads)
Description:
|
|
Top
|
|
|
|
|
#200417 - 2010-10-29 01:19 PM
Re: ReadExcel2 - identifying unique values in column, and more...
[Re: Richard H.]
|
Pax
Getting the hang of it
Registered: 2006-12-01
Posts: 51
|
Hi Richard,
I'm sorry for not replying thus far. For some reason I don't get alerts when my threads are updated even though they are in my watch lists, and I've enabled email alerts.
The spreadsheet looks like the below, and there is no "header" on each column. The data extends for quite time time below this one and the missing data in the second column is intentional.
06Q FCB
08Q FCB
3Y2O
3Y99
3Y9A
3Y9E
3Y9H FCM
3Y9I RTW
3YM4 RTW
3YY2 FCB
3YY4 FCB
3YY9 FCB
3YZ0 FCB
50Z4 FCM
5NA9 SUPP
5Z0B FCB
5Z0N FCB
5Z0T SUPP
5Z0Y
5Z1M FCB
5Z1V RTW
5Z1X FCB
5ZK
The data doesn't show correctly, but the data is seperated by a tab.
Geoff
Edited by Pax (2010-10-29 01:21 PM)
|
|
Top
|
|
|
|
Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart
|
0 registered
and 484 anonymous users online.
|
|
|
