5,699,997 members and growing! (12,698 online)
Email Password   helpLost your password?
Languages » C# » General     Intermediate License: The Code Project Open License (CPOL)

Converting text files (CSV) to datasets

By Christiaan van Bergen

An article on converting text files or csv-files to a System.Data.DataSet based on regular expressions
C# (C# 1.0, C# 2.0, C# 3.0, C#), .NET (.NET, .NET 3.5, .NET 3.0, .NET 2.0)

Posted: 23 Dec 2007
Updated: 18 Feb 2008
Views: 778
Bookmarked: 2 times
Announcements
Loading...



Search    
Advanced Search
Sitemap
3 votes for this Article.
Popularity: 2.39 Rating: 5.00 out of 5
0 votes, 0.0%
1
0 votes, 0.0%
2
0 votes, 0.0%
3
0 votes, 0.0%
4
3 votes, 100.0%
5
Note: This is an unedited contribution. If this article is inappropriate, needs attention or copies someone else's work without reference then please Report This Article

Download textfiledataset_src.zip - 6 KB
Download textfiledataset_demo.zip - 15 KB

Introduction

This code allows you to generate and fill a DataSet from a text file. You are able to:

  • define your own column names or have them created automatically
  • validate data with regular expressions
  • use RegexColumnBuilder to construct the DataSet with no regular expression knowledge
  • specify columntypes for the resulting DataSet, not just the type string
  • process any kind of text file, not just delimited files
  • have a collection of misread lines of data
  • process large files

Background

Every now and then I need to read some text file for importing data. Most of the times what I really need is a dataset with that data. But how to convert that text file to a dataset quickly? There are several articles here on codeproject that describe how to convert a csv file or a text file to a database or dataset (e.g. FinalCSVReader, DataSetFrmDelimTxt). But none of them were flexible enough, or offered data-validation.

Using the code

Getting started

To get started right away let us assume that we have a delimited file birthday.txt with the following content from which we want to create a DataSet:

1,Chris,12-07-1972
2,Dave,03-01-1974
3,John,03-19-1980,Drummer
4,Mark,12-02-1980
5,Eric,09-18-1981

A quick way to get the DataSet would be:

// open the file 
FileStream fileStream = new FileStream("birthday.txt", FileMode.Open, FileAccess.Read);
// create an instance of MyTextFileDataSet
TextFileDataSet MyTextFileDataSet = new TextFileDataSet();
// specify the regular expression for validating and recognising columns
MyTextFileDataSet.ContentExpression = new Regex("^(?<ID>[^,]+),(?<Name>[^,]+),(?<Date>[^,]+)$");
// fill the dataset
MyTextFileDataSet.Fill(fileStream);
// close the file
fileStream .Close();

The resulting MyTextFileDataSet will contain one DataTable with three DataColumns: ID, Name and Date. Ofcourse it will be filled with four rows of data. Four? Yes, four rows. Row number 3 is not valid according to the regular expression. You can find this row in the property MyTextFileDataSet.MisReads. This property is of type List<string>.

Wondering where the column names came from, just take a look at the used regular expression:

^(?<ID>[^,]+),(?<Name>[^,]+),(?<Date>[^,]+)$

You can see that the names for the DataColumns are provided. If you did not specify the names but made your expression something like ^([^,]+),([^,]+),([^,]+)$ The names of the columns would be 1,2 and 3.

Without regular expressions

If you are not familiar with regular expressions another approach is implemented to define the columns.
This is achieved by using the RegexColumnBuilder. Take a look at this sample:

RegexColumnBuilder MyColumBuilder = new RegexColumnBuilder();
MyColumBuilder.AddColumn("ID", ',');
MyColumBuilder.AddColumn("DATE", ',');
MyColumBuilder.AddColumn("NAME", ',');
Regex MyRegex = MyColumBuilder.CreateRegularExpression();

The MyRegex can be used to put in the MyTextFileDataSet.RegularExpression, or just place the complete RegexColumnBuilder in the TextFileDataSet.

MyTextFileDataSet.ColumnBuilder = MyColumnBuilder;

Specify column type

In the previous sample a column is defined with only a suitable delimiter. At this time there are four defined types available: INT, DOUBLE, DATE and STRING. The way to use these is:

MyColumBuilder.AddColumn("ID", ',', RegexColumnType.INTEGER);
MyColumBuilder.AddColumn("DATE", ',', RegexColumnType.DATE);

To have the specified types present in the resulting dataset one must place the MyColumnBuilder in the MyTextFileDataSet.ColumnBuilder property, otherwise the specifications are always of type string.

Large files

TextFileDataSet can handle large files because internally it is using System.IO.Stream as way of input. This also has the extra benefit of not having to have the file on disk per se.

Points of Interest

The TextFileDataSet inherits the System.Data.DataSet, so anything you can do with a System.Data.DataSet you can do with TextFileDataSet.

If you are not familiar with regular expressions, check out these articles and get started:

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Christiaan van Bergen



Occupation: Software Developer (Senior)
Location: Netherlands Netherlands

Article Top
Sign Up to vote for this article
You must Sign In to use this message board.
FAQ FAQ Search Search Messages 
 Layout  Per page   
  (Refresh) 
-- There are no messages in this forum --

General General    News News    Question Question    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

PermaLink | Privacy | Terms of Use
Last Updated: 18 Feb 2008
Editor:
Copyright 2007 by Christiaan van Bergen
Everything else Copyright © CodeProject, 1999-2008
Web17 | Advertise on the Code Project