June 29, 2010 at 9:41 am
I am attempting to import a csv file in a funky format. I can use the {CR}{LF} row delimiter and {,} column delimiter with the "Column names in the first data row" box checked. However, the very last line in the file is the problem. The last field in every record is empty, so the last thing coming through is a comma. That's blowing up my feed. I thought I'd figured it out by going into Advanced and for the last column setting just that column to use ColumnDelimiter {,}{CR}{LF}. (I literally copy/pasted it in & it seemed to take it.) I thought I'd fixed it until I ran in a test file with a single record and it brought nothing at all in. (Since the header record is the only one that doesn't end with a comma.) It appears that I can't set a special column delimiter for just that last column. When I copy/pasted it to change it, it changed it for all the columns which meant I lost my first real row of data. Can anybody suggest anything? I'm attaching the csv with the problem.
Phooey...it won't allow a .csv attachment. If I change it to .txt I'm afraid the problem won't be visible. I'm attaching a Word doc with screenshots instead.
June 29, 2010 at 3:01 pm
You're probably not going to like it though..
You can use a script task as a data-flow data source, you read the data directly and map it into the columns you define coming out, you can read nearly anything using this method AND handle wacky format problems..
CEWII
August 9, 2010 at 8:01 am
I've determined that all I need to do to make this work is to open up the file and add a comma to the end of the header record. (I would replace the word trk12paper with trak12paper,. Can anyone help me with a script that would do this so that it wouldn't require manual intervention? My file name and location path would always be the same and the field I would need to modify (trk12paper) would always be the same. I don't know any VB or scripting languages, so I'm pretty clueless. I did some web surfing and the examples I find are always more complicated that what I'm going for. I know this is a SQL forum, but I'm guessing there are some scripting gurus out there, too. Thanks in advance!
August 9, 2010 at 10:26 am
lduvall (8/9/2010)
I've determined that all I need to do to make this work is to open up the file and add a comma to the end of the header record. (I would replace the word trk12paper with trak12paper,. Can anyone help me with a script that would do this so that it wouldn't require manual intervention? My file name and location path would always be the same and the field I would need to modify (trk12paper) would always be the same. I don't know any VB or scripting languages, so I'm pretty clueless. I did some web surfing and the examples I find are always more complicated that what I'm going for. I know this is a SQL forum, but I'm guessing there are some scripting gurus out there, too. Thanks in advance!
Here's a simple script task that might be good enough:
Public Sub Main()
Dim fs As FileStream = File.OpenRead("C:\Temp\Test.txt")
Dim sr As New StreamReader(fs)
Dim text As String = sr.ReadToEnd()
fs.Close()
' Split file data into array of lines
Dim lines() As String = Split(text, vbCrLf)
'Add a comma to the end of line 1
lines(0) = lines(0) & ","
' Convert array back into string variable
text = Join(lines, vbCrLf)
'Now write the amended file to disk
Dim sw As StreamWriter = File.CreateText("C:\Temp\Test1.txt")
sw.Write(text)
sw.Close()
Dts.TaskResult = ScriptResults.Success
End Sub
It reads a text file C:\Temp\Test.txt, adds a comma to the end of line 1 and then writes out the amended file to C:\Temp\Test1.txt.
Just replace your Sub Main ... End Sub with the above and change the file-specs to get something working.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 9, 2010 at 11:47 am
Phil--Thank you so much for trying to help me. Can you tell me exactly where I would put the script? Can I just paste it into notepad and give it a .bat extension or something? Or do I need to add a task to my SSIS package to put it in?
August 9, 2010 at 11:53 am
No problem. Add a Script Task, open the properties and Edit Script. You'll find a load of template code already in there. Replace the bit from
Sub Main
to
End Sub
with the code I gave you (modified to suit your own environment).
Your DF task will come after this script task, taking the output file (test1.txt in my example) as its input.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 9, 2010 at 12:00 pm
Eureka! That worked perfectly!!! Thank you again very much for your help!
August 10, 2010 at 2:02 am
Thanks for posting back & I'm glad to have helped.
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply