Explains how to fill a dataset with the information stored in a delimited text file
This code allows you to take data stored in a text file and populate a DataSet with it. It contains one static function that:
- Opens the file
- Makes a DataSet with a DataTable of the given name
- Populates the DataTable with the correct columns (pulled from the first line of the text file)
- Populates the DataTable with data and returns the DataSet.
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
single quotation mark
double quotation mark
literal question mark
ASCII character shown in octal notation
ASCII character shown in hexadecimal notation
-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:
public class TextToDataSet
/// Converts a given delimited file into a dataset.
/// Assumes that the first line
/// of the text file contains the column names.
/// <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>
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
//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;
//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 it doesn't then we add it here and mark it as added
added = true;
//if it did exist then we increment the sequencer and try again.
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
//Return the imported data.
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!