Solved

C#: Import data from csv into datatable

Posted on 2008-10-06
6
10,810 Views
Last Modified: 2013-12-17
Can some one help me import data from a comma separated value file into a datatable?
0
Comment
Question by:karakav
6 Comments
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 22649139
0
 
LVL 13

Expert Comment

by:SameerJagdale
ID: 22649165
0
 
LVL 16

Expert Comment

by:Gyanendra Singh
ID: 22649195
please find sample code
//Namespace reference

using System.IO;

using System.Data;

 

#region BuildDataSet

/// <summary>

/// method to read a text file into a DataSet

/// </summary>

/// <param name="file">file to read from</param>

/// <param name="tableName">name of the DataTable we want to add</param>

/// <param name="delimeter">delimiter to split on</param>

/// <returns>a populated DataSet</returns>

public DataSet BuildDataSet(string file,string tableName,string delimeter)

{

    //create our DataSet

    DataSet domains = new DataSet();

    //add our table

    domains.Tables.Add(tableName);

    try

    {

        //first make sure the file exists

        if (File.Exists(file))

        {

            //create a StreamReader and open our text file

            StreamReader reader = new StreamReader(file);

            //read the first line in and split it into columns

            string[] columns = reader.ReadLine().Split(delimeter.ToCharArray());

            //now add our columns (we will check to make sure the column doesnt exist before adding it)

            foreach (string col in columns)

            {

                //variable to determine if a column has been added

                bool added = false;

                string next = "";

                //our counter

                int i = 0;

                while (!(added))

                {

                    string columnName = col;

                    //now check to see if the column already exists in our DataTable

                    if (!(domains.Tables[tableName].Columns.Contains(columnName)))

                    {

                        //since its not in our DataSet we will add it

                        domains.Tables[tableName].Columns.Add(columnName, typeof(string));

                        added = true;

                    }

                    else

                    {

                        //we didnt add the column so increment out counter

                        i++;

                    }

                }

            }

            //now we need to read the rest of the text file

            string data = reader.ReadToEnd();

            //now we will split the file on the carriage return/line feed

            //and toss it into a string array

            string[] rows = data.Split("\r".ToCharArray());

            //now we will add the rows to our DataTable

            foreach (string r in rows)

            {

                string[] items = r.Split(delimeter.ToCharArray());

                //split the row at the delimiter

                domains.Tables[tableName].Rows.Add(items);

            }

        }

        else

        {

            throw new FileNotFoundException("The file " + file + " could not be found");

        }

        

    }

    catch (FileNotFoundException ex)

    {

        _message = ex.Message;

        return null;

    }

    catch (Exception ex)

    {

        _message = ex.Message;

        return null;

    }

    

    //now return the DataSet

    return domains;

}

#endregion

 

 

//Sample usage

 

//for a Windows application

DataSet data = BuildDataSet("C:\MyFile.txt","MyTable",",");

 

//For an ASP.Net application

DataSet data = BuildDataSet(Server.MapPath("MyFile.txt"),"MyTable",",");

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 4

Author Comment

by:karakav
ID: 22649412
If possible I would like a solution that takes advantage of OleDb.
0
 
LVL 13

Accepted Solution

by:
SameerJagdale earned 500 total points
ID: 22649490
0
 
LVL 4

Author Closing Comment

by:karakav
ID: 31503370
Thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SSL https .net web site force redirect 3 36
Connection String 16 43
Entity Framework 7 32
How useful is the free version of Selenium? 3 13
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now