August 6, 2008 at 3:55 pm
I have a comma delimited csv flat file with 25 columns that has no text qualifer. The last column is a comment which i am finding some times has a comma in it.
Any suggestions on how I can fix this problem?
I was thinking of a script transformation but I need help writing the script..
August 6, 2008 at 5:24 pm
What exactly are you trying to do with this flat file?
If you are just loading from this file it shouldn't be a problem as the comment column is your last column.
August 6, 2008 at 9:28 pm
Try defining the row of the file as one long record and run it into a script component. The script component should count over 24 commas and put anything after that in double quotes (or some other text qualifier). Write the output to a flat file but give it a csv extension.
Then try reading in the new file as a csv file with a text qualifier.
You may need to experiment a bit. I don't know if all text fields need the text qualifier or if you can get away with only putting it on the comment.
August 7, 2008 at 7:03 am
Could you help me with the script?
August 7, 2008 at 7:12 am
can you not reexport the file with text qualifiers?
August 7, 2008 at 7:31 am
I need help with the transformation script that will parse each row and count the number of commas (probably with a script). After it gets to the 25th comma it will remove or ignore the rest on that row. Then post the row to the database.
August 7, 2008 at 7:40 am
maybe i'm not understanding what you're after, but if you reeport the file and wrap the 25th column with quotes, you won't have to write a script to parse for 25 commas. just tell the import routine that columns have text qualifiers. it'll import the 25th column as a single field, regardless of what's in it as long as it's wrapped in text qualifiers.
i guess what i'm getting at is that it sounds like you're attacking the problem too late in the process. if you don't want the 25th column imported, define your import routine to only import 24 columns. if you DO want 25 columns imported AND you can reexport your file with text qualifiers, it'd be much easier to do that than to write a script to parse the file, look for the 25th comma, then do stuff.
if i totally misunderstood your issue, disregard everything i've said up to this point: .
August 7, 2008 at 7:15 pm
Here is a section of script component that will count the commas and put the remaining text in double quotes.
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim wRowIn As String
Dim wRowOut As String
Dim wCommaCtr As Integer
Dim wPos As Integer
Dim wCommaPos As Integer
Const wMaxCommas As Integer = 24
wPos = 1
wCommaCtr = 0
wRowIn = Row.RowIn
While wCommaCtr < wMaxCommas
wCommaPos = InStr(wPos, wRowIn, ",", CompareMethod.Text)
If wCommaPos > 0 Then
wCommaCtr = wCommaCtr + 1
wPos = wCommaPos + 1
Else
wCommaPos = wMaxCommas
MsgBox("Only " + wCommaCtr.ToString + " commas found")
End If
End While
wRowOut = Left(wRowIn, wCommaPos) + Chr(34) + Right(wRowIn, Len(wRowIn) - wCommaPos) + Chr(34)
Row.FixedRow = wRowOut
End Sub
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply