473,221 Members | 1,794 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,221 software developers and data experts.

trouble in reading CSV files and putting it in DataTable

18
Hi,


I have an issue with reading CSV files.

I am to reading CSV file and putting it in a Datatable in C#. I am using a regular expression to read the values. Below is the code. Now, it reads CSV file without any issues only if all the fields are not null. If any field is blank, it moves the values to the left and displays the value under invalid column.

Example is shown below:
A part of CSV file.
I am reading the first row as column name of the table and subsequent rows as values for those columns.
Input:
"BEGDOC","DOCID","CUSTODIAN","EvidenceFile"
"TCP-REV-00000015",29,"Linda Difilippo","NSF File"

Output:
BEGDOC DOCID CUSTODIAN EvidenceFile
TCP-REV-00000014 29 Linda Difilippo NSF File

But, If I have an input like this(BegDoc column is empty):
"BEGDOC","DOCID","CUSTODIAN","EvidenceFile"
, 29,"Linda Difilippo","NSF File"

Output I get as:
BEGDOC DOCID CUSTODIAN EvidenceFile
29 Linda Difilippo NSF File

29 comes under BEGDOC column, which was suppose to come under DOCID.

Please help me to fix this?


C# Code:

public DataTable ParseCSVFile(string path)
{

string inputString = "";

// check that the file exists before opening it
if (File.Exists(path))
{

StreamReader sr = new StreamReader(path);
inputString = sr.ReadToEnd();
sr.Close();

}

return ParseCSV(inputString);
}



public DataTable ParseCSV(string inputString)
{

DataTable dt = new DataTable();

// declare the Regular Expression that will match versus the input string
Regex re = new Regex("((?<field>[^\",\\r\\n]+)|\"(?<field>([^\"]|\"\")+)\")(,|(?<rowbreak>\\r\\n|\\n|$))");

ArrayList colArray = new ArrayList();
ArrayList rowArray = new ArrayList();

int colCount = 0;
int maxColCount = 0;
string rowbreak = "";
string field = "";

MatchCollection mc = re.Matches(inputString);

foreach (Match m in mc)
{

// retrieve the field and replace two double-quotes with a single double-quote
field = m.Result("${field}").Replace("\"\"", "\"");

rowbreak = m.Result("${rowbreak}");

if (field.Length > 0)
{
colArray.Add(field);
colCount++;
}

if (rowbreak.Length > 0)
{

// add the column array to the row Array List
rowArray.Add(colArray.ToArray());

// create a new Array List to hold the field values
colArray = new ArrayList();

if (colCount > maxColCount)
maxColCount = colCount;

colCount = 0;
}
}

if (rowbreak.Length == 0)
{
// this is executed when the last line doesn't
// end with a line break
rowArray.Add(colArray.ToArray());
if (colCount > maxColCount)
maxColCount = colCount;
}

// create the columns for the table
// for (int i = 0; i < maxColCount; i++)
// dt.Columns.Add(String.Format("col{0:000}", i));


//Ravi Coding started....
Array ra1 = rowArray.ToArray();
Array ca1 = (Array)(ra1.GetValue(0));
for (int j = 0; j < ca1.Length; j++)
{
string str = ca1.GetValue(j).ToString();
dt.Columns.Add(str);
}
//Ravi Coding ends here...
// convert the row Array List into an Array object for easier access
Array ra = rowArray.ToArray();
for (int i = 1; i < ra.Length; i++)
{
// create a new DataRow
DataRow dr = dt.NewRow();

// convert the column Array List into an Array object for easier access
Array ca = (Array)(ra.GetValue(i));

// add each field into the new DataRow
for (int j = 0; j < ca.Length; j++)
dr[j] = ca.GetValue(j);

// add the new DataRow to the DataTable
dt.Rows.Add(dr);
}

// in case no data was parsed, create a single column
if (dt.Columns.Count == 0)
dt.Columns.Add("NoData");

return dt;
}
May 10 '07 #1
0 2156

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: somaboy mx | last post by:
hi, I'm on winXPpro / apache 1.2 / php 4.4.x I'm experimenting with writing and reading from textfiles via php. I can create a file with fopen, write to it, but I'm having trouble reading...
3
by: Olivier Maurice | last post by:
Hi all, I suppose some of you know the program Redmon (type redmon in google, first result). This neat little tool allows to hook up any functionality to a printer by putting the file printed...
1
by: MattB | last post by:
OK, never mind my last post. It was easy enough to refer to the table the repeater is bound to, but I made a big, incorrect assumption in that post. In my last post I thought I was successfully...
16
by: scorpion53061 | last post by:
Well as some of you know I was using a tab control for a project I was building for my boss. Today he tells me that he wants: When he switches tabs to be able to switch back and see whatever...
4
by: Amit Maheshwari | last post by:
I need to read text file having data either comma seperated or tab seperated or any custom seperator and convert into a DataSet in C# . I tried Microsoft Text Driver and Microsoft.Jet.OLEDB.4.0...
8
by: matt | last post by:
hello, can anyone speak to some of the common or preferred methods for building Excel .XLS files, programmatically thru the .NET framework? i have an intranet app that needs to generate &...
12
by: SAL | last post by:
Hello, Is it possible to read a CSV from the Client, and bind my Datagrid to the data in the CSV file without uploading the file to the Server first? I have tried and in Debug mode on my...
1
by: John Wright | last post by:
I am running a console application that connects to an Access database (8 million rows) and converts it to a text file and then cleans and compacts the database. When it runs I get the following...
2
by: Derik | last post by:
I've got a XML file I read using a file_get_contents and turn into a simpleXML node every time index.php loads. I suspect this is causing a noticeable lag in my page-execution time. (Or the...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.