Click here to Skip to main content
11,415,164 members (77,671 online)
Click here to Skip to main content

Fill a DataSet from delimited text files

, 18 Apr 2004
Rate this:
Please Sign up or sign in to vote.
Explains how to fill a dataset with the information stored in a delimited text file

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 overrload 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!

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

Share

About the Author

CoderForRent
Web Developer
United States United States
Coder For Rent is a full service .Net consulting firm based in Pearland, TX. We specialize in end-to-end solutions using different .Net technologies.

www.coderforrent.com
Group type: Organisation (No members)



Comments and Discussions

 
QuestionEmpty rows in data table using this Pin
Harshana Jayasuriya8-Apr-15 21:08
memberHarshana Jayasuriya8-Apr-15 21:08 
QuestionWhat if there isn't a column heading row? Pin
Member 803413724-Jun-11 18:39
memberMember 803413724-Jun-11 18:39 
AnswerRe: What if there isn't a column heading row? [modified] Pin
Member 1040356713-Nov-14 1:00
memberMember 1040356713-Nov-14 1:00 
QuestionHow can I use a stream from Memory Pin
Member 45552167-Mar-11 10:13
memberMember 45552167-Mar-11 10:13 
QuestionHow can I populate mygridview??? Pin
salihovic13-Jun-10 23:55
membersalihovic13-Jun-10 23:55 
AnswerRe: How can I populate mygridview??? Pin
salihovic14-Jun-10 2:06
membersalihovic14-Jun-10 2:06 
QuestionA doubt Pin
Praneeshpeeyar11-Feb-10 2:03
memberPraneeshpeeyar11-Feb-10 2:03 
GeneralOne more recommendation for anyone using... Pin
SFWheeler22-Jan-10 5:33
memberSFWheeler22-Jan-10 5:33 
Questionhow to set a delimiter to any amount of white spaces? Pin
Unforgiv3n21-Jan-10 0:53
memberUnforgiv3n21-Jan-10 0:53 
Generali added one line here to remove the empty rows. Pin
Gajendra Rathi26-May-09 4:02
memberGajendra Rathi26-May-09 4:02 
GeneralFileUpload - Dispose() Pin
squawk21-Nov-07 12:41
membersquawk21-Nov-07 12:41 
QuestionHow do I bind this to a report? Pin
Weste6-Aug-07 11:15
memberWeste6-Aug-07 11:15 
Generalremoving unwanted characters Pin
minnie mouse10-Jun-06 18:15
memberminnie mouse10-Jun-06 18:15 
GeneralRe: removing unwanted characters Pin
Dave - O17-Oct-06 12:45
memberDave - O17-Oct-06 12:45 
GeneralRe: removing unwanted characters Pin
minnie mouse18-Oct-06 3:25
memberminnie mouse18-Oct-06 3:25 
Generalreturning this data to a blank database Pin
minnie mouse29-May-06 13:04
memberminnie mouse29-May-06 13:04 
GeneralRe: returning this data to a blank database Pin
Dave - O29-May-06 14:05
memberDave - O29-May-06 14:05 
GeneralRe: returning this data to a blank database Pin
minnie mouse1-Jun-06 11:30
memberminnie mouse1-Jun-06 11:30 
QuestionRe: returning this data to a blank database Pin
Paul Chin PC13-Oct-07 16:15
memberPaul Chin PC13-Oct-07 16:15 
AnswerRe: returning this data to a blank database Pin
cjb11015-Mar-10 5:26
membercjb11015-Mar-10 5:26 
GeneralExample Pin
Jonncr1-Apr-06 9:53
memberJonncr1-Apr-06 9:53 
GeneralLarge file getting error 'out of memory exception Pin
mrph1-Sep-05 5:25
membermrph1-Sep-05 5:25 
GeneralRe: Large file getting error 'out of memory exception Pin
hungpvtn22-Jan-06 0:10
memberhungpvtn22-Jan-06 0:10 
GeneralRe: Large file getting error 'out of memory exception [modified] Pin
Dave - O29-May-06 13:58
memberDave - O29-May-06 13:58 
Generalproblem in loading delimited text files Pin
mamidibabu10-Feb-05 19:36
membermamidibabu10-Feb-05 19:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150427.4 | Last Updated 19 Apr 2004
Article Copyright 2004 by CoderForRent
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid