Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.4k views
in Technique[技术] by (71.8m points)

sql server - 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",
"",

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"
""

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.

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.

The error that I am getting is

[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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Try setting the column delimiter for the last column as shown below.You will need to type this in the box and not use the Combobox selection. (Or just select {LF} and edit the text and put a Comma before it) enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...