×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Script To Extract Data

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.

RE: Script To Extract Data

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.  

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close