March 11, 2010 at 12:19 pm
Hi All,
I am working on importing a fixed length flat file and have a question. The file is about 60000 rows long and some of the rows are cut off half way with a carriage return. How can i go about removing these?
Thank you all in advance,
Paul
March 12, 2010 at 8:28 am
You could use a script in the data flow that passes in each row and loops through the characters, writing all but CRs to the output row. If there is a header row with column names, you might need to skip that one to avoid trashing the header row delimiter.
March 12, 2010 at 11:23 am
Thanks for your help. I havent done much scripting could you please give me an example of how to get started with that?
March 12, 2010 at 12:05 pm
You'll need to set up your script component to take the entire row in as input, and add an output column to pass out the corrected row. Since each row will pass through the script, you'll only need a single loop, to check the values of each character. Here is some quick and dirty code to start with:
Dim RowLen As Int32
Dim n As Int32
Dim RowString As String
Dim CurrentChar As String
Dim RowOut As String = ""
RowString = Row.ToString
RowLen = RowString.Length
For n = 1 To RowLen
CurrentChar = RowString.Substring(n, 1)
If CurrentChar <> Chr(13) Then
RowOut = RowOut & CurrentChar
End If
Next n
Row.<whatever script output row variable is named> = RowOut
This will obviously strip any CRs off the end of the row as well, but since it's fixed width, that shouldn't be a problem. If it is, you can throw a conditional in to only strip one CR per row, then exit out of the loop.
Hope this helps. My VB is pretty rusty, but I spent years doing it pre .NET before I got into data wrangling.
March 12, 2010 at 12:13 pm
Awesome, thanks so much for your help, this is going to save me so much time. I can take it from here.
Thanks again,
Paul
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply