Excel Data Import Using Script

After spending a great deal of time to import information from Excel over the years, I wrote the following code to pull data from Excel files. The code allows me to get the users data into a blank database where I can manipulate it. The script assumes that the values in the first row are the names to be used for the field names and that the data begins on the second row. It continues to search for columns until it finds a blank cell...

in the first row. After finding which columns to use it adds a document for each row in the Excel file until it finds a blank row. Hope you find the following useful. ============================= Jason Goodloe jgoodloe@evolvetech.com 'Re-formated Version 'Multi-platform: Option Public Option Explicit 'WIN32 Declare Function W32_NEMGetFile Lib "nnotesws" Alias "NEMGetFile" ( wUnk As Integer, Byval szFileName As String, Byval szFilter As String, Byval szTitle As String ) As Integer 'WIN16 Declare Function W16_NEMGetFile Lib "_nem" Alias "NEMGetFile" ( wUnk As Integer, Byval szFileName As String, Byval szFilter As String, Byval szTitle As String ) As Integer 'OS2 Declare Function OS2_NEMGetFile Lib "inotesws" Alias "NEMGetFile" ( wUnk As Integer, Byval szFileName As String, Byval szFilter As String, Byval szTitle As String ) As Integer 'use nnotesws for Win95 and WinNT, inotesws for OS/2, and _nem for Win16 Sub Initialize %REM ****************************************************************************** OverView: This Function deletes all of the documents in the current database and then re-populates the database based on data contained in the excel file. The column heading row is used for the field names in the notes doucments. Input: User must supply the path and file name for the excel file Results: Creates a Notes document for each row in the excel spreadsheet, excluding the header row Called By: This function is run from the agent list Assumptions: 1. Data is on one worksheet only and that this worksheet is the first worksheet in the workbook 2 Worksheet uses the first row as a column heading 3. The first blank column header is assumed to be the last column to read data in the file 4. The first blank row is assumed to be the last row of the file 5. Field names are the first 20 spaces of the column heading excluding spaces ****************************************************************************** M O D I F I C A T I O N H I S T O R Y ****************************************************************************** Date BY Description ----------------- -------------------- ------------------------------ 09/22/1999 J.Goodloe Initial Development 4.6.2 ****************************************************************************** %END REM Dim S As New NotesSession Dim db As NotesDatabase Dim Doc As NotesDocument Dim item As NotesItem Dim strCellRange$ Dim varCellValue As Variant 'File Dialog Dim strFileName As String*256 Dim strTitle$ Dim strFilter$ 'Excel Application Dim varExcelApp As Variant Dim varExcelWB As Variant Dim varExcelSheet As Variant 'Valid columns list Dim strExcelCodeList List As String Dim FirstletterCode& Dim LastletterCode& 'Iterate Rows Dim intExcelRow% Dim bIsRowBlank% Dim bRowValueFound% Set db = S.CurrentDatabase 'File Dialog strFileName = Chr(0) strTitle$ = s.Commonusername & ", select your database NOW" strFilter$ = "MS Excel Files|*.xls|All Files|*.*|" 'Use this format for ANY file type If IsDefined ("WIN32") Then If W32_NEMGetFile (0, strFileName, strFilter$, strTitle$) <> 0 Then strFileName = strFileName & |"| 'We need to do this because the return is a NULL terminated string. End If Elseif IsDefined ("WIN16") Then If W16_NEMGetFile (0, strFileName, strFilter$, strTitle$) <> 0 Then strFileName = strFileName & |"| End If Elseif IsDefined ("OS2") Then If OS2_NEMGetFile (0, strFileName, strFilter$, strTitle$) <> 0 Then strFileName = strFileName & |"| End

This was first published in November 2000

Dig deeper on Domino Resources - Part 4

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchWinIT

Search400

  • iSeries tutorials

    Search400.com's tutorials provide in-depth information on the iSeries. Our iSeries tutorials address areas you need to know about...

  • V6R1 upgrade planning checklist

    When upgrading to V6R1, make sure your software will be supported, your programs will function and the correct PTFs have been ...

  • Connecting multiple iSeries systems through DDM

    Working with databases over multiple iSeries systems can be simple when remotely connecting logical partitions with distributed ...

SearchEnterpriseLinux

SearchVirtualDataCentre.co.UK

Close