Convert From DataSet To Csv

Purpose

The purpose of this post is to show one way to convert a simple DataSet into CSV format.

Problem

Sometimes, one needs to get a DataSet into CSV, such as for exporting to Excel.

Design

Make something that can take a DataSet (or a DataTable) and return a CSV.

Solution

I created some simple helper methods, with Brute-Force looping, and best-effort CSV conformance.

/// <summary>
/// This will convert the given DataTable to a Csv.
/// </summary>
/// <param name="targetData">This is the data to convert.</param>
/// <returns>This is a Csv list.</returns>
public static string ConvertToCsv(DataTable targetData)
{
	string myCsv = "";

	if (targetData == null)
	{
		throw new System.ApplicationException("The given object, targetData, is null.");
	}

	DataTable myDataTable = targetData.Copy();
	DataSet myDataSet = new DataSet();
	myDataSet.Tables.Add(myDataTable);

	//Call a helper.
	myCsv = Team.Framework.Common.Core.Utility.ConvertToCsv(myDataSet);

	return myCsv;
}

/// <summary>
/// This will convert the given DataSet to a Csv.
/// </summary>
/// <param name="targetData">This is the DataSet to convert, at most 1 DataTable.</param>
/// <returns>This is a Csv list.</returns>
public static string ConvertToCsv(DataSet targetData)
{
	string myCsv = "";

	if (targetData == null)
	{
		throw new System.ApplicationException("The given object, targetData, is null.");
	}

	if (targetData.Tables == null)
	{
		throw new System.ApplicationException("The given object, targetData.Tables, is null.");
	}

	const int DefaultRequiredTableCount = 1;

	if (targetData.Tables.Count != DefaultRequiredTableCount)
	{
		throw new System.ApplicationException("ActualTableCount='" + targetData.Tables.Count.ToString() + 
			"' must equal RequiredTableCount='" + DefaultRequiredTableCount.ToString() + "'.");
	}

	if (targetData.Tables[0] == null)
	{
		throw new System.ApplicationException("The given object, targetData.Tables[0], is null.");
	}

	if (targetData.Tables[0].Columns == null)
	{
		throw new System.ApplicationException("The given object, targetData.Tables[0].Columns, is null.");
	}

	if (targetData.Tables[0].Rows == null)
	{
		throw new System.ApplicationException("The given object, targetData.Tables[0].Rows, is null.");
	}

	//Get a helper.
	StringBuilder myBuilder = new StringBuilder();

	//Add a row in the Csv that contains the columns-names from the DataTable.

	bool isFirst1 = true;

	foreach (DataColumn myColumnTemp1 in targetData.Tables[0].Columns)
	{
		string myValueTemp1 = "";

		if (isFirst1)
		{
			isFirst1 = false;
		}
		else
		{
			myBuilder.Append(",");
		}

		myBuilder.Append("\"");
		myValueTemp1 = myColumnTemp1.ColumnName;
		myValueTemp1 = myValueTemp1.Replace("\"", "\"\"");
		myBuilder.Append(myColumnTemp1.ColumnName);
		myBuilder.Append("\"");
	}

	//Add a row in the Csv for each row in the DataTable.

	foreach (DataRow myRowTemp in targetData.Tables[0].Rows)
	{
		myBuilder.Append(Environment.NewLine);
		bool isFirst2 = true;

		foreach (DataColumn myColumnTemp2 in targetData.Tables[0].Columns)
		{
			string myValueTemp2 = "";

			if (isFirst2)
			{
				isFirst2 = false;
			}
			else
			{
				myBuilder.Append(",");
			}

			myBuilder.Append("\"");

			if ((myRowTemp[myColumnTemp2.ColumnName].GetType() == typeof(bool)) ||
				(myRowTemp[myColumnTemp2.ColumnName].GetType() == typeof(byte)) ||
				(myRowTemp[myColumnTemp2.ColumnName].GetType() == typeof(char)) ||
				(myRowTemp[myColumnTemp2.ColumnName].GetType() == typeof(decimal)) ||
				(myRowTemp[myColumnTemp2.ColumnName].GetType() == typeof(double)) ||
				(myRowTemp[myColumnTemp2.ColumnName].GetType() == typeof(float)) ||
				(myRowTemp[myColumnTemp2.ColumnName].GetType() == typeof(int)) ||
				(myRowTemp[myColumnTemp2.ColumnName].GetType() == typeof(long)) ||
				(myRowTemp[myColumnTemp2.ColumnName].GetType() == typeof(sbyte)) ||
				(myRowTemp[myColumnTemp2.ColumnName].GetType() == typeof(short)) ||
				(myRowTemp[myColumnTemp2.ColumnName].GetType() == typeof(string)) ||
				(myRowTemp[myColumnTemp2.ColumnName].GetType() == typeof(uint)) ||
				(myRowTemp[myColumnTemp2.ColumnName].GetType() == typeof(ulong)) ||
				(myRowTemp[myColumnTemp2.ColumnName].GetType() == typeof(ushort)))
			{
				myValueTemp2 = myRowTemp[myColumnTemp2.ColumnName].ToString();
				myValueTemp2 = myValueTemp2.Replace("\"", "\"\"");
			}
			else
			{
				myValueTemp2 = "";
			}

			myBuilder.Append(myValueTemp2);
			myBuilder.Append("\"");
		}
	}

	myCsv = myBuilder.ToString();

	return myCsv;
}

HTH.

Thank you.

— Mark Kamoski

Advertisements

Author: mkamoski1

n/a