Parse XML & CSV Files into a Data Table

As I have mentioned in the past, I am a fan of anything that can help automate a task and will do anything outside the box to get a process automated and use all the resources at my disposal. One of out latest projects that we are working on is a coupon site that will automatically grab coupons from all of our affiliates and post them automatically. We also wanted to have the ability to updated existing coupons if a better deal is found. Enter a .NET service.

The major thing I wonted to cover in this post was how to get the separate files we would be receiving from our affiliates into data tables. The types of files we will received for this project are both CSV (Comma Separated Values) as well as XML (Extensible Markup Language). I wanted the end result to be the same type of object so that the same class could handle the processing algorithms without having to use overloaded methods or any type of polymorphism. Getting them to data tables seemed like a fairly viable solution because then I would only need to know which columns to grab based off which affiliate we were currently processing; information which is stored in the database with all of the affiliate information.

Getting these files to a data table actually ended up being a fairly easy task once I sat down and thought about it as well as look through some online articles about common ways of handling this type of information. Having worked with XML a lot in the past, I was confident this should not be hard. Let’s start taking a look at how it works. Lets start with the CSV file.

public DataTable ParseCSV(string path)
{
    if (!File.Exists(path))
        return null;
    string full = Path.GetFullPath(path);
    string file = Path.GetFileName(full);
    string dir = Path.GetDirectoryName(full);
    string connString = "Provider=Microsoft.Jet.OLEDB.4.0;"
    + "Data Source=\"" + dir + "\\\";"
    + "Extended Properties=\"text;HDR=No;FMT=Delimited\"";
    string query = "SELECT * FROM " + file;
    DataTable dTable = new DataTable();
    OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);
    try
    {
        dAdapter.Fill(dTable);
    }
    catch (Exception ex)
    {
        WriteError(ex);
    }
    dAdapter.Dispose();
    return dTable;
}

As you can see, not a whole lot to it. It simply gets passed a file location on the hard drive, gets the file information, and builds a OLEDB request setting the data source as the directory. Then we select * from the file which we can throw the results into a data table. Then we just run it through the rest of the class for it to process each row to see if the store exists, the coupon exists, and that the coupon is active and create database record where necessary.

StringReader XMLReader= new StringReader(filePath);
DataSet XMLTables= new DataSet();
theDataSet.ReadXml(XMLReader.ReadToEnd());
DataTable dtValues = XMLTables.Tables[Convert.ToInt32(intTableDepth)];
return dtValues;

This was the one I didn’t realize would be as easy as it was. When the file is passed through the ReadToEnd it parses the information into a data set, each child set having it’s own table. All that is needed to parse the XML properly is to know how deep the data is housed within the XML (the child nodes that house each item). In this case, I have a function that figures it out and passes it to this function when it’s called.

Now, all that needs done is to pass the data table to the functions that iterate through the information and collect what is needed. That pretty much sums up that part of this project. Thanks for reading.