Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

Error while reading excel file

Hi all ,
I am doing a task that looks simple - Read the excel .xls workbook having 20 worksheets from .NET front  end using openrowset sql query.
I am not able to do this from database because my dtabase and file are on deperate machines. But thats not my problem anyways. Here it goes---
Until yesterday this code was working fine but suddenly  it is giving me this error

The Microsoft Jet database engine could not find the object '01-Contact$'. Here Contact is 1st worksheet. Make sure the object exists and that you spell its name and the path name correctly.ReadXlsfile:-External table is not in the expected format.
Any idea guys ................?     pls help
I searched many threads regarding this,but nothing were helpful just saying that this is some bug in ms access
Now thing is that sometimes it runs smoothly but else it gives me error and does not parse my excel file at all.
string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + mappath + ";" + "Extended Properties=Excel 8.0;";
            //You must use the $ after the object you reference in the spreadsheet
            OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [01-Contact$] where ContactID is not null", strConn);
 
              
DataSet dscf = new DataSet();
            myCommand.Fill(dscf, "Excelinfo");
            //myDataSet.Tables[0].Columns[3].
            for (int i = 0; i < dscf.Tables[0].Rows.Count; i++)
            {
                exvm.ContactRole = Convert.ToString(dscf.Tables[0].Rows[i]["ContactRole"]);
                exvm.ExternalSystemName = Convert.ToString(dscf.Tables[0].Rows[i]["ExternalSystemName"]);
                exvm.ExternalNameID = Convert.ToString(dscf.Tables[0].Rows[i]["ExternalNameID"]);
                exvm.ExternalOfficeID = Convert.ToString(dscf.Tables[0].Rows[i]["ExternalOfficeID"]);
                exvm.GivenName = Convert.ToString(dscf.Tables[0].Rows[i]["GivenName"]);
                exvm.FamilyName = Convert.ToString(dscf.Tables[0].Rows[i]["FamilyName"]);
                exvm.OfficeName = Convert.ToString(dscf.Tables[0].Rows[i]["OfficeName"]);
                exvm.OfficeOrganizationCode = Convert.ToString(dscf.Tables[0].Rows[i]["OfficeOrganizationCode"]);
                exvm.OfficeDepartment = Convert.ToString(dscf.Tables[0].Rows[i]["OfficeDepartment"]);
                exvm.AddressStreet = Convert.ToString(dscf.Tables[0].Rows[i]["AddressStreet"]);
                exvm.AddressPostalBox = Convert.ToString(dscf.Tables[0].Rows[i]["AddressPostalBox"]);
                exvm.AddressTown = Convert.ToString(dscf.Tables[0].Rows[i]["AddressTown"]);
                exvm.AddressStateRegion = Convert.ToString(dscf.Tables[0].Rows[i]["AddressStateRegion"]);
                exvm.AddressPostalCode = Convert.ToString(dscf.Tables[0].Rows[i]["AddressPostalCode"]);
                exvm.AddressCountry = Convert.ToString(dscf.Tables[0].Rows[i]["AddressCountry"]);
                exvm.ContactPhone = Convert.ToString(dscf.Tables[0].Rows[i]["ContactPhone"]);
                exvm.ContactFax = Convert.ToString(dscf.Tables[0].Rows[i]["ContactFax"]);
                exvm.ContactEmail = Convert.ToString(dscf.Tables[0].Rows[i]["ContactEmail"]);
                exvm.CreatedBy = Convert.ToString(dscf.Tables[0].Rows[i]["CreatedBy"]);
                exvm.contact_CreatedDate = Convert.ToDateTime(dscf.Tables[0].Rows[i]["CreatedDate"]);
                exvm.contact_CreatedTime = Convert.ToDateTime(dscf.Tables[0].Rows[i]["CreatedTime"]);
                exvm.ReplacesID = Convert.ToString(dscf.Tables[0].Rows[i]["ReplacesID"]);
                exvm.Withdrawn = Convert.ToString(dscf.Tables[0].Rows[i]["Withdrawn"]);
                exvm.ContactIDPick = Convert.ToString(dscf.Tables[0].Rows[i]["ContactIDPick"]);
                exvm.projid = projectId;
                exvm.uploaded_file_id = FileID;
                exvm.Contactid = Convert.ToString(dscf.Tables[0].Rows[i]["ContactID"]);
                exvc.proc_insert_project_contact(exvm);
            }

Open in new window

2009-02-26-COBIEChallenge-V08.xls
0
vikasbapat
Asked:
vikasbapat
1 Solution
 
AdamSenior DeveloperCommented:
Are you regenerating the file each time it works/doesn't work, or are you getting different results from the same file?

Check the Excel file is correctly formed each time - all the sheets are there, and the right order etc
0
 
vikasbapatAuthor Commented:
ok i will try it.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now