Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Reading a text file into a DataTable

Posted on 2010-08-31
3
Medium Priority
?
926 Views
Last Modified: 2012-05-10
What is the easiest way to LOAD a text file into a DataTable?  I have a windows form application that read in a comma delimited text file.  I want to load this text file into a datatable so what is the easiest way to do this.

Here is how I am reading the text file now.
 try
     {
          using (StreamReader sr = new StreamReader(strFileName.ToString()))
           {
              while ((line = sr.ReadLine()) != null)
                    {
                            Values = line.Split(Convert.ToChar(FileMapWindow.SelectedDelimiter));

I want to read this file into a DataTable dt = new DataTable() instead.
0
Comment
Question by:Hojoformo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 8

Accepted Solution

by:
TheMozz earned 2000 total points
ID: 33573389
Basically the process is:

1. Get the number of columns you need for the table - you can get that from the text file on the fly if needed.
2. Create the DataTable
3. Add the columns to the table
4. Iterate the rows of the text file and add new rows to the table

So.....

If you don't know the exact number of columns, or care what they are called you could do something like:

....
Values = line.Split(Convert.ToChar(FileMapWindow.SelectedDelimiter));

DataTable dt = new DataTable();
int columnCount = 1;
for(int columnCount = 0; columnCount < Values.Length; columnCount++)
{
     dt.Columns.Add(new DataColumn("Column" + columnCount.ToString(), typeof(System.String)));
}


That should build up your data table - or you can build it up in code with strongly typed columns if you know the exact columns you'll need.

Then to populate the table, each time through your loop you can do:

int columnCount = 0;
foreach(string value in Values)
{
     // call the data table to get a new row
     DataRow newRow = dt.NewRow();

     // Assign the value for the current column
     newRow[columnCount] = value;

     // don't forget to add the row to the table else it won't be there
     dt.Rows.Add(newRow);

     columnCount++;
}

That is what is needed to load a text file into a DataTable. And looking back at what i typed, i noticed i put the table creation inside a while loop - you will want to change that a bit or you'll end up with a new DataTable for each row in your text file....

Let me know if that doesn't work out.
0
 
LVL 30

Expert Comment

by:anarki_jimbel
ID: 33573625
I believe there are LOT of references around how to import a file into a grid.

Just have a look:
http://www.netomatix.com/development/ImportCSVIntoGrid.aspx
http://forums.devx.com/showthread.php?t=148498

The second link is VB.Net code so you may want to convert it to C# -see converted.

I used the http://www.developerfusion.com/tools/convert/vb-to-csharp/ converter and didn't test the code.
private void Button3_Click(System.Object sender, System.EventArgs e)
{
	if (OpenFileDialog1.ShowDialog(this) == DialogResult.OK) {
		FileInfo fi = new FileInfo(OpenFileDialog1.FileName);
		string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Text;Data Source=" + fi.DirectoryName;
		OleDbConnection objConn = new OleDbConnection(sConnectionString);
		objConn.Open();
		OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM " + fi.Name, objConn);
		OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
		objAdapter1.SelectCommand = objCmdSelect;
		DataSet objDataset1 = new DataSet();
		objAdapter1.Fill(objDataset1, "test");
		DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;
		objConn.Close();
	}

}

Open in new window

0
 
LVL 6

Expert Comment

by:nipunu
ID: 33575061
//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",",");


// Convert DataSet to DataTable by getting DataTable at first zero-based index of DataTableCollection
DataTable myDataTable = data.Tables[0];
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

705 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