如何在VB.NET中使用LINQ将CSV转换为datatable [英] How do I convert CSV to datatable using LINQ in VB.NET

问题描述

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

我尝试了什么：



程序100％有效，但我想改变最后一部分，所以我会得到LINQ的优势：漂亮的代码和更好的性能。感谢您提前获得帮助

ATeDe

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

推荐答案

引用：

我会得到LINQ的优势：漂亮的代码和更好的代码性能。

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

我不确定我的解决方案是否符合您的标准，但是......



我会改变 NoNonsenseCSV2DataTable（CSVfile As String）进入函数的过程。请参阅：

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

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

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:

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

End Sub

欲了解更多详情，请参阅：

File.ReadAllLines Method（String）（System.IO） [ ^ ]

DataTable.LoadDataRow方法（对象） []，布尔）（System.Data） [ ^ ]

函数语句（Visual Basic）| Microsoft Docs [ ^ ]

子语句（Visual Basic）| Microsoft Docs [ ^ ]

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[^]

这篇关于如何在VB.NET中使用LINQ将CSV转换为datatable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！