Solved

Read Excel Missing Data

Posted on 2003-11-10
15
1,467 Views
Last Modified: 2007-12-19
Here is the backend code for a peice of code I am working on.

private void Page_Load(object sender, System.EventArgs e)
            {
                  // Create connection string variable. Modify the "Data Source"
                  // parameter as appropriate for your environment.
                  String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                        "Data Source=C:\\exceltest.xls" + ";" +
                        "Extended Properties=Excel 8.0;";

                  // Create connection object by using the preceding connection string.
                  OleDbConnection objConn = new OleDbConnection(sConnectionString);

                  // Open connection with the database.
                  objConn.Open();

                  // The code to follow uses a SQL SELECT command to display the data from the worksheet.

                  // Create new OleDbCommand to return data from worksheet.
                  OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM RecordData", objConn);

                  // Create new OleDbDataAdapter that is used to build a DataSet
                  // based on the preceding SQL SELECT statement.
                  OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

                  // Pass the Select command to the adapter.
                  objAdapter1.SelectCommand = objCmdSelect;

                  // Create new DataSet to hold information from the worksheet.
                  DataSet objDataset1 = new DataSet();

                  // Fill the DataSet with the information from the worksheet.
                  objAdapter1.Fill(objDataset1, "XLData");

                  // Bind data to DataGrid control.
                  DataGrid1.DataSource = objDataset1.Tables[0].DefaultView;
                  DataGrid1.DataBind();

                  // Clean up objects.
                  objConn.Close();

            }

This is a C-sharp backend for an ASP.NET page. What I am trying to do is read in Data from Excel and pump in out into a DataGrid. I have taken this from the Microsoft KB because I am trying to figure out why it ignores different data types such as dates. The end goal for me is to have a web based app that will read in any excel page and convert it for the web to a datagrid. I have tried to set the HDR=NO;IMEX=1; but that gives me an error, and I cannot seem to get rid of it.  the error was "Could not find installable ISAM."
Any Ideas?
0
Comment
Question by:fester4
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 4

Expert Comment

by:NetPointer
ID: 9719079
why dont u use odbc in place of jet? to make connection...that MIGHT help..

0
 
LVL 27

Expert Comment

by:Dabas
ID: 9720064
Hi fester4,
> "Could not find installable ISAM."
ISAM is not compatible with .NET

I agree with NetPointer. Either use ODBC, or use a reference to Excel and use Excel as an object

Dabas
0
 
LVL 5

Expert Comment

by:tgannetts
ID: 9721906
I tried running your code, and with one minor modification managed to get it to work:

I replaced "SELECT * FROM RecordData" with "SELECT * FROM [RecordData$]", assuming that the name RecordData refers to a worksheet.

The error 'Could not find installable ISAM' is an error I have come across before when using Access VBA. My copy of Access 2000 had not installed properly, so when I tried to transfer an Excel spreadsheet programatically into a database using the TransferSpreadsheet command, I kept on getting this error message. The only way I managed to overcome this was by re-installing Microsoft Access 2000, which re-installed Jet4.0. I wonder if you tried re-installing Jet4.0 only(from Microsoft downloads) whether this would solve your problem.

Otherwise apart from the one modification, your code seems fine.

Tom
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:fester4
ID: 9722412
Ok so I setup the Odbc connection but it still has the same error... basically I think I know what the error is. I think it is because I have multiple data-types in a column it can't handle reading them. Anyone know that fix.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 9722601
You don't need ODBC to get this to work.  The error 'Could not find installable ISAM' means that you have a syntax error in the connect string.  Could you show us the connect string as it is run?
0
 

Author Comment

by:fester4
ID: 9722677
I got the 'Could not find installable ISAM' only when I try to add in the IMEX=1. I have it working except that it doesn't pull up the dates. The excel Sheet uses Columns A,B,C,D... it has strings all the way down A. So not a problem. B has the same thing. C column has a mix of Dates, Strings, etc. D has all strings... The problem is that when it pulls up column C it ignores everytype other than the strings. It loads blank space for the cell. I am wondering how I get it to either treat everything as a string, date or whatever, or how I can get it to just read it in as the different datatypes.
The other thing I am looking at is how to read it in Cell by Cell if neccessary so that I can grab the data that way, in a loop.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 9722721
I use this connect string for Excel:

Data Source=C:\Temp\Test.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"
0
 

Author Comment

by:fester4
ID: 9722775
Does it work with all different datatypes in the columns?
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 9722847
Read this Micro$oft article:

PRB: Excel Values Returned as NULL Using DAO OpenRecordset
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q194/1/24.asp&NoWebContent=1

It applies to ADO type connections also.  The Jet engine uses the registry setting at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows=8 to determine how many rows to use to determine the data type for the column.  The import mode can sometimes still have problems determining data types, but I have found it to be a better mode since I don't get those Null values when the data type doesn't match.
0
 

Author Comment

by:fester4
ID: 9723026
Awesome That was basically what I was looking for... thanks got a question though, I am using a c-sharp backend and when I throw this code at it, it errors...

"Data Source=C:\\exceltest.xls;"+"Extended Properties=Excel 8.0;HDR=Yes;IMEX=1";

but this code works.... What is the deal with setting the IMEX property?

"Data Source=C:\\exceltest.xls" + ";" +"Extended Properties=Excel 8.0;";

It is the same error (the ISAM message) with or without HDR=YES
0
 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 500 total points
ID: 9723063
If you will notice in the example above you need quotes around the Extended Properties:

Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"
0
 

Author Comment

by:fester4
ID: 9723102
TheLearnedOne It won't let me accept any of your comments as answers.... throw an answer up fo rthe points
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 9723886
Did you get this resolved?
0
 

Expert Comment

by:tpmcafee
ID: 10274248
I get nulls too but when I try to use IMEX=1, I get an error. Any clue? Here is my string:

string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\1199sep.xls;Extended Properties=Excel 8.0;IMEX=1";

but this does work:

string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\1199sep.xls;Extended Properties=Excel 8.0;";

Anye clue?
Thomas
0
 
LVL 3

Expert Comment

by:Pro_buddy
ID: 13401329
Hi all,

Your discussion is really helpful and solved my problem. I am able to solve excel datatype issue by changing registry value(eg, TypeGuessRows=100) on my local server. I beleived that client m/c registry might be referred for excel issue, but its referred to server's registry.

Everything worked for me on my local server, but not on host server(since registry value not changed). Can you help me to solve my problem?

Awaiting for your replies!
Pro
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

739 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