Click here to Skip to main content
14,669,324 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi All,

I'm able to upload bulk data to SQLServer DB but before uploading I need to validate excel data.

For example: If any cell left as empty, have to show msg like plz fill the cell..

I googled alot bt no luck:( Plz help me asap as it is very important.
ZurdoDev 8-Jan-13 7:56am
You'll likely want to use the Excel dll so that you can open and use the classes that Excel has.
Sanjay Debnath 8-Jan-13 14:43pm
Or you can fetch the data using OLEBD into datatable & then run your checks, I guess its better than reading excel cell by cell for validation.
Mokshagna 9-Jan-13 0:34am
using (OleDbDataReader dr = command.ExecuteReader())
using (bulkcopy = new SqlBulkCopy(ConnectionString))
SqlBulkCopy s = new SqlBulkCopy(ConnectionString);

bulkcopy.DestinationTableName = "tablename";

If I'm using datatable in this....I'm getting error: No rows found in the table eventhough I have data in the excel sheet.

Please help me...
[no name] 8-Jan-13 15:09pm
How much data are we talking about? If its 10,000 records or less you can probably do this in memory with little effect on performance.
Mokshagna 9-Jan-13 0:35am
It'll be less than 1000 rows
[no name] 9-Jan-13 9:26am
Import the information into a datatable and parse the information for nulls and blanks, it should only take a few seconds

Rate this:
Please Sign up or sign in to vote.

Solution 1

I get the data from a sheet to datatable with the following code. Please check if it'll help you or not.

/// <summary>
        /// Fetch Sheet As DataTable
        /// </summary>
        /// <param name="SheetName">SheetName</param>
        /// <returns>A DataTable</returns>
        public static System.Data.DataTable FetchSheet(string ExcelFilePath, string SheetName)
                string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ExcelFilePath + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
                OleDbConnection objConn = new OleDbConnection(sConnectionString);
                OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + SheetName + "$]", objConn);
                OleDbDataAdapter objAdapter = new OleDbDataAdapter();
                objAdapter.SelectCommand = objCmdSelect;
                System.Data.DataTable dtReturned = new System.Data.DataTable();
                return dtReturned;
            catch (Exception ex)
                return null;
Rate this:
Please Sign up or sign in to vote.

Solution 2


Before insert data to database , you can store that information in one datatable
by using for / foreach you can fetch all the rows by using that datatable.
In page wise your fetching the Excel column value, then u can check that column is empty means you display some message or otherwise data to be inserted into database.

Recently i'm also do the same thing this is works for me.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100