CSV/Text Delimiting – DeQuote string

Removes surrounding delimiters and decodes escaped delimiters in a delimited text block (e.g., CSV-style content).

The function performs the following steps for each line:

Removes double delimiters around empty fields.
Strips leading and trailing empty quoted fields.
Replaces escaped delimiters (e.g., “” for quotes) with a temporary placeholder.
Removes all remaining quote characters from the line.
Restores the original delimiter where placeholders were used.

    Public Function DeQuote(Text As String, 
                            Optional Delimiter As String = """", 
                            Optional FieldSeparator As String = ",") As String

        Dim arRecords As String() = Text.Split(New String() {vbCrLf}, StringSplitOptions.RemoveEmptyEntries)
        Dim szToReturn As String = String.Empty

        Dim iLenDelimiter As Integer = Delimiter.Length
        Dim iLenSeparator As Integer = FieldSeparator.Length
        Dim iTotLen As Integer

        Try

            For Each szRecord In arRecords

                'Just an empty string
                If szRecord = Delimiter + Delimiter Then szRecord = String.Empty

                'Remove empty fields between fields separators
                szRecord = szRecord.Replace(FieldSeparator + Delimiter + Delimiter + FieldSeparator, FieldSeparator + FieldSeparator)

                'Strip first Field if empty 
                iTotLen = iLenDelimiter + iLenDelimiter + iLenSeparator
                If Left(szRecord, iTotLen) = Delimiter + Delimiter + FieldSeparator Then
                    szRecord = Right(szRecord, szRecord.Length - iLenDelimiter - iLenDelimiter)
                End If

                'Strip last Field if empty 
                iTotLen = iLenSeparator + iLenDelimiter + iLenDelimiter
                If Right(szRecord, iTotLen) = FieldSeparator + Delimiter + Delimiter Then
                    szRecord = Left(szRecord, szRecord.Length - iLenDelimiter - iLenDelimiter)
                End If

                'Empty fields with just two field delimiter, example:  ,"", --> ,,
                szRecord = szRecord.Replace(FieldSeparator + Delimiter + Delimiter + FieldSeparator, FieldSeparator + FieldSeparator)

                'Replace a field delimiter (doubled) inside field values with a dummy delimiter, example: "" --> $DUMMYSEP$
                szRecord = szRecord.Replace(Delimiter + Delimiter, "$DUMMYSEP$")

                'Remove any field delimiter, example:  " --> (empty)
                szRecord = szRecord.Replace(Delimiter, String.Empty)

                'Restore the delimeter (single) inside field values from dummy separator, example: $DUMMYSEP$ --> "
                szRecord = szRecord.Replace("$DUMMYSEP$", Delimiter)

                szToReturn = szToReturn + szRecord + vbCrLf
            Next

        Catch ex As Exception
        End Try

        'Strip the last CrLf added by the process
        szToReturn = szToReturn.Remove(szToReturn.Length - vbCrLf.Length)

        Return szToReturn
    End Function

    Public Function DeQuote(Text As String, 
                            Optional Delimiter As String = """", 
                            Optional FieldSeparator As String = ",") As String

        Dim arRecords As String() = Text.Split(New String() {vbCrLf}, StringSplitOptions.RemoveEmptyEntries)
        Dim szToReturn As String = String.Empty

        Dim iLenDelimiter As Integer = Delimiter.Length
        Dim iLenSeparator As Integer = FieldSeparator.Length
        Dim iTotLen As Integer

        Try

            For Each szRecord In arRecords

                'Just an empty string
                If szRecord = Delimiter + Delimiter Then szRecord = String.Empty

                'Remove empty fields between fields separators
                szRecord = szRecord.Replace(FieldSeparator + Delimiter + Delimiter + FieldSeparator, FieldSeparator + FieldSeparator)

                'Strip first Field if empty 
                iTotLen = iLenDelimiter + iLenDelimiter + iLenSeparator
                If Left(szRecord, iTotLen) = Delimiter + Delimiter + FieldSeparator Then
                    szRecord = Right(szRecord, szRecord.Length - iLenDelimiter - iLenDelimiter)
                End If

                'Strip last Field if empty 
                iTotLen = iLenSeparator + iLenDelimiter + iLenDelimiter
                If Right(szRecord, iTotLen) = FieldSeparator + Delimiter + Delimiter Then
                    szRecord = Left(szRecord, szRecord.Length - iLenDelimiter - iLenDelimiter)
                End If

                'Empty fields with just two field delimiter, example:  ,"", --> ,,
                szRecord = szRecord.Replace(FieldSeparator + Delimiter + Delimiter + FieldSeparator, FieldSeparator + FieldSeparator)

                'Replace a field delimiter (doubled) inside field values with a dummy delimiter, example: "" --> $DUMMYSEP$
                szRecord = szRecord.Replace(Delimiter + Delimiter, "$DUMMYSEP$")

                'Remove any field delimiter, example:  " --> (empty)
                szRecord = szRecord.Replace(Delimiter, String.Empty)

                'Restore the delimeter (single) inside field values from dummy separator, example: $DUMMYSEP$ --> "
                szRecord = szRecord.Replace("$DUMMYSEP$", Delimiter)

                szToReturn = szToReturn + szRecord + vbCrLf
            Next

        Catch ex As Exception
        End Try

        'Strip the last CrLf added by the process
        szToReturn = szToReturn.Remove(szToReturn.Length - vbCrLf.Length)

        Return szToReturn
    End Function

Posted by ipercube