Convert csv data to DataTable in VB.net

Question

I am trying to import a large array of integers stored as a csv file into a VB.Net DataTable called BeamMap . The .csv file consists only of integers with a separator 1,3,-2,44,1 without quotation marks around the data (i.e., 1,3,-2,44,1 ) and the end-of-line character of the line feed and carriage return. All I want to do is get every integer in the DataTable cell with the corresponding rows and columns (the number of columns for each row is the same) and be able to refer to it later in my code. I really do not want anything more than absolutely necessary in the code (without headers, titles, titles, etc.), and I need it to be efficient enough (csv array is about 1000 ~ 1000).

Thanks!

Answer 1

Here is a simple approach that requires a strict format (as you already mentioned):

 Dim lines = IO.File.ReadAllLines(path) Dim tbl = New DataTable Dim colCount = lines.First.Split(","c).Length For i As Int32 = 1 To colCount tbl.Columns.Add(New DataColumn("Column_" & i, GetType(Int32))) Next For Each line In lines Dim objFields = From field In line.Split(","c) Select CType(Int32.Parse(field), Object) Dim newRow = tbl.Rows.Add() newRow.ItemArray = objFields.ToArray() Next 

Answer 2

Use the OleDb provider to read the CSV and pouplate DataTable .

  Dim folder = "c:\location\of\csv\files\" Dim CnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & folder & ";Extended Properties=""text;HDR=No;FMT=Delimited"";" Dim dt As New DataTable Using Adp As New OleDbDataAdapter("select * from [nos.csv]", CnStr) Adp.Fill(dt) End Using 

Answer 3

Retrieving a file from a mapped drive and transferring the extracted data to a data set:

 Dim folder = "Z:\" Dim CnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & folder & ";Extended Properties=""text;HDR=No;FMT=Delimited"";" Dim dssample As New DataSet Using Adp As New OleDbDataAdapter("select * from [samplecsv.csv]", CnStr) Adp.Fill(dssample) End Using If dssample.Tables.Count > 0 Then 'some code here End If 

Answer 4

Also, be sure to enable

 Imports System.Data.OleDb 

And if you want to bind to a DataGridView (after reading):

 Dim bs As New BindingSource bs.DataSource = dt DataGridView1.DataSource = bs