Hi.. i have imported a excel in to datatable which contains 5 columns and some thousand rows. I have to do two level validation.

First i have to check whether the column belongs to specific datatype.

If so, then i need to check each and every cell in that column for the constraints.

How can i achieve this using c#?


Name   No   Num   Value
apple    1      3       10.9
ruby     10    3        4.5
john      4    11       2.5

i have to find the datatype first and here the second colum contains No which is of datatype 'int' and the constraints are 0 - 9. As in second row it has value 10 in it, i want to find that value and replace it with null value. Any cell out of the constraint property should be replaced with null values

Please help me ...

I'm assuming you're able to get the data into a DataTable. This should push you in the right direction.

DataTable dtExcelImport = MethodThatGetsThatExcelImportTable();

//If you use regex, declare it up here so it doesn't get created a bunch of times
string sDataType = string.Empty;

foreach (DataColumn dc in dtExcelImport.Columns)
	//Based on column name, give it a data type for validation
	switch (dc.ColumnName)
		case "Name":
			sDataType = "string";

		case "No":
			sDataType = "Int1to9";

		case "Num":
			sDataType = "Int";

		case "Value":
			sDataType = "Money";

		//case "More!":
		//    sDataType = "More!";
		//    break;

			sDataType = "unknown";
	//If the dc.ColumnName aren't set, then perhaps you could use a column index:
	//for (int i = 0; i < dtExcelImport.Columns.Count; i++)
	//    switch (i)
	//    {
	//        case 1:
	//            break;
	//        case 2:
	//            break;
	//    } 

	//Go though each row and use the validation type selected above on that row
	foreach (DataRow dr in dtExcelImport.Rows)
		switch (sDataType)
			case "string":
				if (dr[dc.ColumnName].ToString().Length > 50)
					//String too long? >.>
					//Do some error stuff to it

				//Perhaps you want to clean up the data.  This is a healthy spot to do it.
				//I don't think an Excel import can have a null in it, which if it did will error here.
				dr[dc.ColumnName] = dr[dc.ColumnName].ToString().ToLower();

			case "Int1to9":
				//Make sure each item is 1 to 9, or null it
				if(((Int32)dr[dc.ColumnName]) > 9 || ((Int32)dr[dc.ColumnName]) < 1)
					dr[dc.ColumnName] = null;//or is this System.DBNull... I'm not sure >.>

				//Add any other validation

			case "uniqueInt":
				//Making sure each value is unique, quite a task.
				DataRow[] dra = dtExcelImport.Select(dc.ColumnName + " = '" + dr[dc.ColumnName] + "'");
				if(dra.Length > 1)
					//This value isn't unique >.>
					//Do some error stuff to it

				//Add any other validation

			case "Int":
				//Use regex to see if this is an int.  The regex should be declared before the first foreach
				//to save time.
				//http://www.c-sharpcorner.com/uploadfile/prasad_1/regexppsd12062005021717am/regexppsd.aspx ^.^
				//If you have too much horsepower and want to be ineffecient, you could
				//try catch and Convert.ToInt32(dr[dc.ColumnName]), but thats ugly >.>

			case "Money":
				//Put another regex to use here looking for a double, float, or perhaps USD or however your data is formated.
				//Once you understand the basics of C# regex creation, there are libraries of Regex that can look for what
				//you need.
This article has been dead for over six months. Start a new discussion instead.