import csv file to a dataTable


What is the easiest way to import a CSV file into a .NET DataTable object?

I am using Visual Studio 2005.

1) Does OLEDB have a driver that could parse a CSV into a datatable?

2) Is some other class inside the System.Data namespace able to parse a CSV file?

3) What about VSTO, does that have a tool that could allow me to tap into the functionality of Excel and read a CSV file? (I'm not very familiar with VSTO)

thanks!

brokeMyLegBiking
LVL 4
brokeMyLegBikingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

iboutchkineCommented:
Imports System.Data
Imports System.Data.OleDb


Public Class Form1
    Inherits System.Windows.Forms.Form
    Dim objDataset1 As DataSet()

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
       
        Dim sConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=Text;"
        Dim objConn As New OleDbConnection(sConnectionString)
        objConn.Open()

        Dim objCmdSelect As New OleDbCommand("SELECT * FROM test.csv",
objConn)
        Dim objAdapter1 As New OleDbDataAdapter()
        objAdapter1.SelectCommand = objCmdSelect

        Dim objDataset1 As New DataSet()
        objAdapter1.Fill(objDataset1, "Test")
        DataGrid1.DataSource = objDataset1.Tables(0).DefaultView
        objConn.Close()
    End Sub
End Class

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brokeMyLegBikingAuthor Commented:
ok, that worked great! bloody brilliant.

Do you know how it decides the datatype? Does it ever use a dateTime datatype? (I have a date field, but it cast it as a string in the database)

I see that sometimes it uses Int32 datatype.

I guess I could do a conversion on my dataTable after the data is imported.

thanks again,





iboutchkineCommented:
By default CSV is a text file and all the data are strings. You have to do the conversion when you read data from the dataset
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

eliuhaCommented:
is there a way to point the sConnectionString  to a remote file , instead of a local one ?
i mean , if the file is not on the local machine but somewhere in the internet .
Wardy_01Commented:
eliuha :

declare a new System.Web.WebClient object and call download string on the url then dump that to a local location before running this code.
psyche6Commented:
Using Jet OLEDB has been a great solution for accessing CSV files in 32 bit applications. However, Microsoft has not and will not release a 64 bit version of Jet OLEDB, so .Net apps running in 64 bit that use this technique will fail. One solution is to compile all .Net applications to force 32 bit, but that's the tail wagging the dog. A better solution that's platform independent is to just read and parse the CSV file and manually populate the DataTable as:

string CSVFilePathName = @"C:\test.cvs";
string[] Lines = File.ReadAllLines(CSVFilePathName);
string[] Fields;
Fields = Lines[0].Split(new char[] { ',' });
int Cols = Fields.GetLength(0);
DataTable dt = new DataTable();
//1st row must be column names; force lower case to ensure matching later on.
for (int i = 0; i < Cols; i++)
    dt.Columns.Add(Fields[i].ToLower(), typeof(string));
DataRow Row;
for (int i = 1; i < Lines.GetLength(0); i++)
{
    Fields = Lines[i].Split(new char[] { ',' });
    Row = dt.NewRow();
    for (int f = 0; f < Cols; f++)
        Row[f] = Fields[f];
    dt.Rows.Add(Row);
}

Access rows using DataTable functions as DataTable.Select()
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.