[2005] Comma Delimited with Quotes

[rjdpa]

I just want to import a comma delimited file into a datagridview. The catch is that the values are enclosed in " (double quotes). I know how to grab records from a comma delimited file using the split function, but how would I determine the values that are enclosed with the quotes and also not have the quotes show up when they are in the grid?

I did a search on the forums, but came up with different results like how to eliminate commas within values, etc... I just need a code sample.

[JuggaloBrotha]

test the string's beginning and end for a quote, if both are true then store only the middle conetents:

Dim Str As String = """This string is quoted"""
If Str.StartsWith("""") = True AndAlso Str.EndsWith("""") = True Then
  Str = Str.SubString(1, Str.Length - 2)
End If

[rjdpa]

right, but let's say that in one of the values there is a comma... i don't want that to split up the values.

[bmahler]

You can use the Microsoft Text Driver to query a comma seperated file with SQL. Here is an example function that will do just that

    Private Function LoadCSV(ByVal FilePath As String) As DataTable
        If IO.File.Exists(FilePath) Then
            Dim dt As New DataTable
            Try
                Dim rootPath As String = IO.Path.GetDirectoryName(FilePath)
                Dim fileName As String = IO.Path.GetFileName(FilePath)
                Dim connString As String = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + rootPath.Replace("\", "\\") + ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
                Dim SQL As String
                Dim conn As New System.Data.Odbc.OdbcConnection(connString)
                conn.Open()
                SQL = "SELECT * FROM [" & fileName & "]"
                Dim da As New System.Data.Odbc.OdbcDataAdapter(SQL, conn)
                da.Fill(dt)
                conn.Close()
                conn.Dispose()
                Return dt
            Catch ex As Exception
                Return Nothing
            End Try
        Else
            Return Nothing
        End If
    End Function

[JuggaloBrotha]

right, but let's say that in one of the values there is a comma... i don't want that to split up the values.

You run something similar to my code after you've already split the data by the comma

[BruceG]

IMHO, the easiest way to do this is with the Input function, which is a retooled version of the Input # statement in earlier versions of VB and BASIC. It is specifically design to handle comma-delimited files that have fields that may be enclosed in quotes. Here is an example of a console program that reads such a file and displays its records on the console:

Module Module1 

    Sub Main() 

        Dim strFileName As String = My.Application.Info.DirectoryPath _
                                  & "\employee_comma.txt"
        Dim intEmpFileNbr As Integer

        Dim strEmpName As String
        Dim intDeptNbr As Integer
        Dim strJobTitle As String
        Dim dtmHireDate As Date
        Dim sngHrlyRate As Single

        Console.WriteLine("The records in the employee_fixed.txt file are:")
        Console.WriteLine("")
        Console.WriteLine("EMPLOYEE NAME".PadRight(20) _
                        & Space(3) _
                        & "DEPT" _
                        & Space(3) _
                        & "JOB TITLE".PadRight(21) _
                        & Space(3) _
                        & "HIRE DATE " _
                        & Space(3) _
                        & "HRLY RATE")

        Console.WriteLine("-------------".PadRight(20) _
                        & Space(3) _
                        & "----" _
                        & Space(3) _
                        & "---------".PadRight(21) _
                        & Space(3) _
                        & "--------- " _
                        & Space(3) _
                        & "---------")

        intEmpFileNbr = FreeFile()

        FileOpen(intEmpFileNbr, strFileName, OpenMode.Input)

        Do Until EOF(intEmpFileNbr)
            ' Read one "record's worth" of fields into their 
            ' corresponding variables
            Input(intEmpFileNbr, strEmpName)
            Input(intEmpFileNbr, intDeptNbr)
            Input(intEmpFileNbr, strJobTitle)
            Input(intEmpFileNbr, dtmHireDate)
            Input(intEmpFileNbr, sngHrlyRate)

             ' Output the data to the console as a formatted line ... 
            Console.WriteLine(strEmpName.PadRight(20) _
                            & Space(3) _
                            & intDeptNbr.ToString.PadLeft(4) _
                            & Space(3) _
                            & strJobTitle.PadRight(21) _
                            & Space(3) _
                            & Format(dtmHireDate, "MM/dd/yyyy").PadRight(10) _
                            & Space(3) _
                            & Format(sngHrlyRate, "Currency").PadLeft(9))

        Loop

        FileClose(intEmpFileNbr)

        Console.WriteLine("")
        Console.WriteLine("Press Enter to close this window.")
        Console.ReadLine()

    End Sub

End Module

To try the example, create a text file called "employee_comma.txt", and save it to your project's bin\debug directory. The contents of the file are:
"ANDERSON,ANDY",100,PROGRAMMER,3/4/1997,25
"BABCOCK,BILLY",110,SYSTEMS ANALYST,2/16/1996,33.5
"CHEESEMAN,CHARLIE",100,COMPUTER OPERATOR,3/1/1996,15
"DUNCAN,DARLENE",200,RECEPTIONIST,10/11/1998,12.75
"EACHUS,ERNIE",300,MAIL ROOM CLERK,8/19/1997,10

[Shaggy Hiker]

Splitting on commas won't work, so splitting before removing the double quotes won't work. However, there are two solutions depending on whether ALL your values are quote enclosed, or just some.

If it is just some (as in BruceG's example), then BruceG's suggestion is the way to go.

However, if ALL your values are quote enclosed, as your original post suggested they were, then split on the string:

","

Heck, it's hard to even type that so it is readable. Split on quote, comma, quote, and you should get the right splits, since embedded commas won't have quotes on either side of them.

[stanav]

Use ADO.Net and you can fill a datatable with data from your csv file. The Jet engine will correctly parse out the fields for you. Once you have the datatable filled, you can just bind it to your datagridview.

Have a look at this thread. Post#5 is a function which you just pass in the path to your csv file and it'll spit out a datatable.

http://www.vbforums.com/showthread.p...&highlight=csv

[bmahler]

^^^ Points at post #4 in this thread...

[jmcilhinney]

Use a TextFieldParser object. It has a HasFieldsEnclosedInQuotes property that is set to True by default.

Sample data:

1,"Smith, Peter",19/06/1969
2,"Paul",1/01/2000
3,"Jones, Mary",14/06/2008

The code:

Dim fields As String()

Using parser As New FileIO.TextFieldParser("file path here")
    parser.Delimiters = New String() {","}

    Do Until parser.EndOfData
        fields = parser.ReadFields()

        MessageBox.Show(String.Format("ID: {1}{0}Name: {2}{0}Date: {3}", _
                                      Environment.NewLine, _
                                      fields(0), _
                                      fields(1), _
                                      fields(2)))
    Loop
End Using

Try it out for yourself.

It will also work if the data doesn't have quotes around the text fields that don't need it. Try it again on this data:

1,"Smith, Peter",19/06/1969
2,Paul,1/01/2000
3,"Jones, Mary",14/06/2008

For a real test, try it again on this data:

1,"Peter ""Mad Dog"" Smith",19/06/1969
2,Paul,1/01/2000
3,"Jones, Mary",14/06/2008

I wouldn't fancy writing the code to parse that myself.