Converting DataTable into CSV format

Posted on August 1, 2008

woo themes Premium Wordpress layouts skins

The below code will help in converting the datatable into CSV foramat, which can be opened in MS Excel. This task is essential when you want to mail tabular data as attachment to your receipent. And especially whcn you don't have MS Excel installed in your server.

 public static void SaveDataTableToCsvFile(string AbsolutePathAndFileName, DataTable TheDataTable, params string[] Options)
        {
            //variables
            string separator;
            if (Options.Length > 0)  { separator = Options[0]; }
	    else { separator = ","; //default            }
            string quote = "\"";
 
            //create CSV file
            StreamWriter sw = new StreamWriter(AbsolutePathAndFileName);
 
            //write header line
            int iColCount = TheDataTable.Columns.Count;
            for (int i = 0; i < iColCount; i++)
            {
                sw.Write(TheDataTable.Columns[i]);
                if (i < iColCount - 1) { sw.Write(separator);  }
            }
            sw.Write(sw.NewLine);
 
            //write rows
            foreach (DataRow dr in TheDataTable.Rows)
            {
                for (int i = 0; i < iColCount; i++)
                {
                    if (!Convert.IsDBNull(dr[i]))
                    {
                        string data = dr[i].ToString();
                        data = data.Replace("\"", "\\\"");
                        sw.Write(quote + data + quote);
                    }
                    if (i < iColCount - 1)  { sw.Write(separator);  }
                }
                sw.Write(sw.NewLine);
            }
            sw.Close();
        }