Click here to Skip to main content
Click here to Skip to main content
Go to top

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

 
QuestionWhat if there isn't a column heading row? PinmemberMember 803413724-Jun-11 17:39 
QuestionHow can I use a stream from Memory PinmemberMember 45552167-Mar-11 9:13 
QuestionHow can I populate mygridview??? Pinmembersalihovic13-Jun-10 22:55 
AnswerRe: How can I populate mygridview??? Pinmembersalihovic14-Jun-10 1:06 
QuestionA doubt PinmemberPraneeshpeeyar11-Feb-10 1:03 
GeneralOne more recommendation for anyone using... PinmemberSFWheeler22-Jan-10 4:33 
Questionhow to set a delimiter to any amount of white spaces? PinmemberUnforgiv3n20-Jan-10 23:53 
Generali added one line here to remove the empty rows. PinmemberGajendra Rathi26-May-09 3:02 
GeneralFileUpload - Dispose() Pinmembersquawk21-Nov-07 11:41 
QuestionHow do I bind this to a report? PinmemberWeste6-Aug-07 10:15 
Generalremoving unwanted characters Pinmemberminnie mouse10-Jun-06 17:15 
GeneralRe: removing unwanted characters PinmemberDave - O17-Oct-06 11:45 
GeneralRe: removing unwanted characters Pinmemberminnie mouse18-Oct-06 2:25 
Generalreturning this data to a blank database Pinmemberminnie mouse29-May-06 12:04 
GeneralRe: returning this data to a blank database PinmemberDave - O29-May-06 13:05 
GeneralRe: returning this data to a blank database Pinmemberminnie mouse1-Jun-06 10:30 
QuestionRe: returning this data to a blank database PinmemberPaul Chin PC13-Oct-07 15:15 
AnswerRe: returning this data to a blank database Pinmembercjb11015-Mar-10 4:26 
GeneralExample PinmemberJonncr1-Apr-06 8:53 
GeneralLarge file getting error 'out of memory exception Pinmembermrph1-Sep-05 4:25 
GeneralRe: Large file getting error 'out of memory exception Pinmemberhungpvtn21-Jan-06 23:10 
GeneralRe: Large file getting error 'out of memory exception [modified] PinmemberDave - O29-May-06 12:58 
Generalproblem in loading delimited text files Pinmembermamidibabu10-Feb-05 18:36 
GeneralRe: problem in loading delimited text files PinmemberDave - O10-Feb-05 18:39 
GeneralRe: problem in loading delimited text files Pinmembermamidibabu10-Feb-05 18:41 
GeneralRe: problem in loading delimited text files PinmemberDave - O16-Feb-05 11:04 
GeneralRe: problem in loading delimited text files Pinmembermamidibabu16-Feb-05 19:22 
GeneralIt's adding unwanted lines PinmemberPamela Pennington14-Oct-04 13:34 
GeneralRe: It's adding unwanted lines PinmemberDave - O14-Oct-04 13:41 
GeneralRe: It's adding unwanted lines PinmemberPamela Pennington15-Oct-04 6:40 
AnswerRe: It's adding unwanted lines Pinmemberamatbrewer22-Jan-07 8:08 
AnswerRe: It's adding unwanted lines Pinmemberblvlboy18-Feb-09 5:57 
GeneralEmbedded delimiter Pinmemberfrankcf23-Apr-04 1:05 
GeneralRe: Embedded delimiter Pinmemberthe author23-Apr-04 5:54 
GeneralRe: Embedded delimiter Pinmemberfrankcf23-Apr-04 6:57 
GeneralRe: Embedded delimiter Pinmemberthe author23-Apr-04 9:05 
GeneralRe: Embedded delimiter Pinmemberfrankcf26-Apr-04 0:58 
GeneralRe: Embedded delimiter Pinmemberthe author26-Apr-04 5:59 
GeneralRe: Embedded delimiter PinmemberMuruganantham Durairaj11-Feb-09 23:03 
GeneralCurious... PinmembercasperOne19-Apr-04 3:06 
GeneralRe: Curious... PinmemberThe author20-Apr-04 5:51 
GeneralRe: Curious... PinsussAnonymous18-Jun-04 11:19 
GeneralRe: Curious... PinmemberDave - O18-Jun-04 11:22 

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 | Mobile
Web01 | 2.8.140916.1 | Last Updated 19 Apr 2004
Article Copyright 2004 by CoderForRent
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid