Click here to Skip to main content
14,734,133 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
can u guys refer me any valuable solution for this .

i wanna import some table values from excel into sqlserver

and i was followed the below link


http://www.aspdotnet-suresh.com/2010/09/import-data-from-excel-to-sql-database.html[^]

i got the error
"The Microsoft Office Access database engine could not find the object 'Sheet1$'"
Posted
Comments
Maciej Los 9-Jul-13 9:11am
   
Share your code...

private bool sheetNameValidation()
        {
//this is ur uploaded file name
            string FileName = Path.GetFileName(FileUpload.PostedFile.FileName);
            string Extension = Path.GetExtension(FileUpload.PostedFile.FileName);
            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
            string FilePath = Server.MapPath(FolderPath + FileName);            
            string ExcelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +                          FilePath + ";Extended Properties=" + "\"Excel 12.0;HDR=YES;\"";

            string myExcelDataQuery = "Select top 1 * from [Data$]";
            OleDbConnection OleDbConn = new OleDbConnection(ExcelConnectionString);
            OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn);
            OleDbConn.Open();

            DataTable dt = new DataTable();
            dt = OleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            if (dt == null)
            {
                return false;
            }

            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;

            // Add the sheet name to the string array.
            foreach (DataRow row in dt.Rows)
            {
                excelSheets[i] = row["TABLE_NAME"].ToString();
                i++;
            }            
            OleDbConn.Close();
//
//
//
//Here you ned to check that the sheet of which data you want to import
// is exist in youe excel file...
            if (!excelSheets.Contains("Data$"))
            {
                Master.showError = "Invalid excel document. Please select correct file.";
                return false;
            }
            else
            {
                return true;
            }
        }



I am sure it will help you...to check the sheet name.

if you want to import data into db then follow the link

How to insert data using SqlBulCopy.[^]


i was also facing the problem, but solve my ownself

if it helps you than rate me... :)
   

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