none
Removing an unwanted carriage return from a comma delimited csv file using a script task

    Question

  • I have a comma delimted csv file that I am trying to load via Flat File Source task. However, in column notes, some of the rows have unwanted carraige returns that is causing the package to fail. 

    So to remove the unwanted carriage returns in the notes column, I want to use a Script Task and .Net Code.

    Does anyone know how to remove unwanted carriage return in a column of a CSV file using a Script Task with .Net Code?

    Thanks 

     


    Kajoo
    • Moved by Bob BeaucheminMVP Wednesday, March 10, 2010 4:10 AM Moved to a more relevent forum (From:.NET Framework inside SQL Server)
    Wednesday, March 10, 2010 1:05 AM

All replies

  • Have you tried using the String.Replace that should allow you to strip off all occurances of the CR and/or LF characters.
    Thursday, March 11, 2010 1:48 PM
  • Try out this code to replace the new line chars. It will remove all the carriage returs not sure what you mean by UNWANTED.
    Once you have the modified file data you could save to the file.
    Dim strContents As String
            Dim objReader As StreamReader
            Try
    
                objReader = New StreamReader("C:Path\CarriageReturn.txt")
                strContents = objReader.ReadToEnd()
                objReader.Close()
            Catch Ex As Exception
                MsgBox(Ex.Message)
            End Try
            MsgBox(strContents)
            strContents = strContents.Replace(Environment.NewLine, "")
            MsgBox(strContents)

    Sudeep's Blog
    Thursday, March 11, 2010 3:02 PM
  • Thanks guys!

    I tried using the script above but I kept on getting the same error.

    [Flat File Source [1]] Error: The column delimiter for column "Notes" was not found.

    The problem here is that one of the columns of my csv file, Notes, is corrupted. It has the quotes wrapped around the column. However, in some of the rows, there is a carriage return which breaks the column into another row and that's where it causes the error because it thinks it is another row which is not.

    So the sloution would be to remove the unwanted carraige return in the Notes column.

    Thanks

    Kajoo
    Thursday, March 11, 2010 7:33 PM
  • Thanks Sudeep Jas!

    I am not familiar with VB at all and I am trying to figure out how to resolve the problem with this script

     I am trying to have this script work but I keep getting an error.

    In the script, the StreamReader is highlighted and when rolling over the StreamReader it states that the Type StreamReader is not defined.

    And then in the second portion (Try section), it has the same thing for the New StreamReader (Type StreamReader is not defined)

     

     

    Dim objReader As StreamReader

    objReader = New StreamReader = "C:\AMN\MDM\Taxonomy\Taxonomy.csv"

     

     

     

    So to solve this issue I defined the StreamReader with one of the options as such, but it still has a line under the New StreamReader

            Dim strContents As String
            Dim objReader As IO.StreamReader
            Try
    
                objReader = New IO.StreamReader = "C:\AMN\MDM\Taxonomy\Taxonomy.csv"
                strContents = objReader.ReadToEnd()
                objReader.Close()
            Catch Ex As Exception
                MsgBox(Ex.Message)
            End Try
            MsgBox(strContents)
            strContents = strContents.Replace(Environment.NewLine, "")
            MsgBox(strContents)
    		Dts.TaskResult = Dts.Results.Success
    	End Sub

    Any ideas on how that can be handled?

    Thanks

     

     


    Kajoo
    Wednesday, April 07, 2010 4:51 PM
  • Thanks Sudeep Jas!

    I am not familiar with VB at all and I am trying to figure out how to resolve the problem with this script

     I am trying to have this script work but I keep getting an error.

    In the script, the StreamReader is highlighted and when rolling over the StreamReader it states that the Type StreamReader is not defined.

    And then in the second portion (Try section), it has the same thing for the New StreamReader (Type StreamReader is not defined)

     

     

    Dim objReader As StreamReader

    objReader = New StreamReader = "C:\AMN\MDM\Taxonomy\Taxonomy.csv"

     

     

     

    So to solve this issue I defined the StreamReader with one of the options as such, but it still has a line under the New StreamReader

            Dim strContents As String
            Dim objReader As IO.StreamReader
            Try
    
                objReader = New IO.StreamReader = "C:\AMN\MDM\Taxonomy\Taxonomy.csv"
                strContents = objReader.ReadToEnd()
                objReader.Close()
            Catch Ex As Exception
                MsgBox(Ex.Message)
            End Try
            MsgBox(strContents)
            strContents = strContents.Replace(Environment.NewLine, "")
            MsgBox(strContents)
    		Dts.TaskResult = Dts.Results.Success
    	End Sub

    Any ideas on how that can be handled?

    Thanks

     

     


    Kajoo
    Wednesday, April 07, 2010 4:51 PM