Excel file Import Database multiple sheet
if exists (select 1 from sysobjects where name=n ' p_import_excel ' and type= ' P ')
drop proc P_import_excel
Go
--call example exec p_import_excel ' C:\123.xls ', ' Sheet1; Sheet2; Sheet3 ', ' tbl '
Create proc P_import_excel
@excfilename nvarchar (,--) file path
@sheet nvarchar (1000),--the sheet to be directed, to '; ' Separate, such as ' Sheet1; Sheet2; Sheet3 '
@tblname nvarchar (100)--the name of the table to import
As
Begin
Set @[email protected]+ '; '
DECLARE @excsheet nvarchar (100)
While CHARINDEX ('; ', @sheet) >0
Begin
Select @excsheet =left (@sheet, charindex ('; ', @sheet)-1)
EXEC (' SELECT * into '[email protected]+ ' from OpenrowSET (' microsoft.jet.oledb.4.0 ', ' Excel8.0; hdr=yes,database= ' [email protected]+ ', ' select * from [' [email protected]+ ' $] ')
Set @sheet =stuff (@sheet, 1,charindex ('; ', @sheet), ")
End
End
Go no matter what the Excel version, in the extended properties are to write Excel 8.0, note the space
Hdr=yes: The first line of the Excel file is the column word, not the data. If the first line is also data, use Hdr=no. The default value is Yes
Imex=1: Converts a mixed column into a text column
Note The query statement: the sheet name to use [$] box up
/******* Export to Excel
EXEC Master. xp_cmdshell ' bcp SettleDB.dbo.shanghu out c:\temp1.xls-c-q-s "Gnetdata/gnetdata"-u "sa"-P "" '/*********** Importing 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/** importing text files
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 file
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] ')
--*//************** importing DBF files ****************/
SELECT * FROM OPENROWSET (' MSDASQL ',
' Driver=microsoft Visual FoxPro Driver;
Sourcedb=e:\vfp98\data;
Sourcetype=dbf ',
' SELECT * FROM customer where country! = "USA" ORDER by country ')
Go
/***************** Export to DBF ***************/
If you want to export data to an already generated structure (that is, an existing) FoxPro table, you can insert into OPENROWSET (' MSDASQL ') directly with the following SQL statement,
' 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 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 '; ', table A)********************* importing XML filesDECLARE @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>
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
MSSQL database Import and Export Daquan II (SQL statement)
