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

x
?
Solved

Read Excel Missing Data

Posted on 2003-11-10
15
Medium Priority
?
1,484 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 2000 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 2000 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

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