Categories
Quicksearch
Sponsored Links
Currently...
Reading:
Haruki Murakami: Blind Willow, Sleeping Woman
Listening to:
Moby
(Hotel)
Playing:
Supreme Commander
Watching:
New Tricks
Syndicate This Blog
Powered by
Monday, September 12. 2005
CSV file parser and writer in C# (Part 2)
This is the second part of an article series exploring reading and writing CSV files with C#/.NET.
Part 1 covered converting the contents of a DataTable into CSV format; this part explains reading a CSV file back into a DataTable.
Part 1 covered converting the contents of a DataTable into CSV format; this part explains reading a CSV file back into a DataTable.
First some namespace imports and the namespace declaration for this project:
Like the CsvWriter class, all methods in the CsvParser class are static. There are four parser methods that return a DataTable from either a string or text stream, and expecting a header line in the CSV source or not. Only one method has a real body, all others just adjust their parameter signature. The implementation is simple: the method reads one "row" of the CSV source at a time and populates a row in the DataTable. The real meat is the private class CsvStream explained below. There is one utility method that returns an unused column name for a DataTable, in case there are no headers in the CSV source or the headers are not unique.
The CsvStream class does the actual work - read the CSV source in one character at a time and return meaningful chunks of decoded data, namely data items and rows.
And that's about it. In an upcoming part of this article I'll share some NUnit test cases for these classes.
using System;
using System.Collections;
using System.Data;
using System.Text;
using System.IO;
namespace CsvParser
{
using System.Collections;
using System.Data;
using System.Text;
using System.IO;
namespace CsvParser
{
Like the CsvWriter class, all methods in the CsvParser class are static. There are four parser methods that return a DataTable from either a string or text stream, and expecting a header line in the CSV source or not. Only one method has a real body, all others just adjust their parameter signature. The implementation is simple: the method reads one "row" of the CSV source at a time and populates a row in the DataTable. The real meat is the private class CsvStream explained below. There is one utility method that returns an unused column name for a DataTable, in case there are no headers in the CSV source or the headers are not unique.
public class CsvParser
{
public static DataTable Parse(string data, bool headers)
{
return Parse(new StringReader(data), headers);
}
public static DataTable Parse(string data)
{
return Parse(new StringReader(data));
}
public static DataTable Parse(TextReader stream)
{
return Parse(stream, false);
}
public static DataTable Parse(TextReader stream, bool headers)
{
DataTable table = new DataTable();
CsvStream csv = new CsvStream(stream);
string[] row = csv.GetNextRow();
if (row == null)
return null;
if (headers)
{
foreach (string header in row)
{
if (header != null && header.Length > 0 && !table.Columns.Contains(header))
table.Columns.Add(header, typeof(string));
else
table.Columns.Add(GetNextColumnHeader(table), typeof(string));
}
row = csv.GetNextRow();
}
while (row != null)
{
while (row.Length > table.Columns.Count)
table.Columns.Add(GetNextColumnHeader(table), typeof(string));
table.Rows.Add(row);
row = csv.GetNextRow();
}
return table;
}
private static string GetNextColumnHeader(DataTable table)
{
int c = 1;
while (true)
{
string h = "Column" + c++;
if (!table.Columns.Contains(h))
return h;
}
}
{
public static DataTable Parse(string data, bool headers)
{
return Parse(new StringReader(data), headers);
}
public static DataTable Parse(string data)
{
return Parse(new StringReader(data));
}
public static DataTable Parse(TextReader stream)
{
return Parse(stream, false);
}
public static DataTable Parse(TextReader stream, bool headers)
{
DataTable table = new DataTable();
CsvStream csv = new CsvStream(stream);
string[] row = csv.GetNextRow();
if (row == null)
return null;
if (headers)
{
foreach (string header in row)
{
if (header != null && header.Length > 0 && !table.Columns.Contains(header))
table.Columns.Add(header, typeof(string));
else
table.Columns.Add(GetNextColumnHeader(table), typeof(string));
}
row = csv.GetNextRow();
}
while (row != null)
{
while (row.Length > table.Columns.Count)
table.Columns.Add(GetNextColumnHeader(table), typeof(string));
table.Rows.Add(row);
row = csv.GetNextRow();
}
return table;
}
private static string GetNextColumnHeader(DataTable table)
{
int c = 1;
while (true)
{
string h = "Column" + c++;
if (!table.Columns.Contains(h))
return h;
}
}
The CsvStream class does the actual work - read the CSV source in one character at a time and return meaningful chunks of decoded data, namely data items and rows.
private class CsvStream
{
private TextReader stream;
public CsvStream(TextReader s)
{
stream = s;
}
public string[] GetNextRow()
{
ArrayList row = new ArrayList();
while (true)
{
string item = GetNextItem();
if (item == null)
return row.Count == 0 ? null : (string[])row.ToArray(typeof(string));
row.Add(item);
}
}
private bool EOS = false;
private bool EOL = false;
private string GetNextItem()
{
if (EOL)
{
// previous item was last in line, start new line
EOL = false;
return null;
}
bool quoted = false;
bool predata = true;
bool postdata = false;
StringBuilder item = new StringBuilder();
while (true)
{
char c = GetNextChar(true);
if (EOS)
return item.Length > 0 ? item.ToString() : null;
if ((postdata || !quoted) && c == ',')
// end of item, return
return item.ToString();
if ((predata || postdata || !quoted) && (c == '\x0A' || c == '\x0D'))
{
// we are at the end of the line, eat newline characters and exit
EOL = true;
if (c == '\x0D' && GetNextChar(false) == '\x0A')
// new line sequence is 0D0A
GetNextChar(true);
return item.ToString();
}
if (predata && c == ' ')
// whitespace preceeding data, discard
continue;
if (predata && c == '"')
{
// quoted data is starting
quoted = true;
predata = false;
continue;
}
if (predata)
{
// data is starting without quotes
predata = false;
item.Append(c);
continue;
}
if (c == '"' && quoted)
{
if (GetNextChar(false) == '"')
// double quotes within quoted string means add a quote
item.Append(GetNextChar(true));
else
// end-quote reached
postdata = true;
continue;
}
// all cases covered, character must be data
item.Append(c);
}
}
private char[] buffer = new char[4096];
private int pos = 0;
private int length = 0;
private char GetNextChar(bool eat)
{
if (pos >= length)
{
length = stream.ReadBlock(buffer, 0, buffer.Length);
if (length == 0)
{
EOS = true;
return '\0';
}
pos = 0;
}
if (eat)
return buffer[pos++];
else
return buffer[pos];
}
}
}
}
{
private TextReader stream;
public CsvStream(TextReader s)
{
stream = s;
}
public string[] GetNextRow()
{
ArrayList row = new ArrayList();
while (true)
{
string item = GetNextItem();
if (item == null)
return row.Count == 0 ? null : (string[])row.ToArray(typeof(string));
row.Add(item);
}
}
private bool EOS = false;
private bool EOL = false;
private string GetNextItem()
{
if (EOL)
{
// previous item was last in line, start new line
EOL = false;
return null;
}
bool quoted = false;
bool predata = true;
bool postdata = false;
StringBuilder item = new StringBuilder();
while (true)
{
char c = GetNextChar(true);
if (EOS)
return item.Length > 0 ? item.ToString() : null;
if ((postdata || !quoted) && c == ',')
// end of item, return
return item.ToString();
if ((predata || postdata || !quoted) && (c == '\x0A' || c == '\x0D'))
{
// we are at the end of the line, eat newline characters and exit
EOL = true;
if (c == '\x0D' && GetNextChar(false) == '\x0A')
// new line sequence is 0D0A
GetNextChar(true);
return item.ToString();
}
if (predata && c == ' ')
// whitespace preceeding data, discard
continue;
if (predata && c == '"')
{
// quoted data is starting
quoted = true;
predata = false;
continue;
}
if (predata)
{
// data is starting without quotes
predata = false;
item.Append(c);
continue;
}
if (c == '"' && quoted)
{
if (GetNextChar(false) == '"')
// double quotes within quoted string means add a quote
item.Append(GetNextChar(true));
else
// end-quote reached
postdata = true;
continue;
}
// all cases covered, character must be data
item.Append(c);
}
}
private char[] buffer = new char[4096];
private int pos = 0;
private int length = 0;
private char GetNextChar(bool eat)
{
if (pos >= length)
{
length = stream.ReadBlock(buffer, 0, buffer.Length);
if (length == 0)
{
EOS = true;
return '\0';
}
pos = 0;
}
if (eat)
return buffer[pos++];
else
return buffer[pos];
}
}
}
}
And that's about it. In an upcoming part of this article I'll share some NUnit test cases for these classes.
Trackbacks
Trackback specific URI for this entry
No Trackbacks
Comments
Display comments as
(Linear | Threaded)
Hi there, thank you so much for your interesting code (*CSV file parser and writer in C#*). But is there a way to read a .CSV file and store all the data into 1d array rather than using DataTable.
Hello!
You can use the DataTable as a two-dimensional array if you want. For example, to get the element in row 5, column 10 you write:
table.Rows[5][10]
If you do need an actual array, you could loop over the DataTable and fill an array this way as well.
You can use the DataTable as a two-dimensional array if you want. For example, to get the element in row 5, column 10 you write:
table.Rows[5][10]
If you do need an actual array, you could loop over the DataTable and fill an array this way as well.
This code works great. Is it ok to use it in a commercial application?
Yes, you can use this code in a commercial application, no guarantees, warranties etc. though 
Actually did you thought of assigning a license to it? To trial attorneys, saying others can use in commercial application doesn't really mean that one wouldn't try to collect royalty or impose conditions. Those may not even be bad but could surprise users. Some of the simplest and permissive licenses with strong communities are
*New BSD license
*MIT license
And definitely you can pick other more elaborated or public domain (no need for license). A good resource is http://www.opensource.org/licenses/category,
Those may not even be bad but could surprise users. Some of the simplest and permissive licenses with strong communities are *New BSD license
*MIT license
And definitely you can pick other more elaborated or public domain (no need for license). A good resource is http://www.opensource.org/licenses/category,
Good suggestion. I'll repackage it and add a license once I've had a chance to look at a few.
Nice code. Saved me a fair chunk of time writing my own from scratch:)
Cheers.
Cheers.
Thats great code. Exactly what I was looking for. Thanks for this!
I tried the code it and it works fine!
I tried the code it and it works fine!
Can this parser be used to parse a tab delimited CSV file? I tried it and it didnt work. Any tweak required?
Pls. dont bother. I figured it out. Thanks for the excellent code. It helped a lot. I added the following condition
if ((postdata || !quoted) && (c == ',' || c == '\t'))
Let me know if anything else needs to be done. It worked well though so far. Haven't tested it enough.
if ((postdata || !quoted) && (c == ',' || c == '\t'))
Let me know if anything else needs to be done. It worked well though so far. Haven't tested it enough.
Very nice, and as far as I can tell from the first look flawless code. I have seen quite a few csv readers based on regular expressions, but they all seemed to miss at least one aspect of the csv specification.
Hi. Really nice program. Im having problem with flawed csv with newline in an cell that isnt inclosed in "". Ive tried adding "" manually but cant get it to work.
I was having problems with the Jet ODBC engine only loading 255 columns from a csv file, this saved me a bunch of time over writing my own parser. Thanks for the link to csv spec too, I always wondered how it handled line breaks mid-record...
Nice job. Found a bug: If the last "cell" in the file is an empty string it's ignored. (IE, "1,2,3,4," would return 4 elements instead of 5, with the 5th element being just an empty string)
I modified CsvStream.GetNextRow() to resolve this. Modified the "if (EOL)" block to this:
if (EOL || EOS)
{
EOL = false;
EOS = false;
return null;
}
and in the loop, modified the "if (EOS)" block to this:
if (EOS)
return item.ToString();
(got rid of "return item.Length > 0 ? item.ToString() : null;")
I modified CsvStream.GetNextRow() to resolve this. Modified the "if (EOL)" block to this:
if (EOL || EOS)
{
EOL = false;
EOS = false;
return null;
}
and in the loop, modified the "if (EOS)" block to this:
if (EOS)
return item.ToString();
(got rid of "return item.Length > 0 ? item.ToString() : null;")
COde Not working...
if csv file has any unsigned values like / or blank space the loop break at that point..
so not validated
if csv file has any unsigned values like / or blank space the loop break at that point..
so not validated
Hi.
This is a great article, but could you show some example code how to call the example code from main? I'm thinking of having some line to open file "a.csv" in the current directory.
Is this code meant to be run from Command Prompt, or some other way, such as through Excel?
Or is it part of a VSTO project or something?
Thanks.
This is a great article, but could you show some example code how to call the example code from main? I'm thinking of having some line to open file "a.csv" in the current directory.
Is this code meant to be run from Command Prompt, or some other way, such as through Excel?
Or is it part of a VSTO project or something?
Thanks.
This looks like excellent work, and is just what I needed. Added to StumbleUpon
I am able to create/write/save csv file .
excellent code.
But I need further assistance as to saving that csv file in a particular type. Currently my file is being saved as "CSV(Comma delimited) (*.csv).
But i would like to save it as CSV(MS-DOS)(*.csv).
Please let me know.
excellent code.
But I need further assistance as to saving that csv file in a particular type. Currently my file is being saved as "CSV(Comma delimited) (*.csv).
But i would like to save it as CSV(MS-DOS)(*.csv).
Please let me know.
Sponsors
My del.icio.us
- Swing Design
- Jokes on M B A
- Google advisor
- Quest - Make text adventure games without programming
- Financial Modelling in Python > Welcome to Python - Pg. 1: Safari Books Online
- Python Testing > Testing for Fun and Profit - Pg. 7: Safari Books Online
- Simon Tatham's Portable Puzzle Collection
- Clean Coder
- Git setup niceties
- IF Game Testing
