troubleshooting Question

How To Find and Replace In Specific Column of CSV File

Avatar of endrec
endrec asked on
Visual Basic.NET
6 Comments1 Solution245 ViewsLast Modified:
I wanted to know if someone has ever had to find and replace on a specific column of a .CSV and/or could help me find a way to either modify the code below or use completely new logic to perform a find and replace on individual columns of an entire file.  One of the issues is that none, one, many, or all of the field in the input string could be surrounded by quotes and it's still supposed to be considered valid.

In the function below strInput in the output of a streamreader, e.g.:
           Dim sr2 As New StreamReader(strFileName)
            Dim InOutput As String = sr2.ReadToEnd()
            sr2.Close()
            sr2.Dispose()


So to call the function it would be something like...fncReplaceInSpecificColumn(InOutput, "Company 2345", "Company Y", 4)


    Private Function fncReplaceInSpecificColumn(ByRef strInput As String, ByVal strFind As String, ByVal strReplace As String, ByVal intColumn As Integer) As String()
        Dim r As New Regex(ControlChars.CrLf)
        Dim indivlines() As String = r.Split(strInput)

        'Cycle through lines/records
        For i As Integer = 0 To indivlines.Length - 1
            'Split each line into individual fields by comma or quote enclosed comma delimiter
            Dim splitout() As String = ParseQuotedCSV3(indivlines(i))

            If indivlines(i) <> "" And Len(intColumn) > 0 Then

                If intColumn - 1 > UBound(splitout) Then
                    MsgBox("You cannot specify a column for find and replace that is greater than the number of columns in the input file.")
                    Exit Function
                End If

                splitout(intColumn - 1) = Replace(splitout(intColumn - 1), strFind, strReplace)

                '***IS THERE A WAY TO RECONSTRUCT THE .CSV IN IT'S ORIGINAL FORMAT (WHICH COULD BE WITH OR WITHOUT QUOTES SURROUNDING ONE OR MORE OF THE FIELDS)?

            End If
        Next
    End Function


The following function is one I used from Experts Exchange to split an incoming CSV up into fields regardless of if the fields were commas delimited or surrounded in quotes (part of the problem).  So it deconstructs the CSV instead of reconstructing it in it's original format.  I think I need a way to reconstruct it after finding and replacing on a specific column.

The .CSV could come in with variations like:
Field1,2,3,"4","5",6
"1","2","3","4,,,,","5","6"
or
1,2,3,4,5,6


    Private Function ParseQuotedCSV3(ByVal strInput As String) As String()
        If strInput <> "" Then
            Dim a As New ArrayList
            Dim s1 As String = strInput.Replace(Chr(4), "") '<<< NEW LINE
            s1 = s1.Replace(Chr(23), "")
            s1 = s1.Replace(Chr(24), "")
            s1 = s1.Replace(Chr(25), "")
            Dim s As String() = s1.Split(",") '<<< ALTERED LINE

            Dim i As Integer
            For i = s.Length - 1 To 1 Step -1
                If s(i).TrimEnd.Length > 0 Then
                    If s(i).TrimEnd.LastIndexOf(Chr(34)) = s(i).TrimEnd.Length - 1 Then
                        If s(i).TrimStart.IndexOf(Chr(34)) <> 0 Then
                            s(i - 1) = s(i - 1) & "," & s(i)
                            s(i) = "|||" 'This is an 'ignore' marker which needs to be set to something that would never appear in the input string
                        End If
                    End If
                End If
            Next
            For i = 0 To s.Length - 1
                If s(i) <> "|||" Then 'See comment above
                    If s(i).TrimStart.IndexOf(Chr(34)) = 0 Then
                        s(i) = s(i).Remove(s(i).IndexOf(Chr(34)), 1)
                        s(i) = s(i).Remove(s(i).LastIndexOf(Chr(34)), 1)
                    End If
                    a.Add(s(i).ToString)
                End If
            Next
            Dim ss(a.Count - 1) As String
            a.CopyTo(ss)
            Return ss

        End If

    End Function
ASKER CERTIFIED SOLUTION
Sancler

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros