Printable View
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.
test the string's beginning and end for a quote, if both are true then store only the middle conetents:
Code: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
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 can use the Microsoft Text Driver to query a comma seperated file with SQL. Here is an example function that will do just that
Code: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
You run something similar to my code after you've already split the data by the commaQuote:
Originally Posted by rjdpa
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:
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:Code: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
"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
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.
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
^^^ Points at post #4 in this thread... :)
Use a TextFieldParser object. It has a HasFieldsEnclosedInQuotes property that is set to True by default.
Sample data:The code:Code:1,"Smith, Peter",19/06/1969
2,"Paul",1/01/2000
3,"Jones, Mary",14/06/2008
Try it out for yourself.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
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:For a real test, try it again on this data:Code:1,"Smith, Peter",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.Code:1,"Peter ""Mad Dog"" Smith",19/06/1969
2,Paul,1/01/2000
3,"Jones, Mary",14/06/2008