Link to home
Start Free TrialLog in
Avatar of fester4
fester4

asked on

Read Excel Missing Data

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?
Avatar of NetPointer
NetPointer

why dont u use odbc in place of jet? to make connection...that MIGHT help..

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
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
Avatar of fester4

ASKER

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.
Avatar of Bob Learned
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?
Avatar of fester4

ASKER

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.
I use this connect string for Excel:

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

ASKER

Does it work with all different datatypes in the columns?
ASKER CERTIFIED SOLUTION
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fester4

ASKER

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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fester4

ASKER

TheLearnedOne It won't let me accept any of your comments as answers.... throw an answer up fo rthe points
Did you get this resolved?
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
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