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

Archive for October, 2008


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

Monday, October 6th, 2008

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.

HOME | ABOUT US | CONTACT US | SITEMAP | GOOGLE SITE SEARCH | TOP
12 Lorcan Crescent, Santry, Dublin 9, Ireland +353 87 9807629