Solved

Error while reading excel file

Posted on 2009-04-12
2
532 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
Comment Utility
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
Comment Utility
ok i will try it.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

772 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now