How to remove extra line feeds within double quoted fields

jbpm 0
Viewed 51

Newbie here. Code below removes ALL line feeds in my file but it also removes EOR line feeds. Can somebody please help me how to fix code below so it only removes extra line feeds within double quoted fields? Any help will be greatly appreciated. Thanks

Public Sub Main()
    '
    Dim objReader As IO.StreamReader
    Dim contents As String

    objReader = New IO.StreamReader("testfile.csv")
    contents = objReader.ReadToEnd()
    objReader.Close()

    Dim objWriter As New System.IO.StreamWriter("testfile.csv")
    MsgBox(contents)
    'contents = Replace(contents, vbCr, "")
    contents = Replace(contents, vbLf, "")
    MsgBox(contents)
    objWriter.Write(contents)
    objWriter.Close()
    '
    Dts.TaskResult = ScriptResults.Success
End Sub

I forgot to mention that the input file name changes daily, How do I code so it doesn't care for the file name as long as it as a CSV file? So testfile name has current date and changes daily. I've tried just the file path and it errored out as well. Used the *.csv and it didnt like that either.

objReader = New IO.StreamReader("\FolderA\FolderB\TestFile09212022.csv")

vb.net
1 Answers

If you are sure there are no double quotes text inside the double quotes you can do it like this:

Dim sNewString As String = ""
Dim s As String
Dim bFirstQuoted As Boolean = False
Dim i As Integer

Dim objWriter As New System.IO.StreamWriter("testfile.csv")
MsgBox(contents)

For i = 1 To contents.Length
    s = Mid(contents, i, 1)
    If s = """" Then bFirstQuoted = Not bFirstQuoted
    If Not bFirstQuoted OrElse (s <> vbLf AndAlso bFirstQuoted) Then
        sNewString += s
    else
        sNewString += " "
    End If
Next

MsgBox(sNewString )
objWriter.Write(sNewString )
objWriter.Close()
Dts.TaskResult = ScriptResults.Success
userkyO910
Related