Script To Extract Data
Script To Extract Data
(OP)
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.
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.
Talk To Other Members
RE: Script To Extract Data
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("excel.application")
'excelapp.visible = True
excelapp.visible = False
excelapp.Workbooks.add("O:\PreTrialForms\InterviewReportT")
Set exceldta = excelapp.Workbooks("InterviewReportT1").Worksheets("Data")
Set excelsht = excelapp.Workbooks("InterviewReportT1").Worksheets("InterviewReport")
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 <> "" 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