Solved

Error while reading excel file

Posted on 2009-04-12
2
536 Views
Last Modified: 2013-11-07
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
Comment
Question by:vikasbapat
2 Comments
 
LVL 12

Accepted Solution

by:
Cyber-spy earned 250 total points
ID: 24126489
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
 

Author Closing Comment

by:vikasbapat
ID: 31569331
ok i will try it.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question