How do I convert CSV to datatable using LINQ in VB.NET

Question

0.00/5 (No votes)

See more:

Sub NoNonsenseCSV2DataTable(CSVfile As String)
'A,B,C,D,E
'00001,4,1,2,3560
'00002,4,12,1,2000
'00003,1,1,2,4500
'00004,4,12,1,2538.63
'00005,1,1,2,3400
'00006,1,1,2,2996.48
Dim dTable As New DataTable
Using reader As New StreamReader(CSVfile)
    '_______________________________________________________________________
    Dim CSVheader As String = reader.ReadLine
    Dim Cols = (From s In CSVheader.Split(",") Select s).ToList()
    Dim setTblColumns = (From s In Cols Select dTable.Columns.Add(s, GetType(String))).ToList
    'TEST Dim getTblColumns As List(Of String) = (From c As DataColumn In dTable.Columns Select c.ColumnName).ToList()
    '_______________________________________________________________________
    Dim ReadToEnd As String = reader.ReadToEnd()
    Dim Rows = (From s In ReadToEnd.Split(vbLf) Select s).ToList()
    ' up to now everything goes well
    Dim getTblRows = (From z In Rows Select z.Split(",")).ToList()
    ' getting DataTable rows, OK

    'xxx Dim x As DataRow
    'xxx For Each s As String In Rows
    'xxx    x = dTable.NewRow()
    'xxx    x.ItemArray = s.Split(","c)
    'xxx    dTable.Rows.Add(x)
    'xxx Next
    '_______________________________________________________________________
End Using

End Sub

What I have tried:

The procedure works 100% but I want to change the last part, so I would get an advantage of LINQ: nice looking code and better performance. Thanks for help in advance
ATeDe

Dim x As DataRow
For Each s As String In Rows
    x = dTable.NewRow()
    x.ItemArray = s.Split(","c)
    dTable.Rows.Add(x)
Next

Posted 14-Mar-18 1:55am

ATeDe

Updated 14-Mar-18 11:21am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2018-03-14T11:22:00

Quote:
I would get an advantage of LINQ: nice looking code and better performance.

I'm not sure that my solution meets your criteria, but...

I would change NoNonsenseCSV2DataTable(CSVfile As String) procedure into function. See:

VB.NET

Public Function NoNonsenseCSV2DataTable(CSVfile As String) As DataTable
	Dim lines() As String  = File.ReadAllLines(CSVfile)
	
        'create DataTable
	Dim dt As DataTable = New DataTable()
	'get column names from first line and create an array of DataColumn
	Dim cols As DataColumn() = lines.Take(1) _
		.SelectMany(Function(x) x.Split(New String(){","}, StringSplitOptions.RemoveEmptyEntries)) _
		.Select(Function(x) New DataColumn(x, Type.GetType("System.String"))) _
		.ToArray()
	'add columns into DataTable
	dt.Columns.AddRange(cols)	
	
	'get rows - start from 2 line
	dt = lines.Skip(1) _
		.Select(Function(x) dt.LoadDataRow(x.Split(New String(){","}, StringSplitOptions.RemoveEmptyEntries), False)) _
		.ToArray() _
		.CopyToDataTable()
	'return DataTable
	Return dt

End Function

Usage:

VB.NET

Sub Main
    Dim sFileName As String = "FullFileNameOfCsvFile.csv"
    Dim myData As DataTable = NoNonsenseCSV2DataTable(sFileName)
    'myData is ready to use!

End Sub

For further details, please see:
File.ReadAllLines Method (String) (System.IO)[^]
DataTable.LoadDataRow Method (Object[], Boolean) (System.Data)[^]
Function Statement (Visual Basic) | Microsoft Docs[^]
Sub Statement (Visual Basic) | Microsoft Docs[^]