CODEWITHSUNDEEP
c#.netexcel
user167698
user167698

Reputation: 1863

Unable to read excel sheet

This is strange, I'm trying to access an excel sheet with a data reader and getting an error that I'm having trouble figuring out. Was wondering if anyone ever came across the same issue.

The Microsoft Office Access database engine could not find the object 'SHEETNAME'. Make sure the object exists and that you spell its name and the path name correctly

var str = new StringBuilder();

        using (var myConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=foreignkeys.xlsx;Extended Properties='Excel 8.0;HDR=Yes'"))
        {
            using (var myCommand = new OleDbCommand())
            {
                myConnection.Open();
                myCommand.Connection = myConnection;
                myCommand.CommandText = "select TableName, ColumnName from [SHEETNAME]";

                using (var dr = myCommand.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        var tableName = dr["TableName"].ToString();
                        var columnName = dr["ColumnName"].ToString();
                        var tmp = columnName;
                        tmp = tmp.Replace("ID", "").Replace("Id", "");

                        str.AppendLine(string.Format("IF NOT EXISTS ( SELECT 1 FROM sys.foreign_keys AS FK WHERE name = 'FK_{0}_{1}' AND parent_object_id = OBJECT_ID('{2}'))", tableName, tmp, tableName));
                        str.AppendLine("BEGIN");
                        str.AppendLine(string.Format("ALTER TABLE dbo.{0} WITH CHECK", tableName));
                        str.AppendLine(string.Format("ADD CONSTRAINT FK_{0}_{1} FOREIGN KEY ({2}) REFERENCES dbo.{3}({4})", tableName, tmp, columnName, tmp, columnName));
                        str.AppendLine("END");
                        str.AppendLine("");
                        str.AppendLine("");
                    }
                }                    
            }
        }
        return str.ToString();

Upvotes: 0

Views: 201

Answers (1)

Steve
Steve

Reputation: 216353

Assuming that your excel file contains a sheet named SHEETNAME then you need to write

 myCommand.CommandText = "select TableName, ColumnName from [SHEETNAME$]";
                                                                      ^

Upvotes: 1

Related Questions