Click here to Skip to main content
13,146,315 members (43,373 online)
Click here to Skip to main content
Add your own
alternative version

Stats

10.2K views
482 downloads
13 bookmarked
Posted 20 Mar 2017

Converting text files (CSV) to DataTable

, 21 Mar 2017
Rate this:
Please Sign up or sign in to vote.
An article on converting text files or csv-files to a System.Data.DataSet based on regular expressions

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

Introduction

(Originally posted on 18 Feb 2008)
This code allows you to generate and fill a DataTable 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 DataTable with no regular expression knowledge
  • specify columntypes for the resulting DataTable, 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 DataTable:

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:

You can also get this code on github : https://github.com/cvanbergen/TextFileDataSet

 

License

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

Share

About the Author

Christiaan van Bergen
Software Developer (Senior) NoNerds
Netherlands Netherlands
No Biography provided

You may also be interested in...

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.170915.1 | Last Updated 21 Mar 2017
Article Copyright 2017 by Christiaan van Bergen
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid