/******* Export to excel
exec master..xp_cmdshell 'bcp settledb.dbo.shanghu out c:temp1.xls -c -q -s"gnetdata/gnetdata" -u"sa" -p""'
/*********** Import excel
select *
from opendatasource( 'microsoft.jet.oledb.4.0',
'data source="c:test.xls";user id=admin;password=;extended properties=excel 5.0')...xactions
select cast(cast(account number as numeric(10,2)) as nvarchar(255))+' ' converted alias
from opendatasource( 'microsoft.jet.oledb.4.0',
'data source="c:test.xls";user id=admin;password=;extended properties=excel 5.0')...xactions
/** Import text file
exec master..xp_cmdshell 'bcp dbname..tablename in c:dt.txt -c -sservername -usa -ppassword'
/** Export text file
exec master..xp_cmdshell 'bcp "dbname..tablename" out c:dt.txt -c -sservername -usa -ppassword'
This sentence needs to be enclosed in quotation marks
or
exec master..xp_cmdshell 'bcp "select * from dbname..tablename" queryout c:dt.txt -c -sservername -usa -ppassword'
Export to txt text, separated by commas
exec master..xp_cmdshell 'bcp "Library name..Table name" out "d:tt.txt" -c -t ,-u sa -p password'
bulk insert library name..table name
from 'c:test.txt'
with (
fieldterminator = ';',
rowterminator = 'n'
)
--/* dbase iv file
select * from
openrowset('microsoft.jet.oledb.4.0'
,'dbase iv;hdr=no;imex=2;database=c:','select * from [Customer Data 4.dbf]')
--*/
--/* dbase iii file
select * from
openrowset('microsoft.jet.oledb.4.0'
,'dbase iii;hdr=no;imex=2;database=c:','select * from [Customer Data 3.dbf]')
--*/
--/* foxpro database
select * from openrowset('msdasql',
'driver=microsoft visual foxpro driver;sourcetype=dbf;sourcedb=c:',
'select * from [aa.dbf]')
--*/
/******************Import dbf file******************/
select * from openrowset('msdasql',
'driver=microsoft visual foxpro driver;
sourcedb=e:vfp98data;
sourcetype=dbf',
'select * from customer where country != "usa" order by country')
go
/************************ Export to dbf ***************/
If you want to export data to the generated structure (i.e. existing) foxpro table, you can directly use the following sql statement
insert into openrowset('msdasql',
'driver=microsoft visual foxpro driver;sourcetype=dbf;sourcedb=c:',
'select * from [aa.dbf]')
select * from table
illustrate:
sourcedb=c: specifies the folder where the foxpro table is located
aa.dbf specifies the file name of the foxpro table.
/******************Export to access**********************/
insert into openrowset('microsoft.jet.oledb.4.0',
'x:a.mdb';'admin';'',a table) select * from database name..b table
/******************import access************************/
insert into b table selet * from openrowset('microsoft.jet.oledb.4.0',
'x:a.mdb';'admin';'',a table)
********************* Import xml file
declare @idoc int
declare @doc varchar(1000)
--sample xml document
set @doc ='
<root>
<customer cid= "c1" name="janine" city="issaquah">
<order oid="o1" date="1/20/1996" amount="3.5" />
<order oid="o2" date="4/30/1997" amount="13.4">customer was very satisfied
</order>
</customer>
<customer cid="c2" name="ursula" city="oelde" >
<order oid="o3" date="7/14/1999" amount="100" note="wrap it blue
white red">
<urgency>important</urgency>
happy customer.
</order>
<order oid="o4" date="1/20/1996" amount="10000"/>
</customer>
</root>
'
-- create an internal representation of the xml document.
exec sp_xml_preparedocument @idoc output, @doc
-- execute a select statement using openxml rowset provider.
select *
from openxml (@idoc, '/root/customer/order', 1)
with (oid char(5),
amount float,
comment ntext 'text()')
exec sp_xml_removedocument @idoc
/**********************Import the entire database**************************** *******************/
Stored procedure implemented using bcp
/*
Implement stored procedures for data import/export
According to different parameters, you can import/export the entire database/single table call example:
--export call example
----Export a single table
exec file2table 'zj','','','xzkh_sa..region information','c:zj.txt',1
----Export the entire database
exec file2table 'zj','','','xzkh_sa','c:docman',1
--Import call example
----Import a single table
exec file2table 'zj','','','xzkh_sa..region information','c:zj.txt',0
----Import the entire database
exec file2table 'zj','','','xzkh_sa','c:docman',0
*/
if exists(select 1 from sysobjects where name='file2table' and objectproperty(id,'isprocedure')=1)
drop procedure file2table
go
create procedure file2table
@servername varchar(200) --server name
,@username varchar(200) --Username, if using nt verification method, it will be empty''
,@password varchar(200) --Password
,@tbname varchar(500) --database.dbo.table name. If you do not specify:.dbo.table name, all user tables of the database will be exported.
,@filename varchar(1000) --Import/export path/file name. If the @tbname parameter indicates to export the entire database, then this parameter is the file storage path, and the file name automatically uses the table name.txt
,@isout bit --1 is export, 0 is import
as
declare @sql varchar(8000)
if @tbname like '%.%.%' --If a table name is specified, a single table will be exported directly
begin
set @sql='bcp '+@tbname
+case when @isout=1 then ' out ' else ' in ' end
+' " '+@filename+' " /w'
+' /s '+@servername
+case when isnull(@username,'')='' then '' else ' /u '+@username end
+' /p '+isnull(@password,'')
exec master..xp_cmdshell @sql
end
else
begin --export the entire database, define cursors, and remove all user tables
declare @m_tbname varchar(250)
if right(@filename,1)<>'' set @filename=@filename+''
set @m_tbname='declare #tb cursor for select name from '+@tbname+'..sysobjects where xtype=''u'''
exec(@m_tbname)
open #tb
fetch next from #tb into @m_tbname
while @@fetch_status=0
begin
set @sql='bcp '+@tbname+'..'+@m_tbname
+case when @isout=1 then ' out ' else ' in ' end
+' " '+@filename+@m_tbname+'.txt " /w'
+' /s '+@servername
+case when isnull(@username,'')='' then '' else ' /u '+@username end
+' /p '+isnull(@password,'')
exec master..xp_cmdshell @sql
fetch next from #tb into @m_tbname
end
close #tb
deallocate #tb
end
go
/************************excel to txt**************************** *******************/
Want to use
select * into opendatasource(...) from opendatasource(...)
Implementation of importing the contents of an excel file into a text file
Suppose there are two columns in excel, the first column is the name, and the second column is the account number (16 digits)
And the bank account number is divided into two parts after exporting to a text file, the first 8 digits and the last 8 digits are separated.
If you want to insert it using the statement above, the text file must exist and have one line: name, bank account number 1, bank account number 2
Then you can use the following statement to insert. Note that the file name and directory can be modified according to your actual situation.
insert into
opendatasource('microsoft.jet.oledb.4.0'
,'text;hdr=yes;database=c:'
)...[aa#txt]
--,aa#txt)
--*/
select name, bank account number 1=left (bank account number, 8), bank account number 2 = right (bank account number, 8)
from
opendatasource('microsoft.jet.oledb.4.0'
,'excel 5.0;hdr=yes;imex=2;database=c:a.xls'
--,sheet1$)
)...[sheet1$]
If you want to directly insert and generate text files, use bcp
declare @sql varchar(8000),@tbname varchar(50)
--First import the contents of the excel table into a global temporary table
select @tbname='[##temp'+cast(newid() as varchar(40))+']'
,@sql='select name, bank account number 1=left (bank account number, 8), bank account number 2 = right (bank account number, 8)
into '+@tbname+' from
opendatasource(''microsoft.jet.oledb.4.0''
,''excel 5.0;hdr=yes;imex=2;database=c:a.xls''
)...[sheet1$]'
exec(@sql)
--Then use bcp to export from the global temporary table to a text file
set @sql='bcp " '+@tbname+' " out "c:aa.txt" /s"(local)" /p"" /c'
exec master..xp_cmdshell @sql
--Delete temporary table
exec('drop table '+@tbname )
Stored procedure for importing and exporting files to the database using bcp:
/*--bcp-Import and export of binary files
Supports import/export of image, text, ntext fields
image is suitable for binary files; text, ntext is suitable for text data files
Note: When importing, all rows that meet the conditions will be overwritten
When exporting, all lines that meet the conditions will also be exported to the specified file.
This stored procedure only uses bcp to implement Zou Jian 2003.08-----------------*/
/*--Call example
--Data export
exec p_binaryio 'zj','','','acc_demo data..tb','img','c:zj1.dat'
--Data export
exec p_binaryio 'zj','','','acc_demo data..tb','img','c:zj1.dat','',0
--*/
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[p_binaryio]') and objectproperty(id, n'isprocedure') = 1)
drop procedure [dbo].[p_binaryio]
go
create proc p_binaryio
@servename varchar (30),--server name
@username varchar (30), --username
@password varchar (30), --password
@tbname varchar (500), --database..table name
@fdname varchar (30), --Field name
@fname varchar (1000), --directory + file name, use/overwrite during processing: @filename+.bak
@tj varchar (1000)='', --processing conditions. For data import, if the condition contains @fdname, please specify the table name prefix
@isout bit=1 --1 export ((default), 0 import
as
declare @fname_in varchar(1000) --bcp processing response file name
,@fsize varchar(20) --The size of the file to be processed
,@m_tbname varchar(50) --temporary table name
,@sql varchar(8000)
--Get the size of the imported file
if @isout=1
set @fsize='0'
else
begin
create table #tb (optional name varchar (20), size int
, creation date varchar(10), creation time varchar(20)
, last write operation date varchar(10), last write operation time varchar(20)
,last access date varchar(10), last access time varchar(20),characteristic int)
insert into #tb
exec master..xp_getfiledetails @fname
select @fsize=sizefrom #tb
drop table #tb
if @fsize is null
begin
print 'File not found'
return
end
end
--Generate data processing response file
set @m_tbname='[##temp'+cast(newid() as varchar(40))+']'
set @sql='select * into '+@m_tbname+' from(
select null as type
union all select 0 as prefix
union all select '+@fsize+' as length
union all select null as end
union all select null as format
) a'
exec(@sql)
select @fname_in=@fname+'_temp'
,@sql='bcp " '+@m_tbname+' " out " '+@fname_in
+'" /s" '+@servename
+case when isnull(@username,'')='' then ''
else '" /u" '+@username end
+'" /p"'+isnull(@password,'')+'" /c'
exec master..xp_cmdshell @sql
--Delete temporary table
set @sql='drop table '+@m_tbname
exec(@sql)
if @isout=1
begin
set @sql='bcp "select top 1 '+@fdname+' from '
+@tbname+case isnull(@tj,'') when '' then ''
else ' where '+@tj end
+'" queryout " '+@fname
+'" /s" '+@servename
+case when isnull(@username,'')='' then ''
else '" /u" '+@username end
+'" /p"'+isnull(@password,'')
+'" /i" '+@fname_in+'"'
exec master..xp_cmdshell @sql
end
else
begin
--Prepare temporary tables for data import
set @sql='select top 0 '+@fdname+' into '
+@m_tbname+' from ' +@tbname
exec(@sql)
--Import data into a temporary table
set @sql='bcp " '+@m_tbname+' " in " '+@fname
+'" /s" '+@servename
+case when isnull(@username,'')='' then ''
else '" /u" '+@username end
+'" /p"'+isnull(@password,'')
+'" /i" '+@fname_in+'"'
exec master..xp_cmdshell @sql
--Import data into formal tables
set @sql='update '+@tbname
+' set '+@fdname+'=b.'+@fdname
+' from '+@tbname+' a,'
+@m_tbname+' b'
+case isnull(@tj,'') when '' then ''
else ' where '+@tj end
exec(@sql)
--Delete data processing temporary table
set @sql='drop table '+@m_tbname
end
--Delete data processing answer file
set @sql='del '+@fname_in
exec master..xp_cmdshell @sql