Script To Extract Data

sabavno · Sep 10, 2003

Hi,

I was wondering if someone has already done that before and could share a code with me.

I need to export all data from Notes to SQL2000.
I know that one of the ways is to create a view that will include all required fields, then export that view.

However, the document has many fields and I don't want to create a view with 60 columns in it.

So, what I am interested in is a code that will loop through all documents and extract all fields (if possible it would loop through fields names collection, so I would not have to hardcode the names of the fields) and then dump the data whether to Excel file or if possible to Access or SQL2000 table.

Any help would be greatly appreciated.

Thanks.

lespaul · Sep 10, 2003

Here's a piece of script you can modify that opens excel, loops through all the items in a document and outputs the fieldname and the value.

Code:

Sub Click(Source As Button)
	
	Dim workspace As New NotesUIWorkspace
	Set s = New Notessession
	Set db = s.CurrentDatabase
	Set doc = workspace.CurrentDocument.Document
	
	Dim j As Integer
	
	'opens excel workbook template in the background from the O drive 
	Set excelapp = createobject(&quot;excel.application&quot;)
	'excelapp.visible = True	
	excelapp.visible = False
	excelapp.Workbooks.add(&quot;O:\PreTrialForms\InterviewReportT&quot;)
	Set exceldta = excelapp.Workbooks(&quot;InterviewReportT1&quot;).Worksheets(&quot;Data&quot;)
	Set excelsht = excelapp.Workbooks(&quot;InterviewReportT1&quot;).Worksheets(&quot;InterviewReport&quot;)	
	excelapp.DisplayAlerts = False
	j = 1
	'collects all the items in the currently open document and copies to excel data sheet
	Forall i In doc.Items
		If i.text <> &quot;&quot; Then
			exceldta.cells(j,1).value = Cstr(i.name)
			exceldta.cells(j,2).value = Cstr(i.text)
			j = j+1
		End If
	End Forall	
	'call excel process to format report
	Call excelsht.auto_open
	'print worksheet
'excelsht.printout
	'excelsht.printout
	'set excel to close without prompting to save
	excelapp.DisplayAlerts = False
	'close excel
	excelapp.workbooks(1).close
	excelapp.quit
	
End Sub

I would add a section to loop through all the documents in the database, then for each document, loop through to get the information you want.

HTH

leslie

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Script To Extract Data

sabavno

Programmer

lespaul

Programmer

Similar threads

Part and Inventory Search

Sponsor