CSV to DataTable

Question

I wonder what is my best option to parse CSV into a DataTable.

My CSV is in this format

Separator: comma
Delimiter: double quotes (e.g."string")
Escape: 2x double quotes

Here is the example:

Col1,Col2,Col3,Col4
1234,active,"This is a long text with new lines, commas and all", XGF232
1235,active,"This is also a ""long"" text with new lines and all", XHD233

The problem is that numbers or short; one word strings are not enclosed within quotes.
The other problem is that long strings which are enclosed within quotes that also have a "string" nested are escaped with ""string"".

PS: Excel understands this format without an issue.

EDIT:

The comments pointed me at a different options. I searched the libraries available and I found LumenWorks.Framework.IO.Csv which seems to do the job just perfect.

Using csv As CsvReader = New CsvReader(New StreamReader(filename), True)
                Dim fieldCount = csv.FieldCount
                Dim headers() As String = csv.GetFieldHeaders()
                dt.Load(csv)
End Using

I wouldn't consider a document that conforms to the CSV rules a problem :) - what kind of answer are you expecting? — C.Evenhuis
I was trying to use the loops with splitting, however this brought certain issues, which I think I could solve however this becomes too complicated and I believe there are better ways. Looking at CSVHelper right now. — Matt
A DataTable is not usually the final stop - if the destination is a DB, many will import directly — Ňɏssa Pøngjǣrdenlarp
you could give oledb a go - stackoverflow.com/questions/6813607/… — Ctznkane525

ASH ASH · Accepted Answer · 2018-04-11T04:41:00

Can you import from DGV to Excel?

Imports System.Data.SqlClient
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\vb.net-informations.xls';Extended Properties=Excel 8.0;")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
        MyCommand.TableMappings.Add("Table", "Net-informations.com")
        DtSet = New System.Data.DataSet
        MyCommand.Fill(DtSet)
        DataGridView1.DataSource = DtSet.Tables(0)
        MyConnection.Close()

    End Sub
End Class

CSV to DataTable

1 Answers