The Self-Taught Programmer

Recipes from real-world experience with asp.net.

Posts Tagged ‘CSV

How To Get Values from a DataTable without Crashing if a Column Doesn’t Exist

leave a comment »

The Situation

You have uploaded data into a DataTable (perhaps from a CSV file – see How To Parse a CSV File Into a DataTable for Use In Your Code) and now you need to retreive the data from the DataTable.

BUT because the data has come from an uploaded from a CSV file, it is possible that one or more of the required columns could be missing. How do you handle this problem without causing exceptions to be thrown?

The best solution in many cases would be to test the uploaded data to make sure all the columns are there, and if they aren’t, to notify the user to correct the file and reupload.

But what if you want to process the file even if a column is missing?

A Solution

In this situation, a good thing to do would be to process the data, but also notify the user that columns were missing. I do this by creating a private variable in the class:

private List<string> _missingcolumns;

Then create this function:

private static string GetColumnValue(DataRow row, string column)
{
	string result = "";
	if (row.Table.Columns.Contains(column))
	{
		if (CommonBase.HasValue(row[column].ToString()))
		{
			result = row[column].ToString();
		}
	}
	else
	{
		// Add to the list of missing columns if it isn't already in there
		if ( _missingcolumns.FindIndex(s => s == column) == -1 )
		{
			_missingcolumns.Add(column);
		}
	}

	return result;
}

Note that this function calls my common function library method ‘HasValue’, which can be found here.

Then call the function like this:

foreach ( DataRow r in dtable.Rows )
{
	string somevar = GetColumnValue(r, "Company Name");
}

(The variable ‘dtable’ is of type DataTable.)

So if the column exists and it has a value, you get the value (as a string). If the column exists but is null, you get back an empty string. If the column doesn’t exist, you get back an empty string. (You can of course alter this to suit your needs.)

Notify the User

If the column doesn’t exist, the name of the column is added to the _missingcolumns list, which you can use to notify the user that their data was processed but was incomplete.

Advertisements

Written by Jim McMullen

October 10, 2011 at 3:25 pm

Posted in How-To

Tagged with ,

How To Parse a CSV File Into a DataTable for Use In Your Code

with one comment

The situation:

You have uploaded a CSV file, and you now need to do something with the data in your programming.

A Solution:

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

public static DataTable ParseCSV(string path, bool headerinfile)
{
	if ( !File.Exists(path) )
		return null;

	string headertext = "No";
	if ( headerinfile )
	{
		headertext = "Yes";
	}

	string full = Path.GetFullPath(path);
	string file = Path.GetFileName(full);
	string dir = Path.GetDirectoryName(full);

	//create the "database" connection string 
	string connString = "Provider=Microsoft.Jet.OLEDB.4.0;"
	  + "Data Source=\"" + dir + "\\\";"
	  + "Extended Properties=\"text;HDR=" + headertext + ";FMT=Delimited\"";

	//create the database query
	string query = "SELECT * FROM " + file;

	//create a DataTable to hold the query results
	DataTable dTable = new DataTable();

	//create an OleDbDataAdapter to execute the query
	OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);

	try
	{
		//fill the DataTable
		dAdapter.Fill(dTable);
	}
	catch ( InvalidOperationException /*e*/)
	{ throw; }

	dAdapter.Dispose();

	return dTable;
}

Written by Jim McMullen

October 10, 2011 at 1:33 pm

Posted in How-To

Tagged with