Got a kix issue I'm hoping yall can help me with. I have a working script the goes through AD checking last logon time stamps and output anyone over 30 days to multiple excel .CSV files. What I'm trying to do is make it output to a single .CSV file except in multiple tabs. This is actually the first time I've worked with kix, I have some experience with VBScript, and I'm just working through a script I was given. I've tried messing with some of the UDF files listed on this site, didn't have much luck, I assume I'm not creating some of the objects needed.
Select Case @MONTHNO = 1 $Month = 12 Goto month Case @MONTHNO = 2 $Month = 1 Goto month Case @MONTHNO = 3 $Month = 2 Goto month Case @MONTHNO = 4 $Month = 3 Goto month Case @MONTHNO = 5 $Month = 4 Goto month Case @MONTHNO = 6 $Month = 5 Goto month Case @MONTHNO = 7 $Month = 6 Goto month Case @MONTHNO = 8 $Month = 7 Goto month Case @MONTHNO = 9 $Month = 8 Goto month Case @MONTHNO = 10 $Month = 9 Goto month Case @MONTHNO = 11 $Month = 10 Goto month Case @MONTHNO = 12 $Month = 11 Goto month EndSelect :month
$Domain = "DC=domain,DC=com" $var1 = '"&(objectcategory=person)(!(DormantAcctExempt=*))(lastLogonTimestamp>={{LOCAL:1601/01/01}})(lastLogonTimestamp<={{LOCAL:@year/$month/@mdayno}})"' $var2 = "samAccountName sn givenName title Department TelephoneNumber lastLogonTimestamp"
Glenn BarnasGlenn Barnas KiX Supporter
Registered: 2003-01-28
Posts: 4402
Loc: New Jersey
Couple of things right off the bat...
There's no need for GOTOs in a Select/Case statement - that's the point of the select - only ONE will be done.
The whole Select/Case/Endselect can be replaced with the single line:
Code:
$Month = IIf(@MONTHNO = 1, 12, @MONTHNO - 1)
This returns 1 if @MONTHNO is 12, otherwise returns one less than @MONTHNO.
Get rid of the If Exist() goto.. there's no fatal error and little penalty if you create a directory that's already there. It's probably more effort to detect it and go around than to just create the folders each time.
You can't output .CSV in multiple Excel sheets (tabs) - by definition, a CSV file is a single array of row/column data in a comma-delimited format. There's no way to specify a "Z-axis", which would represent multiple sheets.
If the "xlLib.udf" being referenced is the one I wrote, you can use that to directly create a .XLS file with as many tabs as you like. Download the latest revision from my web site (resources/Kix functions). You'll want to review the xlSheetAdd() and xlSheetSelect() functions in particular. The xlRangeValue will read/write a range of data on the active sheet.
The logic you should consider is: 1. generate the CSV files as you do now. 2. Use the xlLib functions to open a workbook 3. Use the xlLib functions to create a new sheet - then select the new sheet. 4. Read the file - use my CSV() function to convert each line you read to an array. 5. Use the xlRangeValue to write the array to a row indexed by a pointer var. Increment the pointer after each line read. Repeat the ReadLine / xlRangeValue process until the end of file is reached.
Close your file, point to the next file, and repeat steps 3-5 for each file you created with the adfind command.
That's the basics - there are several things that you can do to streamline this code, but let's get a basic process functioning first.
Glenn
_________________________ Actually I am a Rocket Scientist!
Ok, seems pretty straight forward. How do you read a file in KIX though? Should be easy to read it in from the .csv file by telling it to break on the "," but how do you do that, also, how do you test for end of file in KIX (test for NULL maybe?)? Last question, you CSV function, where is that defined, its not listed in the xlLiB file.
MartMart KiX Supporter
Registered: 2002-03-27
Posts: 4673
Loc: The Netherlands
You could use the Open(), ReadLine(), Split() and Close() functions like show below.
You could also use the ReadFile() UDF (it’s in the UDF section of this board) and the Split() function like shown in the second example.
Code:
$rc = Open(1,"c:\somefile.csv", 2)
$line = ReadLine(1)
While NOT @ERROR AND Trim($line) <> ""
$line = Split($line, ",")
;Do some other stuff here
$line = ReadLine(1)
Loop
$rc = Close(1)
Code:
$file = ReadFile("c:\somefile.csv")
For Each $line in $file
$line = Split($line, ",")
;Do some other stuff here.
Next
Glenn BarnasGlenn Barnas KiX Supporter
Registered: 2003-01-28
Posts: 4402
Loc: New Jersey
Mart's example will work only with the most basic form of CSV files - it will fail if the record contains embedded commas. That's why you need the CSV function - it follows strict CSV rules to break on the correct commas, not just on all commas.
Testing for end of file is easy - it returns an EOF error, so just keep reading until you get an error like in Mart's or my examples. In production code, you'd want to be sure that you either A) had actual data, or B) had the proper number of fields (better choice in this situation) by checking the UBound($arrData) value before you actually process the array data.
Code:
If Open(2,'MyFile.csv') = 0
$aryData = CSV(ReadLine(2))
While Not @ERROR
; process the array data. Since it's an array, you could prolly just
; use xlRangeValue to write the data, Assumes you've already created
; the Excel connection object, workbook, and sheet
$ = xlRangeValue($oID, $Row + ':' + $Col, $arrData, $WorkSheet)
$aryData = CSV(ReadLine(2))
Loop
$ = Close(2)
Else
'Error opening MyFile.csv' ?
EndIf
The CSV and xlLib UDFs are available here on KORG, and the latest versions can always be found on my web site in the Resources section. You should add some error-checking to this example code.
Glenn
_________________________ Actually I am a Rocket Scientist!
Ok, here is what I've done so far. For some reason the code hangs with this command: $aryData = CSV(ReadLine(2))
So I separated it like this:
$temp = ReadLine(2) $aryData = CSV($temp)
But it still failed out. So I put in a message box after $temp = to see what it was putting into $temp, it then displayed the correct line read and run fine?
Anyway, putting in a few other message boxes just to check how far the scrip was executing I got it all the way to saving the file, executed past this point but didn;t make a file. Tried everything I can find and still can't get that to work.
Code:
SetConsole("hide")
Break On
call @ScriptDir + "\xlLib.udf"
call @ScriptDir + "\csvLib.UDF"
$oXL = xlInit()
$outPutFile = "C:\KIX\test.xls"
$wBO = xlNewBook($oXL)
xlSheetAdd($oXL, 1, 'Sheet1')
xlSheetSelect($oXL, "Sheet1")
If Open(2,'C:\KIX\732AMS.csv') = 0
$temp = ReadLine(2)
$aryData = CSV($temp)
While Not @ERROR
; process the array data. Since it's an array, you could prolly just
; use xlRangeValue to write the data, Assumes you've already created
; the Excel connection object, workbook, and sheet
$ = xlRangeValue($oXL, $Row + ':' + $Col, $aryData, 'Sheet1')
$aryData = CSV(ReadLine(2))
Loop
$ = Close(2)
Else
MessageBox("ERROR", "", 64)
EndIf
xlFile($oXL, 2, 'C:\KIX\test.xls')
Glenn BarnasGlenn Barnas KiX Supporter
Registered: 2003-01-28
Posts: 4402
Loc: New Jersey
Not sure why you're going to so much effort during script development and debugging - SetConsole(hide) and MessageBox(). Yikes!
What's csvLib? If it's the CSV UDF from my site (or mine from KORG) just call it "CSV.UDF" - using "lib" as part of a udf filename implies it's a Library of related functions - CSV is a single UDF, so name it as such. This isn't contributing to your error, but deviates from standards, and that could come back to bite you (you know where) later.
At this stage, drop the SetConsole and MessageBox garbage - too complicated. Open a command prompt & CD to your folder where these scripts are and run "Kix32 myscript.kix" (using your script name, of course).
Good way to debug: Get the MSG() UDF and include that, too.. declare a global var called $DEBUG, set it to 1, and then sprinkle your code with
Dbg('Line 29: X=' + $X)
type messages - they'll display on the command line. The "Line 29" is an example - you're better served by logical names or messages, like
Dbg('result of CSV(Readline) is ' + $Temp)
When you're done debugging, simply set $DEBUG=0.
This will also allow you to enumerate the array values without popping up dozens of message boxes. Consider the code below:
Glenn
Edited by Glenn Barnas (2008-07-2911:44 PM) Edit Reason: bad line breaks
_________________________ Actually I am a Rocket Scientist!
Also, should the second parameter of xlRangeValue be in COl + ROW format (as in 'A1')? Earlier you had posted it as '' + $Row + $Col, but that gave the same error either way.
Code:
If Open(2,'C:\KIX\732AMS.csv') = 0
$aryData = CSV(ReadLine(2))
While Not @ERROR
For $I = 0 To UBound($aryData)
Dbg($aryData[$I])
Next
$ = xlRangeValue($oXL, $Col + $Row, $aryData, 'Sheet1')
$Row = $Row + 1
$aryData = CSV(ReadLine(2))
Loop
$ = Close(2)
Else
MessageBox("ERROR", "", 64)
EndIf
Glenn BarnasGlenn Barnas KiX Supporter
Registered: 2003-01-28
Posts: 4402
Loc: New Jersey
OK - I'm impressed that you got through a UDF as complex as xlLib in 5 days! Now you know I'm extra tough on my best students!
Here's code that's almost working on my system. I'm getting a COM error on xlRangeWrite, but this is the first time I've tried it on Excel 2007, so I'm not sure if that's related. I'll try it when I get home - I still have Office 2k3 on my laptop. If it works there, I'll look into what's needed for 2K7 support. Try it on 2K3 if you have it.
Couple of things I found:
You were right - it's Col/Row, not Row/Col.
Function is xlBookCreate, not xlNewBook as shown in the example. I'll fix that in the source.
You need to use xlQuit before the script ends, or you'll have many hidden copies of Excel running. If your script dies, you'll need to use Task Manager to kill Excel.
The process "'' + $Col + $Row" didn't work (the initial '' forced evaluation as a string). Changing to $Col + CStr($Row) provided a proper "A1" style specification.
Glenn
_________________________ Actually I am a Rocket Scientist!
Heh, I'll be honest, I don't really understand much of what you do in xlLib.udf, I've just been using it as a "black box". I have had some formal training in Java, so that helps with the logic building stuff, everything else I try to pick up as I have to use it.
It ran the script, created a .xls file, but the file was blank. So I tried a simpler loop to just place data, little tweaking and it worked. Now I will work on the multiple tabs, and probably try putting the whole block of code as a function.
Edit: Also, I was running this with Office 07 installed, don't know if that could be an issue.
Code:
If Open(2,'C:\KIX\732AMS.csv') = 0
$aryData = CSV(ReadLine(2))
While Not @ERROR
For $I = 0 To UBound($aryData)
$oXL.Cells($Col, $Row).Value = $aryData[$I]
$Row = $Row +1
Next
$Row = 1
$Col = $Col + 1
$aryData = CSV(ReadLine(2))
Loop
$ = Close(2)
Else
MessageBox("ERROR", "", 64)
EndIf
Edited by CTT (2008-07-3101:01 AM) Edit Reason: add info.
Glenn BarnasGlenn Barnas KiX Supporter
Registered: 2003-01-28
Posts: 4402
Loc: New Jersey
I find that treating Kix at the same level as a batch file during development - running from a command prompt and seeing the results immediately (inclluding nasty errors) is easier than trying to manage popups. I reserve those for the finished product that might be run by those less technical.
I was getting the same result - blank file. When I printed the @SERROR (as in my last example) I got a COM error - "Null()-Null() blah-blah...". I tested it with Office 2K3 and got the same result, so it's something related to how the cell is referenced. I'll tinker with it more tonight, along with correcting the example in the UDF code.
Putting the whole thing in a function would be good, since you've got 3 files to process. I'm glad to see you working with one file and raw code first to get the kinks worked out before moving to a function, though. Good building-block approach. With a function you could pass file and tab names, along with the XL object pointer. Handle the file open/create/close outside the function, and the tab create/select/write in the function.
Glenn
_________________________ Actually I am a Rocket Scientist!
Glenn BarnasGlenn Barnas KiX Supporter
Registered: 2003-01-28
Posts: 4402
Loc: New Jersey
The problem with xlRangeValue is resolved. Seems that neither of us read the header completely. It states quite clearly that when writing it expects an Array Of Arrays - we were providing a simple array. I verified that my old code worked with the current library, then dug into what we were feeding the function.
The solution is to introduce a new array - $xlData - and assign each row we read from the CSV file to a record in that array. The result is an array of arrays, and the xlRangeValue function is then called once after the data from the CSV file is exhausted. The code below works, creating a workbook with a single sheet, populated with data.
Note the key changes -
Addition of a $xlData array
Defining the index pointer to -1; incrementing it before referencing the array; using it to re-dimension the array
Calling xlRangeValue with the $xlData array after closing the input CSV file.
BTW - I get a format warning when opening the .XLS file in Excel 2K7, but it opens fine.
Glenn
_________________________ Actually I am a Rocket Scientist!
Here's the final function I came up with. I stuck with the simple loop for reading the array values since it was working already. I also get a warning when opening in 07, might have to do with the new format 07 is looking for by default, as you said though it opens fine.
xlInit(), xlBookCreate(), xlFile, and xlQuit are all handled outside the function, at the beginning and end of the script as appropriate.
Edit: Just had another question. How do you get KIC to throw "'s into a command line? The issue I have is that ADFind doesn;t like the & in a command, but from a DOS prompt I can put "&" in and it works, so how do I get KIX to do that via the Shell? Heres an example:
Glenn BarnasGlenn Barnas KiX Supporter
Registered: 2003-01-28
Posts: 4402
Loc: New Jersey
The "&" is special to Kix only outside of strings. Inside, it can exist like any other char. It IS special in DOS as a command connector - "CMD1 & CMD2" will run command 1 and then command 2, while "CMD1 && CMD2" will run command 1, and run command 2 only if command 1 was successful. So - the escaping has to occur at the command string, not within kix.
Also - at the sake of repeating myself yet again, creating Shell commands in a monolithic manner makes things hard to diagnose. Build your commands step by step in a $Cmd string. First off, it's easier to keep pairs of quotes in sync when there's only one or two sets per line. Secondly, you can display the $Cmd string before you execute it with "Shell $Cmd" to verify that it looks correct. You can even copy the generated command string from the screen, put it into notepad and run it as a bat file to see what's failing. Here's a comparison of monolithic vs. segmented Shell command building:
Finally, I prefer single quotes within Kix, so that I can embed double quotes more easily. See the $Staff= definition above for an example. This may be what you need, since the quotes will escape the "&" properly.
Glenn
_________________________ Actually I am a Rocket Scientist!
Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
Originally Posted By: Allen
Quote:
at the sake of repeating myself yet again
God knows I'm tired of reading that suggestion... "yet again"
The consistency of information, pathalogic adherence to good principles and indefatiguable will to shout yourself hoarse until you get your point understood God-damnit is just a sign that someone cares. Or is nuts
Glenn BarnasGlenn Barnas KiX Supporter
Registered: 2003-01-28
Posts: 4402
Loc: New Jersey
Yes, I care, but I never said I wasn't just a tad nuts, too.
I guess what goes on in my head when I see this is - somebody decides to create a long, complex Shell command, and then posts it here when it doesn't work. Instead of breaking it down, or even looking at the syntax before running the command, diagnosis of the command result begins. The problem is, you aren't seeing (or we aren't told) the error message.
Consider the following - we're fans of the Tom and Jerry cartoon series (Am I dating myself? Again?) We create a txt file to store our notes about an episode we just viewed by
Code:
C:\temp>notepad tom&jerry
We might be a bit annoyed when we see the result, but it will lead us in the right direction. Notepad opens, and the file name is "tom.txt", and the command line has a message stating
Code:
'jerry' is not recognized as an internal or external command,
operable program or batch file.
Clearly, the "&" is being interpreted by the shell. Changing the command so we wrap the "&" in quotes solves the problem.
Code:
C:\temp>notepad "tom&jerry"
results in notepad opening Tom&Jerry.txt, as we want.
I'm still wondering why Gargoyle inserted SPACES, because if he had inserted QUOTES (which are Chr(34)) his example might have worked. Too much sun and high temps, I guess. I say "might" have worked because the position of the quotes are important, too. The string with the ampersand is a parameter to an argument - a series of arguments, actually. Placement of the quotes around one argument and its parameter might not be correct, while placement around all argument/parameters, or specific parameters might be. That's why creating this in a Bat file to test the syntax and then moving a working command to Kix is a good idea.
That's the principal behind building the command line - you can debug it in small pieces; you can display it; you can copy/paste it to run it manually; you can edit that command line when things don't work to understand why, which then makes it easy to fix your original Kix code.
Throughout 18 years of teaching coding methods, I've always said "there's no wrong way to code a solution, but there are lots of bad ways. You're here to learn the good, the bad, and the ugly" (bet you knew that was coming, didn't ya!). Long, complex command strings like this fall into the Ugly category, bordering heavily on bad.
Glenn
PS - Thanks, Richard (I think)!
_________________________ Actually I am a Rocket Scientist!
It is most likely the placement of quotes within you command then. 
