Statement/******* 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
/* Dynamic file name
DECLARE @fn varchar, @s varchar (1000)
Set @fn = ' C:\Test.xls '
Set @s = ' Microsoft.Jet.OLEDB.4.0 ',
' Data source= ' + @fn + '; User id=admin; password=; Extended Properties=excel 5.0 ""
Set @s = ' SELECT * from OpenDataSource (' +@s+ ') ... sheet1$ '
EXEC (@s)
*/
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
/********************** Excel leads to remote SQL
Insert OpenDataSource (
' SQLOLEDB ',
' Data source= remote IP; User Id=sa; password= Password '
). The library name. dbo. Table name (column name 1, column name 2)
SELECT column name 1, column name 2
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 '
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 profile 4.dbf] ')
--*/
--/* DBase III Document
SELECT * FROM
OPENROWSET (' MICROSOFT. JET. oledb.4.0 '
, ' DBase III; Hdr=no;imex=2;database=c:\ ', ' select * from [Customer profile 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:\vfp98\data;
Sourcetype=dbf ',
' SELECT * from customer where country!= ' The USA ' ORDER by country ')
Go
/***************** exported to DBF ***************/
If you want to export data to an already generated structure (that is, an existing) FoxPro table, you can use the following SQL statement directly
INSERT INTO OPENROWSET (' MSDASQL ',
' Driver=microsoft Visual FoxPro Driver; SOURCETYPE=DBF; Sourcedb=c:\ ',
' SELECT * from [AA]. DBF] ')
SELECT * FROM table
Description
Sourcedb=c:\ Specify the folder where the FoxPro table resides
Aa. DBF Specifies the filename 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 ... Table B
/************* Import access********************/
Insert into B table Selet * FROM OPENROWSET (' Microsoft.Jet.OLEDB.4.0 '),
' X:\A.mdb '; ' Admin '; ', table A
File name as parameter
DECLARE @fname varchar (20)
Set @fname = ' D:\test.mdb '
EXEC (' SELECT a.* from OpenDataSource (' microsoft.jet.oledb.4.0 '),
' + @fname + '; Admin '; ' ", topics) as a ')
SELECT *
From OpenDataSource (' microsoft.jet.oledb.4.0 ',
' Data source= ' F:\northwind.mdb '; Jet oledb:database password=123; User id=admin; password=; ') ... Products
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= "note=" Wrap it Blue
White red ">
<Urgency>Important</Urgency>
