Bite Size C# – XLS to CSV Converter

Let us have a look at my XLS to CSV Converter class today.

It is a static class, which is located in the Core.CSV namespace. It contains one static method CovertExcelToCsv which takes 3 parameters excelFilePath, csvOutputFile and worksheetNumber. The excelFilePath parameter is used to input the source Excel file, csvOutputFile is used to input the destination CSV file and lastly worksheetNumber is used to define which worksheet to convert, as CSV files do not have worksheets only one worksheet can be converted to CSV file at a time. The worksheetNumber parameter has a default value of 1 in the event that no value is passed in.

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace Core.CSV
{
 public static class XlsToCsvConverter
 {
  public static void CovertExcelToCsv(string excelFilePath, string csvOutputFile, int worksheetNumber = 1)
  {
   if (!File.Exists(excelFilePath)) throw new FileNotFoundException(excelFilePath);
   if (File.Exists(csvOutputFile)) throw new ArgumentException("File exists: " + csvOutputFile);

   // Connection string
   var connectionStr =
    String.Format(
     "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"",
     excelFilePath);
   var cnn = new OleDbConnection(connectionStr);

   // Get schema information and then data in spreadsheet
   var dt = new DataTable();
   try
   {
    cnn.Open();
    var schemaTable = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    if (schemaTable != null && schemaTable.Rows.Count < worksheetNumber)
     throw new ArgumentException("The worksheet number requested does not exist.");
    if (schemaTable != null)
    {
     var worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
     var sql = String.Format("select * from [{0}]", worksheet);
     var da = new OleDbDataAdapter(sql, cnn);
     da.Fill(dt);
    }
   }
   catch (Exception ex)
   {
    //Insert logging of exception here
    throw;
   }
   finally
   {
    // Close connection to the spreadsheet
    cnn.Close();
   }

   // Write out CSV data
   using (var wtr = new StreamWriter(csvOutputFile))
   {
    foreach (DataRow row in dt.Rows)
    {
     var firstLine = true;
     foreach (DataColumn col in dt.Columns)
     {
      if (!firstLine)
      {
       wtr.Write(",");
      }
      else
      {
       firstLine = false;
      }
      var data = row[col.ColumnName].ToString().Replace("\"", "");
      wtr.Write(String.Format("{0}", data));
     }
     wtr.WriteLine();
    }
   }
  }
 }
}
Bite Size C# – XLS to CSV Converter

Bite Size SQL – Basic Query Optimisation

How SQL queries are written can have a huge impact on how quickly and efficiently they execute. In this post I will explain some very basic methods that can be used to optimise SQL queries.

  • When writing select queries, only select the columns you need. In general using “SELECT *” is very bad practice.
  • Avoid using sub queries, rather use joins. What is meant by this is instead of using:
        SELECT COLUMNA FROM TABLEA WHERE COLUMNB IS IN (SELECT COLUMNB FROM TABLEB)
        Rather use:
        SELECT COLUMNA FROM TABLEA AS TA INNER JOIN TABLEB AS TB ON TA.COLUMNB = TB.COLUMNB
  • Use WHERE to limit the result set to only what you need. There is no point in pulling 10000 records if you are only looking for records, for example in a certain date range.
  • Use WITH NOLOCK with SELECT queries when feasible, this can prevent deadlocks which can cause serious performance issues. However note that using WITH NOLOCK can result in a non-accurate result set being returned which contains non committed records, so keep this in mind when using. But when querying a production system database this risk might be outweighed by the risk of deadlocks occuring.

One last point on stored procedures, this is not really an optimisation point as much as a best practice point. When developing stored procedures always write them to return result sets of the same dimensions. I have worked on numerous legacy systems were this was not the case and it results in a complete nightmare, especially with projects that utilise an ORM.

Bite Size SQL – Basic Query Optimisation