Parsing information from an excel document - KiXtart.org

You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » Basic Scripting » Parsing information from an excel document

Page 1 of 2

Topic Options

#130771 - 2004-12-07 07:21 PM Parsing information from an excel document
Josh_R Josh_R Getting the hang of it Registered: 2003-11-25 Posts: 54	I am trying to figure out a way to parse information from an excel document. I have searched through the site and can't find anything that is at my level of scripting (novice). Can any one out there help me?
Top

#130772 - 2004-12-07 07:55 PM Re: Parsing information from an excel document
Les Les KiX Master Registered: 2001-06-11 Posts: 12734 Loc: fortfrances.on.ca	I think you need to provide better information. Do you really mean "Parsing information IN an excel document"? Is this in XLS format or exported as a delimited file? Does the computer that will run the script have Excel installed? Do you plan to use Excel COM? What is the nature of the information you want to parse? By "parse" do you really mean "extract"? _________________________ Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.
Top

#130773 - 2004-12-07 08:00 PM Re: Parsing information from an excel document
Josh_R Josh_R Getting the hang of it Registered: 2003-11-25 Posts: 54	It is an actual XLS document. Yes the computer has excel installed on it. By parse I mean take out certain information (user names) from a column.
Top

#130774 - 2004-12-07 08:19 PM Re: Parsing information from an excel document
Lonkero Lonkero KiX Master Guru Registered: 2001-06-05 Posts: 22346 Loc: OK	and you want certain usernames or all of them? _________________________ ! download KiXnet
Top

#130775 - 2004-12-07 08:35 PM Re: Parsing information from an excel document
NTDOC NTDOC Administrator Registered: 2000-07-28 Posts: 11634 Loc: Space	Quote: my level of scripting (novice). Josh, Unless one of the more advanced scripters here takes it on for you the answer is no. There is no easy built-in method with probably any script language. Can it be done, yes it can be done, but it is not a novice level task. If someone does decide to help you completely with this task then you will need to be more specific on all the details and show examples. Les asked you a few questions and you only maybe answered one or two. Please provide all the details asked, also the version of KiXtart you're using. Will it be done locally on your system or some other system, does it need to be done remotely? What version of Excel? Please show examples of what is in the columns and what you want removed, etc...
Top

#130776 - 2004-12-08 10:26 AM Re: Parsing information from an excel document
Richard H. Richard H. Administrator Registered: 2000-01-24 Posts: 4946 Loc: Leatherhead, Surrey, UK	As the boys have said earlier, the process is not simple. This example is as simple as I could make it, it returns the values in column "A" of the active worksheet in workbook "c:\temp\demo.xls" This demo assumes that the first empty cell is the end of the list (otherwise all possible cells in column A are returned). Code: Break ON $sMyDocument="C:\temp\demo.xls" ; Create Excel application $oExcel=CreateObject("Excel.Application") ; Uncomment the next line to make it visible ; $oExcel.Visible = 1 ; Load the demo document $oWorkbook=$oExcel.Workbooks.Open($sMyDocument) ; Get the active worksheet $oWorksheet=$oWorkbook.ActiveSheet ; Iterate the cells in column A, stopping on the first blank cell For Each $oCell in $oWorksheet.Columns("A:A").Cells $iCounter=$iCounter+1 $sText=$oCell.Text If $sText="" Goto "CellLoopDone" EndIf $iCounter " " $sText ? Next :CellLoopDone ; Clean up $oExcel.Quit() $oExcel=0 Exit 0 If anyone can think of a nicer way to break out of the collection iteration I'd be interested.
Top

#130777 - 2004-12-08 11:06 AM Re: Parsing information from an excel document
Jochen Jochen KiX Supporter Registered: 2000-03-17 Posts: 6380 Loc: Stuttgart, Germany	Nicer means without GOTO ? _________________________
Top

#130778 - 2004-12-08 12:02 PM

Re: Parsing information from an excel document

Jochen Offline

KiX Supporter

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany

Code:

Break ON



$sMyDocument="C:\temp\demo.xls"



; Create Excel application

$oExcel=CreateObject("Excel.Application")



; Uncomment the next line to make it visible

; $oExcel.Visible = 1



; Load the demo document

$oWorkbook=$oExcel.Workbooks.Open($sMyDocument)



; Get the active worksheet

$oWorksheet=$oWorkbook.ActiveSheet



; Iterate the cells in column A, stopping on the first blank cell



do

    $iCounter=$iCounter+1

    $sText=$oWorkSheet.Range("A"+$iCounter).Text

    If $sText

        $iCounter " " $sText ?

    EndIf

until $sText = ""



; Clean up

$oExcel.Quit()

$oExcel=0



Exit 0

First try ... somehow had to use .Range as .Cell(s) denied to work

Edited by Jochen (2004-12-08 12:10 PM)

_________________________

Top

#130779 - 2004-12-08 12:41 PM Re: Parsing information from an excel document
Lonkero Lonkero KiX Master Guru Registered: 2001-06-05 Posts: 22346 Loc: OK	sure it denied as you used it little wrong... _________________________ ! download KiXnet
Top

#130780 - 2004-12-08 01:15 PM Re: Parsing information from an excel document
Jochen Jochen KiX Supporter Registered: 2000-03-17 Posts: 6380 Loc: Stuttgart, Germany	ja, obviously ... _________________________
Top

#130781 - 2004-12-08 01:57 PM

Re: Parsing information from an excel document

Lonkero Offline

KiX Master Guru

Registered: 2001-06-05
Posts: 22346
Loc: OK

I would try something like:
Code:


$sMyDocument="C:\temp\demo.xls"

$table=1

$column=2

 

$oE=CreateObject("Excel.Application")

$oE.Workbooks.Open($sMyDocument).

 

for $row=1 to $oE.sheets($table).rows.count

 $val = $oE.sheets($table).cells($row,$column).value

 if len($val)

  $vals = $vals + chr(10) + $val

 endif

next

$oExcel.Quit

 

$vals=split(substr($vals,2),chr(10))

for each $username in $vals

 ? $username

next

_________________________
!

download KiXnet

Top

#130782 - 2004-12-08 02:48 PM Re: Parsing information from an excel document
Kdyer Kdyer KiX Supporter Registered: 2001-01-03 Posts: 6241 Loc: Tigard, OR	What happened to READEXCEL() or READEXCEL2() UDFs? Kent _________________________ Utilize these resources: UDFs (Full List) KiXtart FAQ & How to's
Top

#130783 - 2004-12-08 02:56 PM Re: Parsing information from an excel document
Lonkero Lonkero KiX Master Guru Registered: 2001-06-05 Posts: 22346 Loc: OK	nothing. for someone who does not know, there are two udfs that does excel "bulk" reading. you can find them by clicking on the UDF link on my sig. _________________________ ! download KiXnet
Top

#130784 - 2004-12-08 03:02 PM Re: Parsing information from an excel document
Kdyer Kdyer KiX Supporter Registered: 2001-01-03 Posts: 6241 Loc: Tigard, OR	A simple example is fine.. I guess we have always tried to direct folks over to the UDF section rather than re-invent the wheel.. Kent _________________________ Utilize these resources: UDFs (Full List) KiXtart FAQ & How to's
Top

#130785 - 2004-12-08 03:11 PM Re: Parsing information from an excel document
Les Les KiX Master Registered: 2001-06-11 Posts: 12734 Loc: fortfrances.on.ca	Well... Josh said "I have searched through the site and can't find anything that is at my level of scripting (novice)" so would ahve to assume that the UDF did not meet his requirement of "simple". _________________________ Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.
Top

#130786 - 2004-12-08 03:54 PM Re: Parsing information from an excel document
Richard H. Richard H. Administrator Registered: 2000-01-24 Posts: 4946 Loc: Leatherhead, Surrey, UK	Quote: Nicer means without GOTO ? Yup. I want to keep the FOR EACH, but avoid the GOTO.
Top

#130787 - 2004-12-08 04:19 PM Re: Parsing information from an excel document
Lonkero Lonkero KiX Master Guru Registered: 2001-06-05 Posts: 22346 Loc: OK	so my code was just fine. _________________________ ! download KiXnet
Top

#130788 - 2004-12-08 05:13 PM Re: Parsing information from an excel document
Richard H. Richard H. Administrator Registered: 2000-01-24 Posts: 4946 Loc: Leatherhead, Surrey, UK	Quote: so my code was just fine. Yes and No There is nothing wrong with your code per se, however you switched to a FOR..TO - I specifically wanted to keep the collection enumeration (FOR...EACH...IN).
Top

#130789 - 2004-12-08 05:20 PM Re: Parsing information from an excel document
Les Les KiX Master Registered: 2001-06-11 Posts: 12734 Loc: fortfrances.on.ca	Quote: I want to keep the FOR EACH, but avoid the GOTO Pretty much impossible, IMHO unless you make it into its own UDF and use EXIT 0. _________________________ Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.
Top

#130790 - 2004-12-09 02:36 PM Re: Parsing information from an excel document
Jochen Jochen KiX Supporter Registered: 2000-03-17 Posts: 6380 Loc: Stuttgart, Germany	Quote: Quote: Nicer means without GOTO ? Yup. I want to keep the FOR EACH, but avoid the GOTO. Baah!! What's wrong with DO-UNTIL then ??? _________________________
Top

Page 1 of 2

Previous Topic View All Topics

Index Next Topic

Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart

Hop to:

Shout Box

Search the board with:
superb Board Search
or try with google:

	Web kixtart.org