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:
- 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.
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!