也談SQL Server表與Excel、Access資料互導
要用T-SQL語句直接匯出至Excel工作薄,就不得不用借用SQL Server管理器的一個擴充套件儲存過程:xp_cmdshell,此過程的作用為“以作業系統命令列直譯器的方式執行給定的命令字串,並以文字行方式返回任何輸出。”下面為定義示例:
實際例子與說明如下:
2、Excel匯入SQL Server表:
在SQL Server中,有定義一個OpenDateSource函式,用於引用那些不經常訪問的 OLE DB 資料來源,而我們的資料互導操作,就是建立在這個函式之上。
首先看一個T-SQL幫助中的示例,描述如下:
如果你直接引用這個示例進行查詢,那麼肯定是通不過的。關鍵在於語句中的兩個地方需要修改,一處在於Data Source處,雙引號內為Excel表格的實際存放位置,要修改為你想查詢的Excel表實際完整路徑;二為最後的...xactions,其實這裡代表的是要進行的某些動作,下面會講,這裡修改成用中括號包圍的Excel表中工作表名字(加上一個$)就可以了,如[Sheet1$]。當然,還可以將Excel 5.0改為Excel 8.0,因為5.0是以前的老版本了。
下面是例項說明:
SQL Server與Excel的資料互導講解完了,你明白了嗎?而Access和Excel的基本一樣,只是要去掉Extended properties宣告。
=======================
Delphi示例(匯出為excel表):
EXEC master..xp_cmdshell 'bcp 庫名.dbo.表名out c:\Book3.xls -c -q -S"servername" -U"sa" -P""'
--引數:S 是SQL伺服器名;U是使用者名稱;P是密碼,沒有就空著
--說明:其實用這個過程匯出的格式實質上就是文字格式的,不信的話在匯出的Excel表中改動一下再儲存看看。
--引數:S 是SQL伺服器名;U是使用者名稱;P是密碼,沒有就空著
--說明:其實用這個過程匯出的格式實質上就是文字格式的,不信的話在匯出的Excel表中改動一下再儲存看看。
實際例子與說明如下:
/**//*如果要將表整個匯出至Excel的話*/
EXEC master..xp_cmdshell 'bcp northwind.dbo.orders out c:\Book1.xls -c -q -S"(local)" -U"sa" -P""'
--注意句中的northwind.dbo.orders,為資料庫名+擁有者+表名
--直接匯出用“out”關健字
-------------------------------------------
/**//*如果要利用查詢來匯出部分欄位至Excel的話*/
EXEC master..xp_cmdshell 'bcp "SELECT orderid,cutomerid,freight FROM northwind..orders ORDER BY orderid" queryout C:\ Book2.xls -c -S"(local)" -U"sa" -P""'
--這裡在bcp後面加了一個查詢語句,並用雙引號括起來
--利用查詢要用“queryout”關鍵字
EXEC master..xp_cmdshell 'bcp northwind.dbo.orders out c:\Book1.xls -c -q -S"(local)" -U"sa" -P""'
--注意句中的northwind.dbo.orders,為資料庫名+擁有者+表名
--直接匯出用“out”關健字
-------------------------------------------
/**//*如果要利用查詢來匯出部分欄位至Excel的話*/
EXEC master..xp_cmdshell 'bcp "SELECT orderid,cutomerid,freight FROM northwind..orders ORDER BY orderid" queryout C:\ Book2.xls -c -S"(local)" -U"sa" -P""'
--這裡在bcp後面加了一個查詢語句,並用雙引號括起來
--利用查詢要用“queryout”關鍵字
2、Excel匯入SQL Server表:
在SQL Server中,有定義一個OpenDateSource函式,用於引用那些不經常訪問的 OLE DB 資料來源,而我們的資料互導操作,就是建立在這個函式之上。
首先看一個T-SQL幫助中的示例,描述如下:
--下面是個查詢的示例,它通過用於 Jet 的 OLE DB 提供程式查詢 Excel 電子表格。
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')xactions
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')xactions
如果你直接引用這個示例進行查詢,那麼肯定是通不過的。關鍵在於語句中的兩個地方需要修改,一處在於Data Source處,雙引號內為Excel表格的實際存放位置,要修改為你想查詢的Excel表實際完整路徑;二為最後的...xactions,其實這裡代表的是要進行的某些動作,下面會講,這裡修改成用中括號包圍的Excel表中工作表名字(加上一個$)就可以了,如[Sheet1$]。當然,還可以將Excel 5.0改為Excel 8.0,因為5.0是以前的老版本了。
下面是例項說明:
/**//*1、插入Excel中的資料到現存的sql資料庫表中(假設C盤有excel表book2.xls,book2.xls中有個工作表sheet1,sheet1中有兩列id和FName;而同時sql資料庫中也有一個表test):*/
insert into test SELECT id,FName
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\book2.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')[sheet1$]
--如果用select * ,則列的次序會亂,資料內容也會亂,無法插入成功,所以指定列名
-----------------------
/**//*2、插入excel表中資料到sql資料庫並新建一個sql表(excel的定義和內容同上):*/
select convert(int,id)as id,FName into test7
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\book2.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')[sheet1$]
--在select 列中最好用convert進行顯示型別轉換,否則資料型別會不如預期。
insert into test SELECT id,FName
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\book2.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')[sheet1$]
--如果用select * ,則列的次序會亂,資料內容也會亂,無法插入成功,所以指定列名
-----------------------
/**//*2、插入excel表中資料到sql資料庫並新建一個sql表(excel的定義和內容同上):*/
select convert(int,id)as id,FName into test7
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\book2.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')[sheet1$]
--在select 列中最好用convert進行顯示型別轉換,否則資料型別會不如預期。
SQL Server與Excel的資料互導講解完了,你明白了嗎?而Access和Excel的基本一樣,只是要去掉Extended properties宣告。
=======================
Delphi示例(匯出為excel表):
ADOQ1.Close;
ADOQ1.SQL.Clear;
sqltrs :=
'INSERT INTO CTable (Name1,Sex,ID)'+
' SELECT'+
' 姓名,性別,身份證號'+
' FROM [excel 8.0;database=' + XlsName + '].[sheet1$]';
ADOQ1.Parameters.Clear;
ADOQ1.ParamCheck:=false;
ADOQ1.SQL.Text := sqltrs;
ADOQ1.Execsql;
//注意中文欄位名左右兩邊不能有空格
ADOQ1.SQL.Clear;
sqltrs :=
'INSERT INTO CTable (Name1,Sex,ID)'+
' SELECT'+
' 姓名,性別,身份證號'+
' FROM [excel 8.0;database=' + XlsName + '].[sheet1$]';
ADOQ1.Parameters.Clear;
ADOQ1.ParamCheck:=false;
ADOQ1.SQL.Text := sqltrs;
ADOQ1.Execsql;
//注意中文欄位名左右兩邊不能有空格
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/16436858/viewspace-666202/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQL Server與Access、Excel的資料轉換SQLServerExcel
- SQL SERVER 與ACCESS、EXCEL的資料轉換SQLServerExcel
- SQL SERVER 與ACCESS、EXCEL的資料轉換 (轉)SQLServerExcel
- SQL codeSQL SERVER 與ACCESS、EXCEL的資料轉換SQLServerExcel
- asp.net 操作Excel表資料匯入到SQL Server資料庫ASP.NETExcelSQLServer資料庫
- SQL server 修改表資料SQLServer
- 談asp 與SQL server互操作的時間處理 (轉)SQLServer
- SQL Server連線ACCESS資料庫的實現 (轉)SQLServer資料庫
- Sql Server系列:資料表操作SQLServer
- SQL SERVER 和EXCEL的資料匯入匯出SQLServerExcel
- [zt] Oracle與SQL Server的互連OracleSQLServer
- SQL SERVER與C#的資料型別對應表SQLServerC#資料型別
- SQL Server資料庫安全管理經驗談SQLServer資料庫
- SQL語句select隨機調取10行資料 Access/SQL Server/Mysql等資料庫隨機ServerMySql資料庫
- sql server 建臨時表修改資料SQLServer
- java poi讀取Excel資料 插入到SQL SERVER資料庫中JavaExcelSQLServer資料庫
- 從EXCEL匯入資料到SQL SERVERExcelSQLServer
- Excel資料匯入Sql Server,部分數字為NullExcelSQLServerNull
- SQL SERVER與ORACLE的資料共享SQLServerOracle
- 談談資料從sql server資料庫匯入mysql資料庫的體驗(轉)Server資料庫MySql
- 臨時表在Oracle資料庫與SQL Server資料庫中的異同Oracle資料庫SQLServer
- SQL Server 資料表程式碼建立約束SQLServer
- ORACLE資料庫裡表匯入SQL Server資料庫Oracle資料庫SQLServer
- 備份和恢復SQL Server資料庫+壓縮ACCESS的類(方法)SQLServer資料庫
- 用ASP.NET/C#連線Access和SQL Server資料庫 (轉)ASP.NETC#SQLServer資料庫
- 修改SQL-SERVER資料庫表結構的SQL命令SQLServer資料庫
- oleload導excel資料Excel
- 從Excel匯入sql serverExcelSQLServer
- Sql Server 匯入另一個資料庫中的表資料SQLServer資料庫
- Excel資料透視表怎麼做 Excel資料透視表技巧Excel
- HTML表單與PHP進行資料互動HTMLPHP
- SQL Server資料庫恢復,SQL Server資料恢復,SQL Server資料誤刪除恢復工具SQLRescueSQLServer資料庫資料恢復
- EXCEL資料上傳到SQL SERVER中的簡單實現方法ExcelSQLServer
- SQL server資料庫表碎片比例查詢語句SQLServer資料庫
- Sql Server中判斷表或者資料庫是否存在SQLServer資料庫
- Excel 表匯入資料Excel
- Sql表和Excel中資料的轉移SQLExcel
- access開發精要(3)-子資料表