Click here to Skip to main content
15,896,111 members
Please Sign up or sign in to vote.
1.00/5 (1 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.
Posted
Comments
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";
bulkcopy.WriteToServer(dr);
}
dr.Close();
}
}

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

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

C#
/// <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)
        {
            try
            {
                string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ExcelFilePath + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
                OleDbConnection objConn = new OleDbConnection(sConnectionString);
                objConn.Open();
                OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + SheetName + "$]", objConn);
                OleDbDataAdapter objAdapter = new OleDbDataAdapter();
                objAdapter.SelectCommand = objCmdSelect;
                System.Data.DataTable dtReturned = new System.Data.DataTable();
                objAdapter.Fill(dtReturned);
                objConn.Close();
                return dtReturned;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }
 
Share this answer
 
HI,

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.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900