开篇介绍
在 ETL 项目中还有一种比较常见的文件经常会被处理 - CSV 文件,全称 Comma-Separated Values - 字符分隔值文件。实际上它本身就是以纯文本形式存储数据的,逗号分隔,所以在 SSIS 中也完全可以按照平面文件的方式来处理这种类型的文件。
右键打开 CSV 文件,可以看到它和平常的平面文件没有什么区别,就是以半角逗号,分隔的的一个平面文件。
我们下面的例子是先加载 CSV 文件,然后处理完成之后再把数据又导出成一个 CSV 文件。
从 CSV 文件导入数据
尽管默认情况下,CSV 文件是被 Office Excel 打开的,但是实质上是一个平面文件。因此在数据流中选择的还是 Flat File Source,并在新建连接管理器的时候需要选择 .csv 文件类型以访问 CSV 数据源文件。
由于这里是中文数据源,并且文件中还包含有一些全角符号,所以处理的时候应该选择 Unicode 编码,并且第一行没有 Column Name。
由于这个 CSV 文件没有列名称,因此默认情况下会显示 Column 0, Column 1... 为了显示方便包括和匹配后面目标表上的列,这里更改了列的名称,数据类型不作处理。
目标表的结构保持和文件默认格式一致,确保所有的数据都能够正常加载成功。
CREATE TABLE [T011_STAGING_HOTEL] (
[HotelName] nvarchar(50),
[HotelAddress] nvarchar(50),
[X] nvarchar(50),
[Y] nvarchar(50),
[MinPrice] nvarchar(50)
)
设置好源和目标的 Mapping 关系,保存并执行包,发现出错了 -
查看错误信息如下:
[FF_SRC_CSV_HOTEL [2]] Error: Data conversion failed. The data conversion for column "HotelAddress" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[FF_SRC_CSV_HOTEL [2]] Error: The "FF_SRC_CSV_HOTEL.Outputs[Flat File Source Output].Columns[HotelAddress]" failed because truncation occurred, and the truncation row disposition on "FF_SRC_CSV_HOTEL.Outputs[Flat File Source Output].Columns[HotelAddress]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
[FF_SRC_CSV_HOTEL [2]] Error: An error occurred while processing file "D:\BIWORKSPACE_FILE\BIWORK_SSIS\INPUT_DIRECTORY\011\北京酒店信息.csv" on data row 46.
上面的这个报错信息告诉了我们大概以下几点原因:
- HotelAddress 列的转换,列有可能会被截断或者 Code Page 字符编码不同。
- 这个错误发生在第46行的位置。
查看源数据文件基本上就是这个问题,也就是说 CSV 文件源到目标 OLE DB Destination 控件给我们推荐的表中列的长度定义并不一定完全准确。
可以试着把 FF_SRC_CSV_HOTEL 中的 Error Output 的 Truncation 都设置为 Ignore failure。
测试并执行包,包运行是成功的,说明确实是发生了截断的错误。
查看一下已经导入的目标表,发现第46行的数据的确发生了截断。在观察这个数据的同时,我们还发现了这个问题 - 在表格中的数据存在中大量中文符号,即全角符号类似于"(",")","【】"。这类符号应该在程序中要替换成半角的符号,即常见的英文格式下的符号。
所以这里要做三件事情:
- 修改 Flat File Connection Manager 中对 HotelAddress 列长的定义。
- 要对包含有中文全角符号的列进行过滤转换处理。
- 修改表结构,增加数据列定义的长度。
在 Derived Column 中,写入表达式并替换旧的 HotelAddress
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( [HotelAddress] , "(", "("),")",")"), "【",""),"】",""),",",","),"。",".")
CREATE TABLE [T011_STAGING_HOTEL] (
[HotelName] nvarchar(50),
[HotelAddress] nvarchar(250),
[X] nvarchar(50),
[Y] nvarchar(50),
[MinPrice] nvarchar(50)
)
保存并执行包,运行成功。
查看数据表的结果,数据格式得到了统一,这就是能够解释 ETL 自身概念的一个例子 - Extract 抽取,Transform 转换,Load 加载。
CSV 文件的输出
下面我们要把这张表中的数据转出成一个 CSV 文件,那么在导出的时候由于前面说过 CSV 文件本身就是一种平面文件格式,因此选用 Flat File Destination 作为数据,源就是上面的这张表。
双击 FF_DST_HOTEL_CSV 编辑,并新建一个输出文件的链接管理器,默认选择 Delimited格式。
输出的时候选择 .csv 格式。
另外这里选择 Unicode 输出,并且 Text qualifier 选择",这是因为 Hotel Address 列种包含有逗号,需要把每列括起来以区分 CSV 文件列的分隔符逗号。
同时可以更改一下输出列的名称。
在 Flat File Destination 中完成 Mapping。
保存并运行包,整个输入和输出的流程都成功了。
检查输出的文件,尽管感觉输出的列加上了双引号看起来不自然,但是这样可以有效的分隔了列数据。