Import/Export DBASE

It is very easy to import data from the DBASE file to the SQL database. Use the following statement directly:

/* ===================================================== ===================================== */
-- If the table to be imported already exists
Insert into Table select * from
OpenRowSet ('Microsoft. Jet. oledb.4.0'
, 'Dbase5.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'
, 'Dbase5.0; database = C:/', 'select * from [test. DBF]')

/* ===================================================== ===================================== */
-- If you export data from the SQL database to dBase, and if the DBASE file already exists, you can simply use it:
Insert
OpenRowSet ('Microsoft. Jet. oledb.4.0'
, 'Dbase5.0; database = C:/', 'select * from [test. DBF]')
Select * from table

/* -- Description:
Database = C:/is the directory where DBF Files are stored.
'Select * from [test. DBF] test. DBF indicates the DBF file name.
--*/

 

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

/* -- Data export DBASE
 
Export The data in the table to DBASE. If the file does not exist, the file is automatically created.
Only standard data types can be exported for the sake of universality.
-- Producer build 2003.07 (reference please keep this information )--*/

/* -- Call example

-- Export DBASE
P_exporttb @ tbname = 'region 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) = '', -- file name, default table name
@ Over bit = 0 -- whether to overwrite existing files. If not, append the existing files directly.
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 whether 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_mongoshell @ 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 + '"'

-- Connect 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

-- SQL statement used to create a table
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 250 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 ('money', 'smallmoney') Then 'money'
Else B. Name end
From syscolumns a left join policypes B on A. xtype = B. xusertype
Where B. name not in ('image', 'text', 'uniqueidentifier', '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, @ 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 code
, @ 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. If the file does not exist, the file is automatically created.
Only standard data types can be exported for the sake of universality.
-- Producer build 2003.07 (reference please keep this information )--*/

/* -- Call example

-- Export DBASE
P_exporttb @ sqlstr = 'select * From region 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 (250) = 'temp. dbf', -- file name, default: temp
@ Over bit = 0 -- whether to overwrite existing files. If not, append the existing files directly.
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 whether 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_mongoshell @ 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 + '"'

-- SQL statement used to create a table
Declare @ tbname sysname
Set @ tbname = '# TMP _' + convert (varchar (38), newid ())
Set @ SQL = 'select * into ['+ @ tbname +'] From ('+ @ sqlstr +')'
Exec (@ SQL)

-- Connect 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

-- SQL statement used to create a table
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 250 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 ('money', 'smallmoney') Then 'money'
Else B. Name end
From tempdb .. syscolumns a left join tempdb .. policypes B on A. xtype = B. xusertype
Where B. name not in ('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) + ')'
, @ Fdlist = substring (@ fdlist, 2,8000)

If @ over = 1
Begin
Exec @ err = sp_oamethod @ OBJ, 'execute ', @ 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 code
, @ SRC as error source, @ DESC as error description
Select @ SQL, @ constr, @ fdlist
Go