AKA Marketing.com Logo            VISIT THE BLOG            

Blogged thoughts, is our web blog. Expect views, opinion, rants and tirades about everything and anything 

« Home / Forums »        

 


Subscribe to our SEO / IT related blog by entering your email address below

Blogged thoughts

| by the www.akamarketing.com team



C# CSV DataTable - Convert CSV data to a ASP.Net DataTable

Often as a developer, it’s best to keep things as simple as possible… and you do know by ‘often’ I mean always so if you have the opportunity to use something simple but which works well then why not?

Earlier today I started on a small web app (only about five days work) which needed a data store of some sort to store I’d say 100 records (absolute max) containing three basic things: name, email and ID. I’ve worked with SQL Server, Oracle, MySQL, XML, XSD, XSLT etc. extensively but I wasn’t going anywhere near them for this project as it was just plain overkill so I just went with old school CSV format for the data store needs of my application.

Getting access to CSV based data in C# ASP.Net is very easy. First I’d suggest using the static ReadAllLines() method of the File class to place all lines in the CSV file into a String based Array. Next you will want to call something like the Split() method to actually get your hands on the individual bits of data. Using something like this is fine but I prefer to use these methods as part of a wider effort to import the CSV data into a DataTable / DataSet as processing is made an awful lot easier.

CSV based DataTables with C#
If you too wanted to convert CSV data into a ASP.Net / C# DataTable here’s a method I wrote earlier which you can use. It’s by no means bullet proof (i.e - it requires a well formed CSV) and maybe there’s a more efficent way of doing it but it’s working well for me.

public static DataTable csvToDataTable(string file, bool isRowOneHeader)
{

DataTable csvDataTable = new DataTable();

//no try/catch - add these in yourselfs or let exception happen
String[] csvData = File.ReadAllLines(HttpContext.Current.Server.MapPath(file));

//if no data in file ‘manually’ throw an exception
if (csvData.Length == 0)
{
    throw new Exception(”CSV File Appears to be Empty”);
}

String[] headings = csvData[0].Split(’,');
int index = 0; //will be zero or one depending on isRowOneHeader

if(isRowOneHeader) //if first record lists headers
{
    index = 1; //so we won’t take headings as data
   
    //for each heading
    for(int i = 0; i < headings.Length; i++)
    {
        //replace spaces with underscores for column names
        headings[i] = headings[i].Replace(” “, “_”);

       //add a column for each heading
        csvDataTable.Columns.Add(headings[i], typeof(string));
   } 
}
else //if no headers just go for col1, col2 etc.
{
    for (int i = 0; i < headings.Length; i++)
    {
       //create arbitary column names
       csvDataTable.Columns.Add(”col”+(i+1).ToString(), typeof(string));
    }
}

//populate the DataTable
for (int i = index; i < csvData.Length; i++)
{
    //create new rows
    DataRow row = csvDataTable.NewRow();

    for (int j = 0; j < headings.Length; j++)
    {
         //fill them
         row[j] = csvData[i].Split(’,')[j];
    }

    //add rows to over DataTable
    csvDataTable.Rows.Add(row);
}

//return the CSV DataTable
return csvDataTable;

You can then a) call the csvToDataTable() method to fill a DataTable and b) bind that DataTable to something like a GridView using a code snippet similar to below:

DataTable csvDataTable = csvToDataTable(”data.csv”,true);
       
//what’s in the DataTable?
GridView gv = new GridView();
form1.Controls.Add(gv);
gv.DataSource = csvDataTable;
gv.DataBind(); 

The first parameter is obviously the location of the CSV file, while the second parameter lets you specify that the first line in the CSV file is (or is not) the name of the column/elements in the CSV which will then be used to create matching column names on the outputted DataTable. A CSV excerpt I used it on was:

name,email,account num,telephone
Dave,x@x.com,1234565,18001234567
James,y@y.com,932323332,232332332332
Tina,t@t.com,3838383838,262626263
Tom,tom@tom.com,729372937,12121212121

Convert CSV to XML with C# ASP.Net
Incidentally if for whatever reason you wanted to convert from CSV to XML with C# it’s as simple as calling the WriteXml() method of the DataTable you’ve created using csvToDataTable():

csvDataTable.WriteXml(”csv.xml”);

Please let me know if you have any comments and/or questions.

17 Comments on “C# CSV DataTable - Convert CSV data to a ASP.Net DataTable”
1| Kris Brixon said,

Thanks for the post.
I was looking for help with recreating a method called QuerySim that I used in ColdFusion ( http://cflib.org/index.cfm?event=page.udfbyid&udfid=255 ). I used this in prototypes to mock a database/DAL and I used it when someone else was handling the back end and I needed something to program the front end against.

2| John said,

This is a great article, but it doesn’t address header columns that have a comma as part of the value of the column names such as:

Name, Adress, “City,State”, Zip

I’d love to see an easy example for that.

3| David Callan said,

Just add

headings[i] = headings[i].Replace(”,“, “_”);

below

headings[i] = headings[i].Replace(” “, “_”);

I think that should work. comma would be invalid in a column name so you will have to convert it to something…. underscore or hyphen perhaps!

4| Andrew said,

David, that makes no sense, because you already have split the row into string chunks delimited by a comma, so you are never going to encounter a comma in any of the heading strings chunks. To do what John wants to do, one would not be able to use the string.Split() method but create one that iterates through the row string character by character and keeps track of how many quotations marks there are and if there is a even number and a comma is encountered then its a new field but if it’s odd then it’s just a comma. Use string.Substring() to extract each field and putting them into a string collection.

5| David Callan said,

Hi Andrew, thanks for the comment. Yeah I see what you say, oversight on my part.

CSV file though - how would one be able to distinguish between a comma as part of a header name and a comma as a regular CSV delimter? When you mention quotations are you talking in terms of escaping characters?

6| John said,

David,
This worked for me:

”’
”’ Will parse a single quote encapsulated CSV string into a string array
”’
”’
”’
”’
Public Function ParseCsvRow(ByVal input As String) As String()

Dim dt As New DataTable()

‘ declare the Regular Expression that will match versus the input string
Dim re As New Regex(”((?[^"",\r\n]+)|”"(?([^""]|”"”")+)”")(,|(?\r\n|\n|$))”)

Dim field As String = “”

Dim mc As MatchCollection = re.Matches(input)

Dim stc As New Specialized.StringCollection()

For Each m As Match In mc
‘ retrieve the field and replace two double-quotes with a single double-quote
field = m.Result(”${field}”).Replace(”"”"”", “”"”)
If field.Length > 0 Then
stc.Add(field)
End If
Next

Dim s(stc.Count - 1) As String
stc.CopyTo(s, 0)
Return s

End Function

7| abhishek said,

hey guys,
i am just designing a software as a part of my project where i need to convert
csv files to sql and xml. and the whole programming is to be done in c#. will u guys be kind enough to tell me how to start. i am not asking for code ..justa few tips…
reagrds
abhi

8| John said,

You can use C# or SQL to select a text (csv) file directly into a data table. You’ll probably want to run some queries on the data to get the correct values. Also, with many of the implementations of this columns are trimmed to 255 characters at the max length.

Depending on the XML requirements you can also handle that through SQL, but it might be easier to create a couple of serializable classes and load the data to the class from a .NET DataTable and then serialize the class.

9| John said,

P.S. For “get the correct values” above, I meant the correct column types and appropriate character lengths on text columns.

10| Aji said,

hi.. all
i cannot get the last row, using the given query for insertion
BULK INSERT portf FROM ‘E:\\portfolio\\WebSite2\\grouped\\2007\1\\EQ020107.CSV’
WITH (FORMATFILE=’C:\\Documents and Settings\\user\\portfol.fmt’,FIRSTROW=2)

11| remove line from excel file | keyongtech said,

[...] carry on the processing from there. It’s simple enough to populate a DataTable manually in code: http://www.akamarketing.com/blog/256-csv-datatable.html — Mark Rae ASP.NET MVP [...]

12| Praveen said,

this block of C# code helped a lot. Thank you so much. Your help is appreciated.

13| hoa said,

please help me split file cvs below
ten, diachi, mail, ….
hung, 1111 hcm, “hung,nguyen@yahoo.com,….
hoa,”123/233 HVB “,” QPN” ,”hoa,dinh@yahoo.com,
…….

14| Sanjay Suman said,

If a Dataset will be used that will be much much better instead of Datatable. And What if we are using this method for more than one CSV file with different number of columns??
Please Help me!!
Thanks in Advance…

15| Komal Dutta said,

Thanks a lot, exactly what i needed.

16| Ayaz said,

Thanks a lot dear i was looking for it from a real long time thank you very much friend :)

17| Bill Costas said,

I’m trying to import a text or csv file to an access database. Can anyone help. I’ve been researching but can’t seem to find the answer. Thanks. Sorry for the duplicate - forgot to check the notify me box.

Leave a Comment
Name:
Email:
Website:
 
HOME | ABOUT US | CONTACT US | SITEMAP | GOOGLE SITE SEARCH | TOP
12 Lorcan Crescent, Santry, Dublin 9, Ireland +353 87 9807629