Solved

C#: Import data from csv into datatable

Posted on 2008-10-06
6
10,813 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Open a link in vb.net 2 15
Using data from certificate to re-create certificate programmatically 3 25
VS 2017 18 30
ASP.NET MVC - Views 3 15
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

789 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