Solved

Read Excel Missing Data

Posted on 2003-11-10
15
1,455 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
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
 

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
What Security Threats Are You Missing?

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.

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

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…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 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

23 Experts available now in Live!

Get 1:1 Help Now