Page 1 of 3 123>
Topic Options
#180834 - 2007-09-25 08:33 PM Need a script that read/collect and creates...
1984 Offline
Starting to like KiXtart

Registered: 2003-08-14
Posts: 150
Hi,

I have an Excel file which contains hundreds of user IDs, contact information, location, department, and manager IDs for each of listed users.

I need a script which reads the current Excel file, find/collect all users belonging to each of the managers, and then for each “manager” it creates new Excel files/sheets containing all users that belong to that manager.

Excel sheet:

 Code:
A	B	C	D
ID:	Unit:	Site:	Manager:
userP	UnitBX	London	ManD
userX	UnitA	Paris	ManA
user54	Unit34	London	ManF
user23	Unit34	London	ManF
user01	UnitRR	Paris	ManC
user11	UnitBX	Madrid	ManA
user7	UnitZ	Madrid	ManD
user62	UnitRR	London	ManD
userTX	UnitA	Paris	ManD
userLA	UnitFM	Madrid	ManA
user05	UnitRV	London	ManC
user12	UnitZ	Paris	ManC




Could someone give me a hand on this please?

BR/CY
_________________________
"... Great minds talk about idea' s, average minds talk about events and samll minds talks about people...!"

Top
#180835 - 2007-09-25 08:40 PM Re: Need a script that read/collect and creates... [Re: 1984]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
look at this post for a library of Excel functions. Once you load such a library, you can call the functions like any other Kix command.

This library contains UDFs to read/write, Open, Save, create, and Format Excel worksheets.

Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#180837 - 2007-09-25 11:31 PM Re: Need a script that read/collect and creates... [Re: Glenn Barnas]
1984 Offline
Starting to like KiXtart

Registered: 2003-08-14
Posts: 150
Thanx Glenn,

I checked your UFDs, but cannot figure out which one, or how to use them to get the result I want, as I described earlier.

Let’s say I have same scenario as above but the list is a text file (*.txt) and a delimiter is used to separate each values, such as “;”.

 Code:
userP;UnitBX;London;ManD
userX;UnitA;Paris;ManA
user54;Unit34;London;ManF
user23;Unit34;London;ManF
user01;UnitRR;Paris;ManC
user11;UnitBX;Madrid;ManA
user7;UnitZ;Madrid;ManD
user62;UnitRR;London;ManD
userTX;UnitA;Paris;ManD
userLA;UnitFM;Madrid;ManA
user05;UnitRV;London;ManC
user12;UnitZ;Paris;ManC


Now how can I read that text file, find/collect all users belonging to each of the managers, and then for each “manager” generate new text file containing all users that belong to that manager.
_________________________
"... Great minds talk about idea' s, average minds talk about events and samll minds talks about people...!"

Top
#180839 - 2007-09-26 12:42 AM Re: Need a script that read/collect and creates... [Re: 1984]
Witto Offline
MM club member
*****

Registered: 2004-09-29
Posts: 1828
Loc: Belgium
You can always use your macro recorder to catch some code and use it in a script
Here is maybe an idea for your first scenario
 Originally Posted By: 1984
have an Excel file which contains hundreds of user IDs, contact information, location, department, and manager IDs for each of listed users.

;*************************************************************************
;  Script Name:  
;  Author:        Wim Rotty
;  Date:          26/09/2007
;  Description:   Copy and paste data from one Excel book to other books
;*************************************************************************

 
;Script Options
If Not @LOGONMODE
    Break On
Else
    Break Off
EndIf
Dim $RC
$RC = SetOption("Explicit", "On")
$RC = SetOption("NoMacrosInStrings", "On")
$RC = SetOption("NoVarsInStrings", "On")
If @SCRIPTEXE = "KIX32.EXE"
    $RC = SetOption("WrapAtEOL", "On")
EndIf

;Declare variables
Dim
 
$MyTable
Dim $objExcel
Dim $xlDown, $xlToLeft, $xlToRight, $xlUp

;Initialize variables
$MyTable = "C:\Test\Tabel.xls"
$objExcel = CreateObject("Excel.Application")
If @ERROR
    Exit @ERROR
EndIf
$xlDown = -4121
$xlToLeft = -4159
$xlToRight = -4161
$xlUp = -4162

;Code
$objExcel.Visible = -1
$RC = $objExcel.Workbooks.Open($MyTable)
$RC = $objExcel.Selection.Autofilter
$RC = $objExcel.Range("A1").End($xlDown).End($xlToRight).Select
$RC = $objExcel.Range($objExcel.Selection, $objExcel.Cells(1)).Select
$RC = $objExcel.Selection.AutoFilter(4, "ManA")
$RC = $objExcel.Selection.Copy
$RC = $objExcel.Workbooks.Add
$RC = $objExcel.ActiveSheet.Paste
$RC = $objExcel.ActiveWindow.ActivateNext
$RC = $objExcel.Selection.AutoFilter(4, "ManC")
$RC = $objExcel.Selection.Copy
$RC = $objExcel.Workbooks.Add
$RC = $objExcel.ActiveSheet.Paste
$RC = $objExcel.ActiveWindow.ActivateNext
$RC = $objExcel.Selection.AutoFilter(4, "ManD")
$RC = $objExcel.Selection.Copy
$RC = $objExcel.Workbooks.Add
$RC = $objExcel.ActiveSheet.Paste
$RC = $objExcel.ActiveWindow.ActivateNext
$RC = $objExcel.Selection.AutoFilter(4, "ManF")
$RC = $objExcel.Selection.Copy
$RC = $objExcel.Workbooks.Add
$RC = $objExcel.ActiveSheet.Paste
$RC = $objExcel.ActiveWindow.ActivateNext
$RC = $objExcel.Selection.AutoFilter(4)

;Personal UDF Section

;UDF Section

Microsoft Excel Constants (Excel 2003 VBA Language Reference)

Top
#180840 - 2007-09-26 01:02 AM Re: Need a script that read/collect and creates... [Re: Witto]
1984 Offline
Starting to like KiXtart

Registered: 2003-08-14
Posts: 150
Witto thanx for your wonderful code,

I see in your code that “managers” (ManD, ManA …) are manually defined .

But the problem is that there are more than 50 managers. I want to avoid manually defining each one in the code.

The script should also be automated to read and list managers without manually defining them in the code.


Edited by 1984 (2007-09-26 01:25 AM)
_________________________
"... Great minds talk about idea' s, average minds talk about events and samll minds talks about people...!"

Top
#180841 - 2007-09-26 05:55 AM Re: Need a script that read/collect and creates... [Re: 1984]
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11623
Loc: CA
Well KiXtart is not exactly a match for doing this sort of manipulation of Excel data. I would recommend using the built-in VBA or if you're not familiar with it then this site here is like KiXtart.org only for EXCEL

There are a lot of very smart guys over there that can easily help you whip up something.

http://www.mrexcel.com/

Top
#180845 - 2007-09-26 08:46 AM Re: Need a script that read/collect and creates... [Re: NTDOC]
Gargoyle Offline
MM club member
*****

Registered: 2004-03-09
Posts: 1597
Loc: Valley of the Sun (Arizona, US...
If your files are CSV files, then Kix can do what you want and output them to a CSV file as well.

Using your example...

 Code:
;Script Options
$SO=SETOPTION("Explicit", "ON")
$SO=SETOPTION("NoMacrosInStrings", "ON")
$SO=SETOPTION("NoVarsInStrings", "ON")
$SO=SETOPTION("WrapAtEOL", "ON")

If Not @LogonMode
	BREAK ON
EndIf

Dim $FH, $Line, $Managers[], $Count, $Manager, $Count2

$FH = FreeFileHandle()
$Count = 0


Open($FH,"C:\Support\MyInputFile.CSV")

$Line = Readline($FH)

While @Error = 0
	$Line = Split($Line,",")
	
	If InArray($Managers,$Line[3]) < 0
		Redim Preserve $Managers[$Count]
		$Managers[$Count] = $Line[3]
		$Count = $Count + 1
	EndIf
	$Line = Readline(1)
Loop

Close($FH)

Dim $Users[Ubound($Managers),0]

$Count = 0

For Each $Manager in $Managers
	$Count2 = 1
	$FH = FreeFileHandle()
	Open($FH,"C:\Support\MyInputFile.CSV")
	$Users[$Count,0] = $Manager
	$Line = Readline($FH)
	While @Error = 0
		$Line = Split($Line,",")
		If $Line[3] = $Manager
			$Users[$Count,$Count2] = $Line[0]
			$Count2 = $Count2 + 1
		EndIf
		$Line = Readline($FH)
	Loop
	$Count = $Count + 1
Next
Close($FH)

For $Count = 0 to Ubound($Users,1)
	$FH = FreeFileHandle()
	Open($FH,"C:\Output\"+$Users[$Count,0]+".csv",5)
	For $Count2 = 1 to Ubound($Users,2)
		Writeline($FH,$Users[$Count,$Count2]+@CRLF)
	Next
	Close($FH
Next


This does require the use of the UDF InArray so make sure you include it.

This Code sample is 100% UNTESTED.

This is just an example of one way to accomplish your task.
_________________________
Today is the tomorrow you worried about yesterday.

Top
#180849 - 2007-09-26 09:56 AM Re: Need a script that read/collect and creates... [Re: Gargoyle]
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
No need for arrays, just redirect the output to a file as you read it.

I've kept it very simple and used RedirectOutput() to save strokes.

Input and ouput is assumed to be CSV (comma "," seperated values)

 Code:
Break ON
 
$=SetOption("Explicit","ON")
Dim $fh,$sFile,$sLine
 
$fh=FreeFileHandle()
$sFile=".\data.csv"
 
If Not Open($fh,$sFile)
	$sLine=ReadLine($fh)
	While Not @ERROR
		$sLine=SPlit($sLine,",")
		If UBound($sLine)=3
			$=RedirectOutput($sLine[3]+".csv")
			Join($sLine,",")+@CRLF
			$=RedirectOutput("")
		EndIf
		$sLine=ReadLine($fh)
	Loop
Else
	"Cannot open "+$sFile+" for reading"+@CRLF
EndIf


This code is 100% tested. \:\/

Top
#180852 - 2007-09-26 01:31 PM Re: Need a script that read/collect and creates... [Re: Richard H.]
BradV Offline
Seasoned Scripter
****

Registered: 2006-08-16
Posts: 686
Loc: Maryland, USA
I'd have to agree with NTDOC. You have an excel spreadsheet that contains the data you want to use to create more excel spreadsheets. I would use the onboard VBA.

Regards,

Brad V

Top
#180867 - 2007-09-26 05:38 PM Re: Need a script that read/collect and creates... [Re: BradV]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
Some ideas based on the original request - read a file (excel or other) and group the data by manager. First a simple example reading data from Excel and gathering it in a temporary file. This could also be done if the source is a CSV-type file.

The second example shows how to take that temporary data and create CSV files for each manager. You could use the xlLib to create new XLS files as well.

Glenn

 Code:
;Working with Excel, for example

; Define an Excel object pointer
$oXL = xlInstantiateApp()
 
; Open the file
xlFile($oXL, 0, 'MyFile.xls')
 
; Return an array of 4 values from the first data row
; Start with row 2, since row 1 has titles
; keep reading until a blank row is returned
$Row = 2
 
While $Row > 0
  $Range = 'A' + CStr($Row) + ':D' + CStr($Row)
  $aData = xlRangeValue($oXL, $Range, , 'Sheet1')
 
  ; The first element of the array contains the user, fourth has the manager
  ; Howard Bullok has a great Hash UDF that would work well here, but 
  ; I'm going to use a "poor-man's" hash - an INI file, and reduce the number
  ; of UDFs required
 
  If $aData[0] = ''
    ; Anti-Golf to make it clear what data goes where
    $User = $aData[0]
    $Manager = $aData[3]
    $Record = Join($aData, Chr(31))	; Chr(31) is ASCII US - Unit Separator
    $ = WriteProfileString('.\hash.ini', $Manager, $User, $Record)
  Else
    $Row = -1     ; no more data - exit the loop
  EndIf
 
  $Row = $Row + 1
Loop




You now have a file that looks like this, with the data from the spreadsheet:
 Code:
[MANA]
UserX=userX;UnitA;Paris;ManA
User11=user11;UnitBX;Madrid;ManA
userLA=userLA;UnitFM;Madrid;ManA
[MANC]
User01=user01;UnitRR;Paris;ManC
User05=user05;UnitRV;London;ManC
User12=user12;UnitZ;Paris;ManC


and so on...

This groups users by manager, with the extracted data. You can then use the EnumINI() UDF to first get an array of manager IDs, and then a list of users by manager.

Of course, you could read a text file, use Split(string, ';') to convert to an array and write to the hash file in pretty much the same way.
In the example below, use the CSV() UDF to output properly formatted CSV records.


 Code:
; Get list of Managers
$aManagers = EnumIni('.\hash.ini')
For Each $Manager in $aManagers
  ; get the list of users for the current manager ID
  $aUsers = EnumIni('.\hash.ini', $Manager)

  ; the print statements are only for debugging/monitoring
  ; Removing them can streamline the process by placing the RedirectOutput commands
  ; outside of the Users For/Next loop
  'Manager is ' $Manager ?
  For Each $User in $aUsers
    $User ?
    $aData = Split(ReadProfileString('.\hash.ini', $Manager, $User), Chr(31))
    ; the original data is now in $aData array
    ; simple result - write a CSV record to a MANAGER.CSV file
    $ = RedirectOutput('.\' + $Manager + '.csv')
    CSV($aData) ?
    $ = RedirectOutput('')

  Next ; User

Next ; Manager
_________________________
Actually I am a Rocket Scientist! \:D

Top
#180878 - 2007-09-26 08:17 PM Re: Need a script that read/collect and creates... [Re: Glenn Barnas]
1984 Offline
Starting to like KiXtart

Registered: 2003-08-14
Posts: 150
Thanx a lot all for your input,

I have now tested using both CSV and text format.

Unfortunately I cannot make any of your later provided codes to work properly.

Richard:
Nothing happens!

Gargoyle:
Dim $Users[UBound($Managers),0] give the error: array reference out of bounds!.

Glenn:
xlFile gives error: unknown command!

Any suggestion?
_________________________
"... Great minds talk about idea' s, average minds talk about events and samll minds talks about people...!"

Top
#180880 - 2007-09-26 08:37 PM Re: Need a script that read/collect and creates... [Re: 1984]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
 Originally Posted By: 1984

Glenn:
xlFile gives error: unknown command!

Any suggestion?

Are you downloading the UDFs and including them in your script? (Either by directly copying / pasting, or via CALL statements?)
Do you have Excel installed on the machine where you are running this? You need Excel installed in order to instantiate a connection to it.

As far as my code examples, the logic should function. You'll need to add error checking, change file names and output formats and such, of course.

It's rare that I'll provide "silver-platter" code. 16 years as an IT instructor prevents that, but you will get a good hard shove in the right direction. ;\)

Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#180882 - 2007-09-26 09:24 PM Re: Need a script that read/collect and creates... [Re: Glenn Barnas]
1984 Offline
Starting to like KiXtart

Registered: 2003-08-14
Posts: 150
Yeap, Excel is in place (2007!) and code copied and pasted to ase.
Not meaning to be served "silver-platter" codes, but would like feedbacks that I could understand and learn :-)

Not a coder, just like kix :-)

Top
#180885 - 2007-09-26 11:30 PM Re: Need a script that read/collect and creates... [Re: 1984]
1984 Offline
Starting to like KiXtart

Registered: 2003-08-14
Posts: 150
Oh sorry Glenn, I had a typo in the function part when pasting code.

However now I get this error :

"invalid method/function call: missing required parameter 3!"
referring to the line $_Sheet = IIf($_Sheet = '', $_ID.ActiveSheet.Name, $_Sheet)
in function section xlRangeValue

 Code:
; Define an Excel object pointer
$oXL = xlInstantiateApp()
 
; Open the file
xlFile($oXL, 0, 'C:\Bin\Book1.xls')
 
; Return an array of 4 values from the first data row
; Start with row 2, since row 1 has titles
; keep reading until a blank row is returned
$Row = 2
 
While $Row > 0
  $Range = 'A' + CStr($Row) + 'D' + CStr($Row)
  $aData = xlRangeValue($oXL, $Range, , 'Sheet1')
 
  ; The first element of the array contains the user, fourth has the manager
  ; Howard Bullok has a great Hash UDF that would work well here, but 
  ; I'm going to use a "poor-man's" hash - an INI file, and reduce the number
  ; of UDFs required
 
  If $aData[0] = ''
    ; Anti-Golf to make it clear what data goes where
    $User = $aData[0]
    $Manager = $aData[3]
    $Record = Join($aData, Chr(31))	; Chr(31) is ASCII US - Unit Separator
    $ = WriteProfileString('.\hash.ini', $Manager, $User, $Record)
  Else
    $Row = -1     ; no more data - exit the loop
  EndIf
 
  $Row = $Row + 1
Loop

Function xlFile($_ID, $_FN, $_File)

Dim $_

Select
Case $_FN = 0 ; Open
If Exist($_File) ; if the file exists, open it
;$_ = $_ID.Open($_File, $_Links, $_RO, $_Fmt)
$_ = $_ID.WorkBooks.Open($_File)
Exit @ERROR
Else
Exit 2 ; otherwise complain that is isn't found
EndIf

Case $_FN = 1 ; Save(as)
; If the file was previously saved (or opened), just re-save it,
; otherwise do a Save As
If Not $_ID.ActiveWorkbook.Path
$_ = $_ID.ActiveWorkbook.SaveAs($_File)
Else
$_ = $_ID.ActiveWorkbook.Save
EndIf
Exit @ERROR

Case $_FN = 2 ; SaveAs
$_ = $_ID.ActiveWorkbook.SaveAs($_File)
Exit @ERROR

EndFunction

Function xlRangeValue($_ID, $_Range, OPTIONAL $_Value, OPTIONAL $_Sheet)

$_Sheet = IIf($_Sheet = '', $_ID.ActiveSheet.Name, $_Sheet)

If VarType($_Value) <> 0
$_ID.WorkSheets($_Sheet).Range($_Range).Value = $_Value
Else
$xlRangeValue = $_ID.WorkSheets($_Sheet).Range($_Range).Value
EndIf

Exit @ERROR

EndFunction

Top
#180888 - 2007-09-27 12:45 AM Re: Need a script that read/collect and creates... [Re: 1984]
Gargoyle Offline
MM club member
*****

Registered: 2004-03-09
Posts: 1597
Loc: Valley of the Sun (Arizona, US...
Dim $Users[UBound($Managers),0] give the error: array reference out of bounds!.

Try it this way
Dim $Count3,$Users[]
$Count3 = Ubound($Managers)
ReDim $Users[$Count3,0]
_________________________
Today is the tomorrow you worried about yesterday.

Top
#180889 - 2007-09-27 12:49 AM Re: Need a script that read/collect and creates... [Re: 1984]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
 Originally Posted By: 1984
Not meaning to be served "silver-platter" codes, but would like feedbacks that I could understand and learn :-)

Didn't imply that you were, just indicating that you'd have to add some of your own brain power to it. ;\)

I'll look at it after dinner and see what's up.

G-
_________________________
Actually I am a Rocket Scientist! \:D

Top
#180891 - 2007-09-27 05:32 AM Re: Need a script that read/collect and creates... [Re: Glenn Barnas]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
Actually, we found a possible bug in how the variant arrays are handled by Kix. I have updated the UDF referenced above to work around the problem. It now returns an array of arrays when more than one row is specified. I'll post some sample code in the morning that uses the updated UDF.

Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#180893 - 2007-09-27 05:49 AM Re: Need a script that read/collect and creates... [Re: Glenn Barnas]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
This should get you started reading your Excel file - make sure you download the latest xlLib from the page referenced by my earlier post near the top of this thread.

This script module creates the HASH.INI file from data in the spreadsheet - part 2 of the code I posted earlier can enumerate this INI file and generate the CSV files, one for each manager.

I've used your data and confirmed that the HASH.INI file is created as expected using this script.

Glenn

 Code:
;; KixGenerated: 2007/09/26 - 19:54:31
Break On

;Working with Excel, for example

; Define an Excel object pointer
$oXL = xlInstantiateApp()
 
; Open the file
xlFile($oXL, 0, 'c:\temp\test.xls')
'Open: ' @SERROR ?
 
; Return an array of 4 values from the first data row
; Start with row 2, since row 1 has titles
; keep reading until a blank row is returned
$Row = 2

While $Row > 0
  $Range = 'A' + CStr($Row) + ':D' + CStr($Row)
  $aData = xlRangeValue($oXL, $Range, , 'Sheet1')
For Each $ in $aData
 $ ?
Next

  ; The first element of the array contains the user, fourth has the manager
  ; Howard Bullok has a great Hash UDF that would work well here, but 
  ; I'm going to use a "poor-man's" hash - an INI file, and reduce the number
  ; of UDFs required

  If $aData[0]
    ; Anti-Golf to make it clear what data goes where
    $User = $aData[0]
    $Manager = $aData[3]
    $Record = Join($aData, Chr(31))
    $ = WriteProfileString('.\hash.ini', $Manager, $User, $Record)
  Else
    $Row = -1     ; no more data - exit the loop
  EndIf

  $Row = $Row + 1
Loop
_________________________
Actually I am a Rocket Scientist! \:D

Top
#180903 - 2007-09-27 01:56 PM Re: Need a script that read/collect and creates... [Re: 1984]
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
 Originally Posted By: 1984
Richard:
Nothing happens!


Well, I tested it (again) and it works fine.

Are your files comma (",") delimited?
Did you run the script from the command line?
Are there 4 fields on each line as in your example?

Here is the script again, but with a little more debugging information:
 Code:
Break ON

$=SetOption("Explicit","ON")
Dim $fh,$sFile,$sLine

$fh=FreeFileHandle()
$sFile=".\data.csv"

If Not Open($fh,$sFile)
	$sLine=ReadLine($fh)
	While Not @ERROR
		"Processing line: "+$sLine+@CRLF
		$sLine=SPlit($sLine,",")
		If UBound($sLine)=3
			$=RedirectOutput($sLine[3]+".csv")
			Join($sLine,",")+@CRLF
			$=RedirectOutput("")
		Else
			"ERROR: Number of fields on this line <> 4"+@CRLF
			Exit 1
		EndIf
		$sLine=ReadLine($fh)
	Loop
Else
	"Cannot open "+$sFile+" for reading"+@CRLF
EndIf

Top
#180915 - 2007-09-27 06:15 PM Re: Need a script that read/collect and creates... [Re: Richard H.]
Witto Offline
MM club member
*****

Registered: 2004-09-29
Posts: 1828
Loc: Belgium
As far as I can see, 1984 says the files are semicolumn (";") delimited
 Originally Posted By: 1984

 Code:
userX;UnitA;Paris;ManA
user54;Unit34;London;ManF


Top
Page 1 of 3 123>


Moderator:  Shawn, ShaneEP, Ruud van Velsen, Arend_, Jochen, Radimus, Glenn Barnas, Allen, Mart 
Hop to:
Shout Box

Who's Online
2 registered (morganw, mole) and 414 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.084 seconds in which 0.032 seconds were spent on a total of 14 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org