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.OL EDB.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(sConnectio nString);
// 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(objDatase t1, "XLData");
// Bind data to DataGrid control.
DataGrid1.DataSource = objDataset1.Tables[0].Defa ultView;
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?
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.OL
"Data Source=C:\\exceltest.xls" + ";" +
"Extended Properties=Excel 8.0;";
// Create connection object by using the preceding connection string.
OleDbConnection objConn = new OleDbConnection(sConnectio
// 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(objDatase
// Bind data to DataGrid control.
DataGrid1.DataSource = objDataset1.Tables[0].Defa
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?
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
> "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
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
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.
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?
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.
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;Ex tended Properties="Excel 8.0;HDR=Yes;IMEX=1"
Data Source=C:\Temp\Test.xls;Ex
ASKER
Does it work with all different datatypes in the columns?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
"Data Source=C:\\exceltest.xls;"
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.O LEDB.4.0;D ata Source=C:\1199sep.xls;Exte nded Properties=Excel 8.0;IMEX=1";
but this does work:
string strConn = @"Provider=Microsoft.Jet.O LEDB.4.0;D ata Source=C:\1199sep.xls;Exte nded Properties=Excel 8.0;";
Anye clue?
Thomas
string strConn = @"Provider=Microsoft.Jet.O
but this does work:
string strConn = @"Provider=Microsoft.Jet.O
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
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