1

I am using C# LinqToExcel to upload data from a spreadsheet to the database. I have noticed when the file is open it is able to retrieve the data from the spreadsheet. However if it is not open on my machine I get this error:

base {System.Data.Common.DbException} = {"External table is not in the expected format."}

Errors = {System.Data.OleDb.OleDbErrorCollection}

This is when I try to expand results view.

This is my C# code:

 [HttpPost]
        public void CreateUsersBulk(ModelBulkUpload model, HttpPostedFileBase file)
        {
            if (file != null && file.ContentLength > 0) // Pass to Data Layer if true
            {
                DataLayer.ExcelParser ex = new DataLayer.ExcelParser();
                ex.BulkUploadChoice(model.Type.ToString().Trim().ToLower(), file);
            }
        }

public void BulkUploadChoice(string Choice, System.Web.HttpPostedFileBase File)
        {

            switch (Choice)
            {
                case "create":
                    BulkUploadCreate(File);
                    break;
                case "edit":
                    BulkUploadEdit();
                    break;
            }
        }

 public void BulkUploadCreate(System.Web.HttpPostedFileBase File)
        {
            var excel = new ExcelQueryFactory();
            excel.FileName = "C:/Users/nickgowdy/Desktop/UM Bulk Upload/UM - BulkUpload.xlsm";
             //excel.DatabaseEngine = LinqToExcel.Domain.DatabaseEngine.Ace;

            var query = from q in excel.Worksheet("Users")
                        select q;

            //var query = from q in excel.("A1", "L20", "Users")
            //            select q;

            foreach (LinqToExcel.Row row in query)
            {
                Model.ModelUser u = new Model.ModelUser();

            }
        }

Do I need to write some more code for it to work the way I want it to. I basically when them to click the upload button to select the spreadsheet from windows explorer window. I don't want the spreadsheet to be open while this code is running.

nick gowdy
  • 6,191
  • 25
  • 88
  • 157
  • isn't it duplicate of [http://stackoverflow.com/questions/1139390...](http://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format) ? – kasitan Oct 04 '13 at 09:26
  • @kasitan The problem is the same but I am using LinqToExcel so where do I amend my code to fix the problem? – nick gowdy Oct 04 '13 at 09:33
  • 1
    @kasitan Yes I used "excel.DatabaseEngine = DatabaseEngine.Ace;" but I get the same error message. The excel file is .xlsm but I have tried .xls as well. – nick gowdy Oct 04 '13 at 09:57
  • yes, I noticed commented line in code immediately as I posted comment so I deleted it, sorry. – kasitan Oct 04 '13 at 10:03
  • @nickgowdy i used your Database Engine settings for my .xls file. This meant my database engine was changed from DataEngine.Jet to Database Engine.Ace. This fixed my problem of not being able to read from spreadsheet when the file itself was closed. Thanks now my app can read from the sheet whether its open or in closed state. – Sike12 Dec 18 '13 at 16:43

0 Answers0