Pages

Export data to CSV – (1) Database

May 30, 2018

本文以 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]