David Schmalzer
asked on
Excel Import Opinion
I have seen many posts and code on importing an excel spreadsheet, however not being a script guy myself, I would like an opinion on the best, simplest code to use. And in that opinion, show me where to put my column data which will be(DEPT,FP,S_A,IPL_CODE,BA TCH,DESCRI PTOR,QTY,G ,START_DAT E,DUE_DATE ,TYPE).
I want to use it in a button in a view or an agent, whatever would work best. I want it to overwrite everything in the view.
Also, when using these excel import scripts, do I have to make sure column widths are the same in the spreadsheet like when I am importing from a lotus spreadsheet?
I want to use it in a button in a view or an agent, whatever would work best. I want it to overwrite everything in the view.
Also, when using these excel import scripts, do I have to make sure column widths are the same in the spreadsheet like when I am importing from a lotus spreadsheet?
ASKER
Ok, now where in the above code do I put the column-field names. Just show me one, and I can do the rest. Thanks.
ASKER
Oh, and do the colum widths have to match between the spreadsheet and the view?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I found this code and I got it to work. The problem is that it only imported 30 records when there are 61 rows in the spreadsheet. Any idea why? Here is the code I am using:
Dim ws As New NotesUIWorkSpace
Dim ses As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim App As Variant, Wbook As Variant, WSheet As Variant
Dim RetVal As Variant
Dim XLfile As String
Dim row As Long
If Dir$(defDir,16) = "" Then
Mkdir defDir
End If
'Choose Excel file
XLfile = "o:\data\spreadsheets\sche dwet.xls"
'get Excel Object
Print "Preparing to import..."
Set App = CreateObject("Excel.Applic ation")
If App Is Nothing Then
Msgbox "You do not have Excel installed.", 0 + 16, "Import from excel"
Exit Sub
End If
App.Visible = False
App.Workbooks.Open XLFile
Set Wbook = App.ActiveWorkbook
Set WSheet = Wbook.ActiveSheet
Set db = ses.CurrentDatabase
Set view = db.GetView("YieldImport")
Dim importCnt As Long
importCnt = 0
Print "Importing..."
Dim fileNum As Integer
Dim fileName As String
fileNum = Freefile()
fileName = defdir & "\Excel_Import_Log" & Format(Today,"ddmmyy") & ".txt"
Open fileName For Output As filenum
row = 2 '<--- skip the column header use 1 if your file has no column header
While Trim(Cstr(WSheet.Cells(row , 1).Value)) <> ""
'-----> check if doc already exists
Dim dummyDoc As NotesDocument
Call view.Refresh
Set dummyDoc = view.GetDocumentByKey(Trim (Cstr(WShe et.Cells(r ow, 1).Value)))
If dummyDoc Is Nothing Then
'-----> check if doc already exists
Set doc = db.CreateDocument
doc.form = "YieldSheet1"
doc.dept = Trim(Cstr(WSheet.Cells(row , 1).Value))
doc.fp = Trim(Cstr(WSheet.Cells(row , 2).Value))
doc.s_a = Trim(Cstr(WSheet.Cells(row , 3).Value))
doc.ipl_code = Trim(Cstr(WSheet.Cells(row , 4).Value))
doc.batch = Trim(Cstr(WSheet.Cells(row , 5).Value))
doc.descriptor = Trim(Cstr(WSheet.Cells(row , 6).Value))
doc.qty = Trim(Cstr(WSheet.Cells(row , 7).Value))
doc.g = Trim(Cstr(WSheet.Cells(row , 8).Value))
doc.startdate = Trim(Cstr(WSheet.Cells(row , 9).Value))
doc.due_date = Trim(Cstr(WSheet.Cells(row , 10).Value))
doc.type = Trim(Cstr(WSheet.Cells(row , 11).Value))
doc.save True, True
importCnt = importCnt + 1
Else
Print #fileNum, "Record already exists :" & Trim(Cstr(WSheet.Cells(row , 1).Value))
End If
row = row + 1
Wend
Close fileNum
App.Application.Quit
Set App = Nothing
Set Wbook = Nothing
Set Wsheet = Nothing
Msgbox "Successfully imported " & Cstr(importCnt) & " record(s).", 0 + 64, "Import from excel"
Dim ws As New NotesUIWorkSpace
Dim ses As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim App As Variant, Wbook As Variant, WSheet As Variant
Dim RetVal As Variant
Dim XLfile As String
Dim row As Long
If Dir$(defDir,16) = "" Then
Mkdir defDir
End If
'Choose Excel file
XLfile = "o:\data\spreadsheets\sche
'get Excel Object
Print "Preparing to import..."
Set App = CreateObject("Excel.Applic
If App Is Nothing Then
Msgbox "You do not have Excel installed.", 0 + 16, "Import from excel"
Exit Sub
End If
App.Visible = False
App.Workbooks.Open XLFile
Set Wbook = App.ActiveWorkbook
Set WSheet = Wbook.ActiveSheet
Set db = ses.CurrentDatabase
Set view = db.GetView("YieldImport")
Dim importCnt As Long
importCnt = 0
Print "Importing..."
Dim fileNum As Integer
Dim fileName As String
fileNum = Freefile()
fileName = defdir & "\Excel_Import_Log" & Format(Today,"ddmmyy") & ".txt"
Open fileName For Output As filenum
row = 2 '<--- skip the column header use 1 if your file has no column header
While Trim(Cstr(WSheet.Cells(row
'-----> check if doc already exists
Dim dummyDoc As NotesDocument
Call view.Refresh
Set dummyDoc = view.GetDocumentByKey(Trim
If dummyDoc Is Nothing Then
'-----> check if doc already exists
Set doc = db.CreateDocument
doc.form = "YieldSheet1"
doc.dept = Trim(Cstr(WSheet.Cells(row
doc.fp = Trim(Cstr(WSheet.Cells(row
doc.s_a = Trim(Cstr(WSheet.Cells(row
doc.ipl_code = Trim(Cstr(WSheet.Cells(row
doc.batch = Trim(Cstr(WSheet.Cells(row
doc.descriptor = Trim(Cstr(WSheet.Cells(row
doc.qty = Trim(Cstr(WSheet.Cells(row
doc.g = Trim(Cstr(WSheet.Cells(row
doc.startdate = Trim(Cstr(WSheet.Cells(row
doc.due_date = Trim(Cstr(WSheet.Cells(row
doc.type = Trim(Cstr(WSheet.Cells(row
doc.save True, True
importCnt = importCnt + 1
Else
Print #fileNum, "Record already exists :" & Trim(Cstr(WSheet.Cells(row
End If
row = row + 1
Wend
Close fileNum
App.Application.Quit
Set App = Nothing
Set Wbook = Nothing
Set Wsheet = Nothing
Msgbox "Successfully imported " & Cstr(importCnt) & " record(s).", 0 + 64, "Import from excel"
It hit a blank value in column 1 of the 31 row.
ASKER
I got it. I decided to put the batch as the first column because that data is always different. It was picking up duplicate data in column 1 and not creating new records for the duplicates. Changed the column order in the spreadsheet to match and updated the code.
ASKER
I am going to post another question to follow up on this. There is something else I want to accomplish, but it is worthy of being a new question.
ASKER
Thanks for your help lately.
Not a problem, whatever works for you. If you get a chance, do explore the module import.. it's a bit more robust than the one you selected.
ASKER
I will explore the other module, but since time is of the essence(when is it not?), I will use this one for now. And now part 2 question coming.
http://searchdomino.techtarget.com/tip/1,289483,sid4_gci822343,00.html?track=NL-47&ad=541560 is good, and easy to follow.
This one, imports the excel file and creates a check view of the imported stuff. The only thing you need to do is create the Notes form, and include your fields: Dept, FP, S_A, IPL_CODE, BATCH.. etc. on the form. (Or if you never want to view the FORM, just create the form with minimal fields, createdby createddate, form field. The code will create the form and display it in the Excel Import Check view.
Option Public
Option Declare
'Important set these values to your environment..........
Const str_VIEWNAME="Excel Import Check"
Const str_FORM = "Refresh Tracking" 'Change this to your form name
Const str_IMPORTFILE="C:\assettr
Const max_RECORDS=100 'Set this to the maximum number of records you want to loop
Const max_SKIPROWS=10 'Set this to the number of blank rows you want to skip before you assume
'the rest of the worksheet is blank
'I am using these to concatentate three column values into a unique key to avoid importing duplicates
'
Const chk_COL1=2 'Asset is excel column 2
Const chk_COL2=3 'userid is excel column 3
Const chk_COL3=4 'office_id is excel column 4
Sub Initialize
'Clear Lotus Notes View Of Records
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim vColl As NotesViewEntryCollection
Dim max_Columns As Integer
Set db = session.currentdatabase
Set view = db.getview(str_VIEWNAME)
If view Is Nothing Then
Set view = CreateChkView(session)
If view Is Nothing Then
Msgbox "Sorry, unable to create the check view to avoid importing duplicate documents"
Exit Sub
End If
End If
Dim xlFilename As String
'IMPORT FILENAME..................
xlFilename= str_IMPORTFILE
Dim doc As NotesDocument, titles As Variant
Dim row As Integer, col As Integer, k As Integer
Dim written As Integer
On Error Goto Handle_Error
'First Connect to Excel and see if there are any records to import....................
Dim Excel As Variant
Dim xlWorkbook As Variant
Dim xlSheet As Variant
Print "Connecting to Excel..."
Set Excel = CreateObject( "Excel.Application" )
If Excel Is Nothing Then
Print "Unable to open the excel object..."
Exit Sub
End If
Excel.Visible = False '// Don't display the Excel window
Excel.Workbooks.Open xlFilename '// Open the Excel file
Print "Opening " & xlFilename & "..."
Excel.Workbooks.add
Excel.Workbooks(1).Workshe
Set xlWorkbook = Excel.Workbooks(1)
Set xlSheet = Excel.Workbooks(1).Workshe
Dim chk As String, skiprow As Integer
Records:
Dim FieldNames(255) As String, tmpStr As String
row = 1 '// These integers intialize to zero anyway
col = 1
written =0
Print "Starting import from Excel file..."
chk="xxxx"
'GET FIELD NAMES IN FIRST ROW
With xlSheet
For k = 1 To 255
tmpStr =Format(.cells(row,k).valu
If Len(Trim(tmpStr))>0 Then
FieldNames(k) = tmpStr
Else
Select Case K
Case 1
Msgbox "First Row and Column in the worksheet must contain the Field Titles",,"Column Title Missing"
Exit Sub
Case Else
max_COLUMNS = k-1
Exit For
End Select
End If
Next
End With
Row = 2
Do While (row<=max_RECORDS)
With xlSheet
chk =Lcase(Trim(Format(.cells(
Format(.cells(row,chk_COL2
Format(.cells(row,chk_COL3
If Len(chk)<0 Then
skiprow = skiprow +1
Goto NextRow
End If
Select Case row
Case 1
'Header Row is Titles
Case Else
If Len(chk)>0 Then
'Check to see if the document has been created
Set doc = view.GetDocumentByKey(chk,
If Not doc Is Nothing Then Goto NextRow
Set doc = New NotesDocument(db)
doc.Form = str_FORM 'Form name
'Update to Fields
For k = 1 To max_COLUMNS
Call doc.ReplaceItemValue(Field
Next
doc.Save True, False, True
Print " Creating Record..." + Cstr(row)
written = written +1
'If we find a record, then set the skiprow back to 0 - it was just a blank row
skiprow = 0
Set doc = Nothing
End If
End Select
End With
NextRow:
row= row+1
If skiprow =>max_SKIPROWS Then
Print Cstr(max_SKIPROWS) + " blank rows reached... assuming the rest of the worksheet is blank..."
Exit Do
End If
Loop
Print "Disconnecting from Excel..."
Excel.activeworkbook.close
Excel.Quit '// Close Excel
Set Excel = Nothing '// Free the memory that we'd used
view.Refresh
Exit Sub
Handle_Error:
If Err = 213 Then
Print "Import File not found..."
Err = 0
Exit Sub
End If
On Error Goto 0
If Not xlworkbook Is Nothing Then
Excel.activeworkbook.close
Excel.Quit '// Close Excel
Set Excel = Nothing '// Free the memory that we'd used
End If
Exit Sub
End Sub
Sub Terminate
End Sub
Function CreateChkView(thisSession As NotesSession) As NotesView
Dim tmpView As NotesView, columnCount As Integer
Dim strSelect As String, strCOL As String
ColumnCount = 0
strSelect = {SELECT ((Form = "} + str_FORM + {"));@All}
strCOL={@LowerCase(@Text(A
Set tmpView = thisSession.currentdatabas
If Not tmpView Is Nothing Then
Dim col As NotesViewColumn, Col1 As NotesViewColumn, Col2 As NotesViewColumn, Col3 As NotesViewColumn
Set col = tmpView.CreateColumn(1, "KEY", strCOL)
Col. IsSorted = True
Set col1 = tmpView.CreateColumn(2, "Asset", "Asset")
Set col2 = tmpView.CreateColumn(3, "UserID", "userid")
Set col3 = tmpView.CreateColumn(4, "Office ID", "office_id")
Set CreateChkView = tmpView
End If
End Function
--------------------------
As long as you have the form with the field names = col names, then you can format as you see fit and add more columns.
Regards!