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 each 循环中抓取文件夹中的所有文件.通常只有一个文件，但我正在为可能的倍数腾出空间，文件名是文件名的变量表达式:@[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屋！