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, Apr 10 '18 at 14:26
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, Apr 10 '18 at 14:30
A DataTable is not usually the final stop - if the destination is a DB, many will import directly — Ňɏssa Pøngjǣrdenlarp, Apr 10 '18 at 14:58
you could give oledb a go - https://stackoverflow.com/questions/6813607/parsing-csv-using-oledb-using-c-sharp — Ctznkane525, Apr 10 '18 at 15:04
You have several options. VB.NET specific, you could implement the TextFieldParser(1). A generic .NET solution, you could implement an OleDb solution(2). A 3rd party option, you could implement a library like @HansPassant suggested. 1: https://msdn.microsoft.com/en-us/library/cakac7e6(v=vs.110).aspx 2: https://stackoverflow.com/a/47310774/1920035 — David, Apr 10 '18 at 15:06
Thanks everyone for your comments. It pointed me at a different direction for looking up the answer. I found LumenWorks.Framework.IO.Csv which seems to do the job just perfect. More details above. — Matt, Apr 10 '18 at 15:26
Possible duplicate of [CSV File Imports in .Net](https://stackoverflow.com/questions/1898/csv-file-imports-in-net) — IvanH, Apr 10 '18 at 20:47

score 0 · Answer 1 · answered Apr 11 '18 at 04:41

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 Answers1