Import data from CSV file into data set

In this post, I am going to use the OLE DB Provider for Jet to connect to and query CSV file and then load the data into a dataset.

First of all, I will import the following namespaces:

Imports System.Data
Imports System.Data.OleDb

And then there is a function which will load data from a CSV file and return a dataset.

Private Function LoadCsvData(ByVal filename As String) As DataSet

'Create a new connection
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\;" & _
"Extended Properties='text;HDR=Yes;FMT=Delimited'")

'Query the csv file
'Example of file name – Test_CSV.csv
Dim cmd As New OleDbCommand("select * from " & filename, con)

Dim da As New OleDbDataAdapter(cmd)
Dim ds As New DataSet

'Open connection
conn.Open()

'Fill the data set using the data adapter
da.Fill(ds)

'Close the connection
conn.Close()

'Return the dataset
Return ds
End Function

Please change the location of the CSV file.