Export Data Table to CSV file with comma values

public void WriteCSVToResponse(DataTable dt, string fileName, string delimiter)
    {
        //prepare the output stream
        Response.Clear();
        Response.ContentType = "text/csv";
        Response.AppendHeader("Content-Disposition",
            string.Format("attachment; filename={0}", fileName));
      
        //write the csv column headers
        if (dt != null && dt.Rows.Count > 0)
        {
           
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (dt.Columns[i].ColumnName != "FormsResponseXML")
                {
                    Response.Write(dt.Columns[i].ColumnName);
                    Response.Write((i < dt.Columns.Count - 1) ? delimiter : Environment.NewLine);
                }
            }

            //write the data
            foreach (DataRow row in dt.Rows)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (dt.Columns[i].ColumnName != "FormsResponseXML")
                    {                       
                        var append = row[i].ToString().Contains(",")? string.Format("\"{0}\"", row[i].ToString()): row[i].ToString();
                        Response.Write(append);
                        Response.Write((i < dt.Columns.Count - 1) ? delimiter : Environment.NewLine);
                    }
                }
            }
        }

        Response.End();
    }

Convert DataTable to CSV file

 public void WriteCSVToResponse(DataTable dt, string fileName, string delimiter)
    {
        //prepare the output stream
        Response.Clear();
        Response.ContentType = "text/csv";
        Response.AppendHeader("Content-Disposition",
            string.Format("attachment; filename={0}", fileName));

        //write the csv column headers
        if (dt != null && dt.Rows.Count > 0)
        {
           
            for (int i = 0; i < dt.Columns.Count; i++)
            {
               
                    Response.Write(dt.Columns[i].ColumnName);
                    Response.Write((i < dt.Columns.Count - 1) ? delimiter : Environment.NewLine);
               
            }

            //write the data
            foreach (DataRow row in dt.Rows)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                   
                        Response.Write(row[i].ToString());
                        Response.Write((i < dt.Columns.Count - 1) ? delimiter : Environment.NewLine);
                   
                }
            }
        }

        Response.End();
    }

Reverse Data Table

This Method is used the Reverse the given input DataTable

For Eample,

Input Data Table has the following Records,

NAME TITLE DEV_IN SCOST DCOST SOLD
-------- -------------------- -------- ---------- ---------- ----------
RAMESH HOTEL MANAGEMENT DBASE 12000 35000 4
RAMESH DEAD LEE PASCAL 99.95 4500 73

public static DataTable ReverseDataTable(DataTable dataTable)
{
DataTable table = new DataTable();
DataRow[] rowArray = dataTable.Select();
table = dataTable.Clone();
for (int i = rowArray.Length - 1; i >= 0; i--)
{
table.ImportRow(rowArray[i]);
}
return table;
}

OutPut

NAME TITLE DEV_IN SCOST DCOST SOLD
-------- -------------------- -------- ---------- ---------- ----------
RAMESH DEAD LEE PASCAL 99.95 4500 73
RAMESH HOTEL MANAGEMENT DBASE 12000 35000 4

Filter DataTable and Sort

public static DataTable FilterDataTable(DataTable dataTable, string filterQuery, string sortQuery)
{
DataTable table = new DataTable();
try
{
DataRow[] rowArray = dataTable.Select(filterQuery, sortQuery);
table = dataTable.Clone();
foreach (DataRow row in rowArray)
{
table.ImportRow(row);
}
}
catch
{
table = dataTable.Clone();
}
return table;
}

Create Excel File

public static bool CreateExcelFile(DataTable dataTable, string fileName)
{
try
{
string str = "";
string str2 = "";
string str3 = "";
string str4 = string.Empty;
StringBuilder builder = new StringBuilder();
foreach (DataColumn column in dataTable.Columns)
{
str3 = str3 + "" + column.ColumnName + "";
}
str3 = str3 + "";
foreach (DataRow row in dataTable.Rows)
{
builder.Append("");
for (int i = 0; i < dataTable.Columns.Count; i++)
{
str4 = " ";
if (!string.IsNullOrEmpty(row[i].ToString()))
{
str4 = row[i].ToString();
}
builder.Append("" + str4 + "");
}
builder.Append("");
}
string s = str + str3 + builder.ToString() + str2;
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType = "application/vnd.xls";
HttpContext.Current.Response.Write(s);
HttpContext.Current.Response.Flush();
return true;
}
catch
{
return false;
}
}

Read CSV File and covert to DataTable

public static DataTable ReadCSV(string filePath, DataTable csvTable)

{

//DataTable csvTable = new DataTable();

// open the file "data.csv" which is a CSV file with headers

using (CsvReader csv = new CsvReader(new StreamReader(filePath), true))

{

// missing fields will not throw an exception,

// but will instead be treated as if there was a null value

csv.MissingFieldAction = MissingFieldAction.ReplaceByNull;

// to replace by "" instead, then use the following action:

//csv.MissingFieldAction = MissingFieldAction.ReplaceByEmpty;

int fieldCount = csvTable.Columns.Count;

// this bit could be modified to fine-tune the columns

while (csv.ReadNextRecord())

{

DataRow newRow = csvTable.NewRow();

for (int i = 0; i < fieldCount; i++)

newRow[i] = csv[i] == null ? "MISSING" : csv[i];

csvTable.Rows.Add(newRow);

}

}

return csvTable;

}

Join Two Data Tables

public static DataTable JoinTable(DataTable firstTable, DataTable secondTable, string firstTableKey, string secondTableKey)

{

DataTable dtResults = secondTable.Clone();

foreach (DataColumn dataColumn in firstTable.Columns)

{

if (!dtResults.Columns.Contains(dataColumn.ColumnName))

dtResults.Columns.Add(dataColumn.ColumnName, typeof(string));

}

foreach (DataRow dataRow in firstTable.Rows)

{

DataRow dtRow = dtResults.NewRow();

foreach (DataColumn dataColumn in firstTable.Columns)

{

dtRow[dataColumn.ColumnName] = dataRow[dataColumn.ColumnName].ToString();

}

foreach (DataRow finalRow in secondTable.Select("[" + secondTableKey + "] = '" + dataRow[firstTableKey].ToString() + "'"))

{

foreach (DataColumn dataColumn in secondTable.Columns)

{

dtRow[dataColumn.ColumnName] = finalRow[dataColumn.ColumnName].ToString();

}

break;

}

dtResults.Rows.Add(dtRow);

}

return dtResults;

}

Sort the Data Table by Column

public DataTable SortDataTableByColumn(DataTable dataTable, string sortColumnName, string sortOrder)

{

DataRow[] filteredRows;

if (!string.IsNullOrEmpty(sortColumnName))

{

if (string.IsNullOrEmpty(sortOrder))

sortOrder = "ASC";

filteredRows = dataTable.Select(string.Empty, sortColumnName + " " + sortOrder);

DataTable filteredDataTable = dataTable.Clone();

foreach (DataRow dataRow in filteredRows)

{

filteredDataTable.ImportRow(dataRow);

}

return filteredDataTable;

}

else

{

return dataTable;

}

}

Trim Table

public static DataTable TrimDataTable(DataTable dataTable, int noOfRows)

{

DataRow[] dataRows = dataTable.Select();

DataTable filteredDataTable = dataTable.Clone();

for (int i = 0; i < dataRows.Length; i++)

{

filteredDataTable.ImportRow(dataRows[i]);

if (i >= noOfRows - 1)

break;

}

return filteredDataTable;

}