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
Load the dbf file
Connect to the database
Do the bulk insert
Disconnect and exit
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
'Setup a variable to make it easier to reference the dbase data:
' Dim datasheet As Worksheet
Set datasheet =
'Now make sure SQL*XL is loaded. Load it again and ignore any errors
On Error Resume Next
On Error GoTo 0
'Connect to the database
SQLXL.database.Connect username:="scott", password:="tiger", _
'Insert the data
SQLXL.InsertRecordset Table:="your_table_name", _
Copyright (C) 1995-2014 Linker IT
Software BV. All Rights Reserved. Oracle is a registered trademark of
Oracle corporation. Excel and Office are registered trademarks of
Microsoft corporation. Other names appearing on the site may be trademarks
of their respective owners. Software,
files, documents, articles and other material are provided
"as is" and without warranties as to performance or mechantability or
any other warranties whether expressed or implied. No
warranty of fitness for a particular purpose is offered.