Flat File Connection Manager -- last row has diff col delimiter

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

  • 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

  • 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!

  • 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

  • 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?

  • 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

  • Eureka! That worked perfectly!!! Thank you again very much for your help!

  • 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