SSIS 2012：修剪CSV输出中的尾随逗号 [英] SSIS 2012: Trim trailing commas from a CSV output

问题描述

我有一个文件，我不能控制输出，在每个数据行而不是标题行有一个结尾逗号。

I have a file, I cannot control the output, that has a trailing comma on each data row and not the header row.

"Pid","Dtdate","Tencode","Stype","Amount","Currentbal"
"13773793","05/26/2014","Abc123","PAYMENT","50.00","1807.95",
"13773794","05/26/2014","Abc123","ADJUSTMENT","10.00","18.95",
"",

我想弄清楚如何修剪尾部逗号的每一行，以便它能正确读入SSIS。

I am trying to figure out how to trim each row of the trailing comma so that it will read in SSIS correctly.

"Pid","Dtdate","Tencode","Stype","Amount","Currentbal"
"13773793","05/26/2014","Abc123","PAYMENT","50.00","1807.95"
"13773794","05/26/2014","Abc123","ADJUSTMENT","10.00","18.95"
""

脚本转换，但这是我遇到的地方，我不能得到程序太多，所以我在VB脚本任务很新手。

I assume I would use a Script Transformation but that is where I get stuck, I don't get to program much so I am pretty novice in the VB Script Tasks.

可能值得一提的是在FOR每个循环中抓取一个文件夹内的所有文件。通常只有一个文件，但是我为多个的潜在空间，文件名是文件名的变量表达式：@ [User :: FileName]

Might be worth mentioning this is in a FOR each loop to grab all files within a folder. There is usually only one file but I am making room for the potential of multiples and the file name is a variable expression of the filename: @[User::FileName]

我的假设是，一旦文件被动态生成，你失去了规定列规则的能力。这是为什么我正在寻找一种方法来修剪每一行的尾随逗号。

My assumption is that once the file is dynamically generated that you lose the ability to dictate the column rules. That is why I was looking for a way to trim the trailing comma of each row.

我得到的错误是

[Flat File Source [18]] Error: The column delimiter for column "Currentbal" was not found. 
[Flat File Source [18]] Error: An error occurred while skipping data rows.

我假设这是因为尾随逗号告诉系统应该有另一列没有相关的标题。一旦一个结束方法被删除，文件就会被删除。

I assume it is because the trailing comma is telling the system there should be another column with no associated header. Once trailing commas are removed the file processes fine.

推荐答案

更简单的方法是更改​​你的连接管理器，

An even simpler approach would be to change your connection manager to skip the header row.

• 要跳过的标题行：1


• 第一个数据行中的列名称：未选中

这篇关于SSIS 2012：修剪CSV输出中的尾随逗号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！