Saturday, October 4, 2008

Accessing dBASE tables with MS SQL

For years the dBASE (dbf) table format had been very popular to store data in. Many third party applications included ways to import and export to the dBASE (dbf) file type.

This stopped at dBASE 5.0. Since then the dBASE table format has evolved and is now at level 7. The sad news is that third party application developers have not included the ability to import and export to this format.

This might be as a result of third party vendors not considering dBASE as a viable alternative or that the authors of dBASE have just not seen a need to write a driver to access their data.

Either way the need to access dBASE data has dwindled over the years. Never the less, there are still large amounts of data still stored in this format. So, if you have a place where data is stored, then you should be able to retrieve that data.

With many moving over to the more robust, faster, secure database servers like MSSQL, accessing this dBASE data becomes increasingly difficult.

How then do you access your data in dbf format from MSSQL, having the ability to join that table to your current data? How can one import dbf data?

There might be many ways to achieve this, one of which you can export your data to a format that can easily be imported into MSSQL. This can be a labour intensive operation, especially if your dbf data is still live and changing continuously.

Fortunately, MSSQL has a few methods of accessing other data. Two of which are:

1. Using OpenRowset.
2. Creating a linked server.


Both methods can be used to access live data, negating the need for an import facility, and also allowing you to do normal joins and other SQL stuff.

OpenRowset

The OPENROWSET feature in SQL Server and MSDE provides a fast and easy way to open an OLE DB compatible data source, such as an Excel sheet or dBASE data, directly from your SQL script. Coupled with the "SELECT * INTO" command, the OPENROWSET feature can import data from your other data sources into a table in SQL Server or MSDE. You may even want to just use the data in a view or join.

Openrowset should be used in cases when access in not required on a regular basis, ie. as in adhoc queries. When data is accessed several times, a linked server should then be used.

The OpenRowset function in its simplest form carries the following syntax.

OPENROWSET (provider_name,provider_string,query_syntax)

So to access dBASE we would use the following syntax:

OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};
DBQ=[Path to data];
SourceType=DBF',
SQL Query)

This assuming you have at least mdac2.6 installed. So we could issue the following:

OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};
DBQ=D:\dbase Work\R3\Santam\Data;
SourceType=DBF',
'SELECT top 1000 * FROM gl')


This returned over 14000 rows without a query plan (first run) in 2 seconds. Once the query plan is stored it is a second or less.

Now you can use any SQL query, (Note: I haven't tested every single query), as in:

SELECT *
FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};
DBQ=D:\dbase Work\R3\Santam\Data;
SourceType=DBF',
'SELECT * FROM gl where GLcode >30000 order by date desc')


How about a join within the openrowset query:

SELECT *
FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};
DBQ=D:\dbase Work\R3\Santam\Data;
SourceType=DBF',
'SELECT * FROM gl x
inner join glacc y on y.glm_accountglacc = x.glcode
')

How about a join of two openrowset queries. Remember that the data source does not have to be dBASE. You can access Excel (tested up to 2003), text files etc. But we are using dBASE here just to show you the ease of it:

SELECT *
FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};
DBQ=D:\dbase Work\R3\Santam\Data;
SourceType=DBF',
'SELECT * FROM gl x
inner join glacc y on y.glm_accountglacc = x.glcode
') a
inner join
OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};
DBQ=D:\dbase Work\R3\Santam\Data;
SourceType=DBF',
'SELECT * FROM glstaccount') b
on a.glcode = b.glkey

Now what about adding your own newly created data in MSSQL to the pot. So you can now join dBASE data, what about joining that to a SQL table or two.

SELECT *
FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};
DBQ=D:\dbase Work\R3\Santam\Data;
SourceType=DBF',
'SELECT * FROM gl x
inner join glacc y on y.glm_accountglacc = x.glcode
') a
inner join
OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};
DBQ=D:\dbase Work\R3\Santam\Data;
SourceType=DBF',
'SELECT * FROM glstaccount') b
on a.glcode = b.glkey
inner join dbo.Insyear c on a.Prop = c.Period

So as you can see, your imagination can run wild. No longer does your dBASE data have to be side-lined. No longer do you have to pull your hair out trying to figure out how to access your dBASE data.

But what if you just want to import the data and start over. Well we make use of a very nice statement, select into … Which will create a brand new table for us.

SELECT Glcode, Account, ContAccountgl, Amount, DC, Glm_stDesc into openrowsettable
FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};
DBQ=D:\dbase Work\R3\Santam\Data;
SourceType=DBF',
'SELECT * FROM gl x
inner join glacc y on y.glm_accountglacc = x.glcode
')

So there you have it. Working with dBASE data in MS SQL.

Next we will look at how you can use a linked server to dBASE dbf tables to view and read your dBASE data.

6 comments:

Anonymous said...

thank you. very good article. search it for two days and was very satisfied.

Website design, web development, web hosting and Search Engine Optimization
Gustavo said...

Hi, good article, it was helpful for my select query...
I have one question, I hope you can help me, when I use a select query for example:

select * from openrowset('MSDASQL',
'Driver={Microsoft dBase Driver (*.dbf)};
DBQ=d:\bases\iclientes;
SourceType=DBF',
'select * from maesbag')
, it works perfectly, but when i tried to execute a update or insert query:::

UPDATE openrowset('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};DBQ=d:\bases\ivendedores;SourceType=DBF','select * from vendbag') SET NLIBMIL = '11002670' WHERE CVEN = '905'
, it reports this error: The requested operation could not be performed because the OLE DB provider 'MSDASQL' does not support the required transaction interface...any solution for this problem?

Thanks for your help and sorry for my bad english...

Website design, web development, web hosting and Search Engine Optimization
Robert Bravery said...
This comment has been removed by a blog administrator.
Robert Bravery said...

I can tell you why it happen, but as of yet, I Haven't found a solution.

The problem is cause because of the BDE, or Borland Database engine. This has not been update in a while. SO older ODBC dirvers written to interact with it do not understand the newer (well not so new) dBASE file and index structure. The Driver that we are using are actually meant for dbase 5.0. Even then they were very suspect when it came to insert and update.

Website design, web development, web hosting and Search Engine Optimization
Gustavo said...

Thanks for your quick response.

Well, Before I worked the way you display in your site (it works any time), I worked as follows:
openrowset('MSDASQL',
'DSN=Visual FoxPro Tables;
SourceDB=d:\bases\iclientes;
SourceType=DBF',
'select * from maescaj')
...
for select, update querys but the problem is that sometimes works and sometimes it reports this error: OLE db provider 'MSDASQL' reported an error. .... The Provider didnot give any information about the error...Do you know why is this happen?... I'm working with SQL Server 2000.

Thanks again.

Website design, web development, web hosting and Search Engine Optimization
agorthog said...

Any idea how to access a dBase file using openrowset in SS2K8 64-bit? Apparently the 64-bit version of the MSDASQL provider cannot access 32-bit drivers. There currently is no 64-bit Microsoft dBase Driver.

Website design, web development, web hosting and Search Engine Optimization
Website design, web development, web hosting and Search Engine Optimization