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.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
else //if no headers just go for col1, col2 etc.
for (int i = 0; i < headings.Length; i++)
//create arbitary column names
//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++)
row[j] = csvData[i].Split(’,')[j];
//add rows to over DataTable
//return the CSV DataTable
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();
gv.DataSource = csvDataTable;
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:
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():
Please let me know if you have any comments and/or questions.