Import/Export dBASE

From the dBASE file, import the data into the SQL database, very simply, using the following statement directly:

/*===================================================================*/
--If the table that accepts the data import already exists
INSERT INTO table SELECT * FROM
OpenRowset (' MICROSOFT. JET. oledb.4.0 '
, ' DBase 5.0;database=c:/', ' select * from [TEST.DBF] '

--If you import data and generate a table
SELECT * Into table from
OpenRowset (' MICROSOFT. JET. oledb.4.0 '
, ' DBase 5.0;database=c:/', ' select * from [TEST.DBF] '

/*===================================================================*/
-If you export data to dBASE from a SQL database, if the dBASE file already exists, you can simply use:
INSERT INTO
OpenRowset (' MICROSOFT. JET. oledb.4.0 '
, ' DBase 5.0;database=c:/', ' select * from [TEST.DBF] '
SELECT * FROM table

/*--Description:
database=c:/c:/is the storage directory of DBF files
' SELECT * from [TEST.DBF] test.dbf refers to the DBF filename
--*/

-If the dBASE file does not exist, you need to use the following stored procedure.

/*--Data export dbase

Export the data from the table to dBASE, and if the file does not exist, the file will be created automatically
Only export standard data types are supported based on commonality considerations
--Jiangjian 2003.07 (please keep this information for reference)--*/

/*--Call Example

--Export dBASE
P_EXPORTTB @tbname = ' Area information ', @path = ' c:/', @over =0
--*/
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ P_EXPORTTB] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [P_EXPORTTB]
Go

Create proc P_EXPORTTB
@tbname sysname--Name of the table to be exported
@path nvarchar (1000),--File storage directory
@fname nvarchar (250) = ',--filename, default to table name
@over bit=0-Overwrite files that already exist, append directly if not overridden
As
declare @err int, @src nvarchar (255), @desc nvarchar (255), @out int
declare @obj int, @constr nvarchar (1000), @sql varchar (8000), @fdlist varchar (8000)

--Parameter detection
If IsNull (@fname, ') = ' Set @fname = @tbname + '. dbf '

--Check if the file already exists
If Right (@path, 1) <> '/' Set @path = @path + '/'
CREATE TABLE #tb (a bit,b bit,c bit)
Set @sql = @path + @fname
INSERT into #tb exec master. Xp_fileexist @sql
if exists (select 1 from #tb where a=1)
If @over =1
Begin
Set @sql = ' del ' + @sql
EXEC master.. xp_cmdshell @sql, No_output
End
Else
Set @over =0
Else
Set @over =1

--Database Creation statement
Set @sql = @path + @fname
Set @constr = ' provider=microsoft.jet.oledb.4.0; Extended properties= "DBASE 5.0; '
+'; Hdr=no;database= ' + @path + ' "'

--Connecting to the database
exec @err =sp_oacreate ' adodb.connection ', @obj out
If @err <>0 goto Lberr

EXEC @err =sp_oamethod @obj, ' open ', NULL, @constr
If @err <>0 goto Lberr

--Create a table's SQL
Select @sql = ', @fdlist = '
Select @fdlist = @fdlist + ', ' +a.name
 , @sql = @sql + ', [' +a.name+ ' ] '
  +case when b.name in (' char ', ' nchar ', ' varchar ', ' nvarchar ') then
     ' text ( ' +cast (case when a.length>250 then else a.length end as varchar) + '] '
   when b.name in (' Tynyint ') , ' int ', ' bigint ', ' tinyint ') then ' int '
   when b.name in (' smalldatetime ', ' datetime ') Then ' DateTime '
   when b.name in (' $ ', ' smallmoney ') Then ' money '
   else b.name End
from Syscolumns A-left join systypes B to A.xtype=b.xusertype
where b.name not in (' image ', ' text ', ' uniqueidentif Ier ', ' sql_variant ', ' ntext ', ' varbinary ', ' binary ', ' timestamp ')
 and object_id (@tbname) =id
Select @sql = ' CREATE TABLE [' + @fname
 + '] (' +substring (@sql, 2,8000) + ') '
 , @fdlist =substring (@fdlist, 2,8000)

If @over =1
Begin
EXEC @err =sp_oamethod @obj, ' execute ', @out out, @sql
If @err <>0 goto Lberr
End

EXEC @err =sp_oadestroy @obj

Set @sql = ' OpenRowset (' MICROSOFT. JET. oledb.4.0 ', ' dBase 5.0;database= '
+ @path + ', ' select * from [' + @fname + '] '

--Import data
EXEC (' insert INTO ' + @sql + ' (' + @fdlist + ') Select ' + @fdlist + ' from ' + @tbname)

Return

Lberr:
EXEC sp_OAGetErrorInfo 0, @src out, @desc out
Lbexit:
Select CAST (@err as varbinary (4)) as error number
, @src as error source, @desc as error description
Select @sql, @constr, @fdlist

Go

/*--Data export dbase

Export the data in the query statement to dBASE, and if the file does not exist, the file will be created automatically
Only export standard data types are supported based on commonality considerations
--Jiangjian 2003.07 (please keep this information for reference)--*/

/*--Call Example

--Export dBASE
P_EXPORTTB @sqlstr = ' SELECT * from area information ', @path = ' c:/', @over =1
--*/

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ P_EXPORTTB] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [P_EXPORTTB]
Go

Create proc P_EXPORTTB
@sqlstr varchar (8000),--Name of the query to be exported
@path nvarchar (1000),--File storage directory
@fname nvarchar = ' temp.dbf ',--filename, default to Temp
@over bit=0-Overwrite files that already exist, append directly if not overridden
As
declare @err int, @src nvarchar (255), @desc nvarchar (255), @out int
declare @obj int, @constr nvarchar (1000), @sql varchar (8000), @fdlist varchar (8000)

--Parameter detection
If IsNull (@fname, ') = ' Set @fname = ' temp.dbf '

--Check if the file already exists
If Right (@path, 1) <> '/' Set @path = @path + '/'
CREATE TABLE #tb (a bit,b bit,c bit)
Set @sql = @path + @fname
INSERT into #tb exec master. Xp_fileexist @sql
if exists (select 1 from #tb where a=1)
If @over =1
Begin
Set @sql = ' del ' + @sql
EXEC master.. xp_cmdshell @sql, No_output
End
Else
Set @over =0
Else
Set @over =1

--Database Creation statement
Set @sql = @path + @fname
Set @constr = ' provider=microsoft.jet.oledb.4.0; Extended properties= "DBASE 5.0; '
+'; Hdr=no;database= ' + @path + ' "'

--Create a table SQL
declare @tbname sysname
Set @tbname = ' # #tmp_ ' +convert (varchar), newid ())
Set @sql = ' select * into [' + @tbname + '] from (' + @sqlstr + ') a '
EXEC (@sql)

--Connecting to the database
exec @err =sp_oacreate ' adodb.connection ', @obj out
If @err <>0 goto Lberr

Exec @err =sp_oamethod @obj, ' open ', NULL, @constr
if @err <>0 goto lberr

--Create a table's SQL
Select @sql = ', @fdlist = '
Select @fdlist = @fdlist + ', ' +a.name
 , @sql = @sql + ', [' +a.name+ ' ] '
  +case when b.name in (' char ', ' nchar ', ' varchar ', ' nvarchar ') then
     ' text ( ' +cast (case when a.length>250 then else a.length end as varchar) + '] '
   when b.name in (' Tynyint ') , ' int ', ' bigint ', ' tinyint ') then ' int '
   when b.name in (' smalldatetime ', ' datetime ') Then ' DateTime '
   when b.name in (' $ ', ' smallmoney ') Then ' money '
   else b.name End
from tempdb.. Syscolumns a LEFT join tempdb. Systypes b on A.xtype=b.xusertype
where b.name (' image ', ' text ', ' uniqueidentifier ', ' sql_variant ', ' ntext ', ' varbinary ', ' binary ', ' timestamp ')
 and a.id= (select id from tempdb. sysobjects where name= @tbname)
Select @sql = ' CREATE TABLE [' + @fname
 + '] (' +substring (@sql, 2,8000) + ') '
&NBSP, @fdlist =substring (@fdlist, 2,8000)

If @over =1
Begin
EXEC @err =sp_oamethod @obj, ' execute ', @out out, @sql
If @err <>0 goto Lberr
End

EXEC @err =sp_oadestroy @obj

Set @sql = ' OpenRowset (' MICROSOFT. JET. oledb.4.0 ', ' dBase 5.0;database= '
+ @path + ', ' select * from [' + @fname + '] '

--Import data
EXEC (' insert INTO ' + @sql + ' (' + @fdlist + ') Select ' + @fdlist + ' from [' + @tbname + '] ')

Set @sql = ' drop table [' + @tbname + '] '
EXEC (@sql)

Return

Lberr:
EXEC sp_OAGetErrorInfo 0, @src out, @desc out
Lbexit:
Select CAST (@err as varbinary (4)) as error number
, @src as error source, @desc as error description
Select @sql, @constr, @fdlist
Go