Fill a DataSet from Delimited Text Files

Introduction

This code allows you to take data stored in a text file and populate a DataSet with it. It contains one static function that:

1. Opens the file
2. Makes a DataSet with a DataTable of the given name
3. Populates the DataTable with the correct columns (pulled from the first line of the text file)
4. Populates the DataTable with data and returns the DataSet

Background

Anyone who works in business knows that while the delimited text file is the lowest common denominator of data transfers, the process of handling that data can be a pain. This class is an attempt to make handling these files as easy as possible.

Using the Code

Using this code is simple. Include it in your project and call it like this:

DataSet ds = TextToDataSet.Convert(
  "c:\test.txt", "MyNewTable", "\t");

It is necessary to give the full path to the file, so if you use this class in an ASP.NET application, the code may look something like this:

DataSet ds = TextToDataSet.Convert(
  Server.MapPath("test.txt"), "MyNewTable", "\t");

The last parameter is the delimiter parameter. This is what separates each column from the next. In the case shown, we pass it the escape sequence for a horizontal tab, but you can pass any string such as a space (" ") or a semi-colon(;). You may find this list helpful:

Escape Sequences for Formatting

Escape Sequence	Purpose
\a	bell (alert)
\b	backspace
\f	form feed
\n	new line
\r	carriage return
\t	horizontal tab
\v	vertical tab
\'	single quotation mark
\"	double quotation mark
\\	backslash
\?	literal question mark
\ooo	ASCII character shown in octal notation
\xhh	ASCII character shown in hexadecimal notation
\xhhhh	-UNICODE character shown in hexadecimal notation when this escape sequence is used in a wide-character constant or a UNICODE string literal

There are many more, but these are the most common.

I guess now all that is left is to give you the code, so here it is:

        
using System;
using System.Data;
using System.IO;

namespace TestTextToDataSet
{
  public class TextToDataSet
  {  
    public TextToDataSet()
    {  }
  
    /// <summary>
    /// Converts a given delimited file into a dataset. 
    /// Assumes that the first line    
    /// of the text file contains the column names.
    /// </summary>
    /// <param name="File">The name of the file to open</param>    
    /// <param name="TableName">The name of the 
    /// Table to be made within the DataSet returned</param>
    /// <param name="delimiter">The string to delimit by</param>
    /// <returns></returns>  
    public static DataSet Convert(string File, 
    string TableName, string delimiter)
    {   
    //The DataSet to Return
    DataSet result = new DataSet();
    
    //Open the file in a stream reader.
    StreamReader s = new StreamReader(File);
        
    //Split the first line into the columns       
    string[] columns = s.ReadLine().Split(delimiter.ToCharArray());
  
    //Add the new DataTable to the RecordSet
    result.Tables.Add(TableName);
    
    //Cycle the colums, adding those that don't exist yet 
    //and sequencing the one that do.
    foreach(string col in columns)
      {
        bool added = false;
        string next = "";
      int i = 0;
      while(!added)        
      {
        //Build the column name and remove any unwanted characters.
        string columnname = col + next;
         columnname = columnname.Replace("#","");
          columnname = columnname.Replace("'","");
          columnname = columnname.Replace("&","");
        
        //See if the column already exists
        if(!result.Tables[TableName].Columns.Contains(columnname))
          {
          //if it doesn't then we add it here and mark it as added
            result.Tables[TableName].Columns.Add(columnname);
            added = true;
          }
          else
        {
          //if it did exist then we increment the sequencer and try again.
            i++;  
          next = "_" + i.ToString();
          }         
      }
    }
    
    //Read the rest of the data in the file.        
    string AllData = s.ReadToEnd();
    
    //Split off each row at the Carriage Return/Line Feed
    //Default line ending in most windows exports.  
    //You may have to edit this to match your particular file.
    //This will work for Excel, Access, etc. default exports.
    string[] rows = AllData.Split("\r\n".ToCharArray());
 
      //Now add each row to the DataSet        
    foreach(string r in rows)
      {
      //Split the row at the delimiter.
        string[] items = r.Split(delimiter.ToCharArray());
      
      //Add the item
       result.Tables[TableName].Rows.Add(items);  
      }
    
    //Return the imported data.        
    return result;
    }
  }
 }

Points of Interest

You can overload this function many different ways to fit your project's needs. This is just one way that I do it. If there is a desire for more options, I will post some of them. Enjoy the code!

History

• 19^th April, 2004: Initial version

License

This article has no explicit license attached to it, but may contain usage terms in the article text or the download files themselves. If in doubt, please contact the author via the discussion board below. A list of licenses authors might use can be found here.