Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server C#4.0
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 luckFrown | :( Plz help me asap as it is very important.
Posted 8-Jan-13 0:31am
Comments
ryanb31 at 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 at 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 at 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...
Mathlab at 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 at 9-Jan-13 0:35am
   
It'll be less than 1000 rows
Mathlab at 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: bad
good
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)
        {
            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;
            }
        }
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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.
  Permalink  

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



Advertise | Privacy | Mobile
Web02 | 2.8.141022.1 | Last Updated 9 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

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