Data format - text file or Excel

HCL Domino Domino Forum
1

Hi all,

I need to write a lotus script scheduled agent import data from a file to Lotus notes database. The customer have sent me sample Excel file. I have not imported form Excel before and though text file would be better. Not sure which one would be better approch Excel or Text?

Any suggestions would be appreciated and Thanks in advance.

2

Subject: data format - text file or Excel

Excel import is easy. Look in the sandbox and you’ll find some examples.

Here’s a sample code:

Import Truck data

Dim xlFilename As String

xlFilename = "c:\MaintReq0806.xls" '// This is the name of the Excel file that will be imported



Dim session As New NotesSession

Dim db As NotesDatabase

Dim view As NotesView

Dim doc As NotesDocument

Set db = session.CurrentDatabase

Set doc = New NotesDocument(db)

Dim One As String



Dim row As Integer

Dim written As Integer



                                   '// Next we connect to Excel and open the file. Then start pulling over the records.

Dim Excel As Variant

Dim xlWorkbook As Variant

Dim xlSheet As Variant

Print "Connecting to Excel..."

Set Excel = CreateObject( "Excel.Application" )

Excel.Visible = False '// Don't display the Excel window

Print "Opening " & xlFilename & "..."

Excel.Workbooks.Open xlFilename '// Open the Excel file

Set xlWorkbook = Excel.ActiveWorkbook

Set xlSheet = xlWorkbook.ActiveSheet



                                   '// Cycle through the rows of the Excel file, pulling the data over to Notes

Goto Records

Print "Disconnecting from Excel..."

xlWorkbook.Close False '// Close the Excel file without saving (we made no changes)

Excel.Quit '// Close Excel

Set Excel = Nothing '// Free the memory that we'd used

Print " " '// Clear the status line

Records:

row = 0 '// These integers intialize to zero anyway

written = 0



Print "Starting import from Excel file..."



Do While True

Finish:

	With xlSheet

		row = row + 1

		Set view = db.GetView("Equipment Listing")

		Set doc = db.CreateDocument '// Create a new doc 

		doc.Form = "Equipment Listing"

		

		doc.Asset = .Cells( row, 1 ).Value

		doc.EquipGroup = .Cells(row, 2 ).Value

		doc.PMSchLast = .Cells(row, 3).Value

		doc.Region = .Cells(row, 4 ).Value

		doc.Store = .Cells(row, 5).Value

		doc.VIN = .Cells( row, 6).Value

		doc.Make  = .Cells(row, 7).Value

		doc.Model = .Cells( row, 8).Value

		doc.Year = .Cells(row, 9).Value

		doc.BodyType = .Cells(row, 10).Value

		doc.Fuel = .Cells(row, 11).Value

		doc.Trans = .Cells(row, 12).Value

		doc.TareWt = .Cells(row, 13).Value

		doc.GVW = .Cells(row, 14).Value

		doc.NumAxle = .Cells(row, 15).Value

		doc.StdTireSize = .Cells(row, 16).Value

		doc.PmbAsem = .Cells(row, 17).Value

		doc.PmbAsmDate = .Cells(row, 18).Value

		doc.Status = .Cells(row, 19).Value

		doc.License = .Cells(row, 20).Value

		doc.LicenseState = .Cells(row, 21).Value

		doc.LicExpir = .Cells(row, 22).Value

		doc.MVIExpir = .Cells(row, 23).Value

		doc.SpecialE = .Cells(row, 24).Value

		doc.Dateinfleet = .Cells(row, 25).Value

		doc.Dateoutfleet = .Cells(row, 26).Value

		doc.Body = .Cells(row, 27).Value

		

		

		Call doc.Save( True, True ) '// Save the new doc

		

		written = written + 1

		

		If written = 790 Then

			'Print written	

			'Print written & "record stopped at..", doc.SWEPO(0), doc.SWEORDER(0),doc.SWEORDERDATE(0),doc.ITEMNUMBER(0)

			'ans$ = Inputbox$("Do you want to continue, Y/N?")

			'If  ans$ = "Y" Or ans$ ="y" Then

			'	Goto Finish

			'Else

			Goto Done

		'End If

		End If

’ End If

	End With

Loop

Return

Done:

RTF:

3

Subject: data format - text file or Excel

I would prefer text data, simply because I wouldn’t want to install Excel on the server. It could also be that your server is running on something other than Windows, in which case you could not install Excel on the server.

/Peter