Automate the daily insertion of a dbase file into Oracle using SQL*XL

SQL*XL home

This topic was created to ilustrate how SQL*XL can be used to load external data - in this example a dbase file - into an Oracle database. The solution presented is a non-interactive solution as it will be kicked off from a batch file. This is useful when external programs may kick the process off like a batch scheduler or indeed an operator using an MS-DOS window

Step 1: create a batch file
In a text editor create a simpel text file to contain the following text:

Save the file to conversion.bat. This file will be used to start the process. It will open Excel and load the workbook conversion.xls. In this workbook we will create a little bit of code to do the import.

Step 2: create a new workbook
A new workbook is needed to get an environment to call SQL*XL and to import the dbase file into Oracle.
We will use the Workbook_Open method to execute our code when the workbook is opened. Our code will look something like this:

There is one thing that you must do: Add a reference to SQLXL in the new spreadsheet. This is done by entering the Visual Basic environment (Tools | Macros | Visual Basic). Then select the correct project - in our case VBAProject(conversion.xls). Execute Tools | References and use the Browse button to add a reference to sqlxl.xla. That's it! You're ready to unleash the functionality of SQL*XL!

The code that goes into the conversion.xls project is below. Since it is so small I have added the comments to the code. Simply cut and past this into your conversion.xls file. Note that you will need to change the database connection details and the table details the will collect the data from the dbase file.

Private Sub Workbook_Open()

'Open the dbase file
Workbooks.Open "c:\data\my_dbase_file.dbf"
'
'Setup a variable to make it easier to reference the dbase data:
'
Dim datasheet As Worksheet
Set datasheet = Workbooks("my_dbase_file.dbf").Sheets(1)
'
'Now make sure SQL*XL is loaded. Load it again and ignore any errors
'
On Error Resume Next
Workbooks.Open sqlxl_xla
On Error GoTo 0
'
'Connect to the database
'
SQLXL.Database.ConnectionType = litOO4O
SQLXL.database.Connect username:="scott", password:="tiger", _

DBAlias:="my_db"

'
'Insert the data
'
SQLXL.InsertRecordset Table:="your_table_name", _

Columns:="col1,col2,col3,col4,col5,col6", _
DataRange:=datasheet.Range("A2:F100"), _
PromptOnError:=False, SortToStatus:=False, _
CommitFrequency:=5, Orientation:=1, _
Silent:=True, Feedback:=True

'
'All done. Disconnect and exit
SQLXL.Database.DisConnect
Application.DisplayAlerts = False
Application.Exit

End Sub

See also: