Export data to CSV – (1) Database

本文以 bcp 產生 csv 檔案工作流程的實作說明,當使用者要求匯出一份資料時,先將查詢語法組合為字串並建立新工作,透過 SQL Server Agent 排程執行輸出檔案。


bcp 基本語法

bcp 指令是 SQL Server 可以做大量資料匯入與匯出,在 DOS Command 環境下中執行,先用幾個範例認識 bcp 指令的用法。

(本文的範例是 Northwind 資料庫)

資料來源指定一個資料表或檢視

bcp dbo.Region out "E:\Region.csv" -S "(local)" -d Northwind -U sa -P mis123 –c


image


image


資料來源是一個 T-SQL 查詢語法

bcp "select * from dbo.Region" queryout "E:\Region2.csv" -t "," -S "(local)" -d Northwind -U sa -P mis123 -c


資料來源是執行一個預存程序

bcp "EXEC [dbo].[CustOrderHist] 'ALFKI'" queryout "E:\CustOrderHist.csv" -t "," -S "(local)" -d Northwind -U sa -P mis123 -c


加強 csv 在 Excel 的可讀性

產生欄位標題

以 bcp 產生的 csv 檔案是不包括欄位標題,找到解決方式是先產生一個欄位標題文字檔,再與 匯出的 csv 檔案合併為另一個檔案。

COPY 指令範例

COPY /V /Y /B E:\Temp\Header.txt + E:\Temp\DATA.temp E:\Temp\DATA.csv

DEL E:\Temp\DATA.temp

Header.txt 是欄位標題

DATA.temp 是 bcp 產生的 csv 格式的檔案

DATA.csv 是合併的新檔案名稱


當資料來源是預存程序時,SQL Server 2012 以後的版本可用 T-SQL 語法查詢得到欄位的標題

SELECT name
FROM sys.dm_exec_describe_first_result_set_for_object
(
	OBJECT_ID('dbo.CustOrdersOrders'), 
	NULL
);


資料內容可讀性

當使用者以 Excel 開啟 csv 檔案,遇到以下幾個情況無法閱讀

(1) 資料型別為日期或時間

如以下 csv 第 2 ~ 4 欄為日期格式

10643,1997-08-25 00:00:00.000,1997-09-22 00:00:00.000,1997-09-02 00:00:00.000
10692,1997-10-03 00:00:00.000,1997-10-31 00:00:00.000,1997-10-13 00:00:00.000
10702,1997-10-13 00:00:00.000,1997-11-24 00:00:00.000,1997-10-21 00:00:00.000
10835,1998-01-15 00:00:00.000,1998-02-12 00:00:00.000,1998-01-21 00:00:00.000
10952,1998-03-16 00:00:00.000,1998-04-27 00:00:00.000,1998-03-24 00:00:00.000
11011,1998-04-09 00:00:00.000,1998-05-07 00:00:00.000,1998-04-13 00:00:00.000

在 Excel 中呈現如下圖

image

(2) 文件前字元為零,如 00001234 會呈現 1234


若使用者主要以 Excel 開啟閱讀,解決方式則輸出資料時,改為公式

="00001234"


工作流程實作

範例實作在 Northwind 資料庫中,建立一個工作流程資料表,與相關流程作業的預存程序。

下載: Northwind with Export CSV procedure

image

運作方式

(1) 使用者介面呼叫預存程序 CustOrdersOrders_ExportTask 建立一個新工作

(2) SQL Server Agent 排程執行 SysPrint_BigQuery_DoTask 讀取資料並匯出 csv 檔案


參考文章

  1. SQL Server 啟用 xp_cmdshell 

    [open doc]

  2. bcp Utility

    [open doc]

  3. bcp Export to CSV with headers

    [open technet]

  4. Numeric fields lose leading zero while writing CSV in c#

    [open stackoverflow]


Popular posts from this blog

Google Map 多點路線規劃

Oppo R9 Turn on/off developer mode

解決瀏覽器無法下載檔案