ktpoitm
asked on
OleDB Read from Excel with Cells > 256 Chars?
Hi,
i am trying to read Data from an Excel spreadsheet. My code works great, but it can not read cells with more than 256 chars.
Is there a solution for it?
i am trying to read Data from an Excel spreadsheet. My code works great, but it can not read cells with more than 256 chars.
Is there a solution for it?
OleDbConnection DBConnection;
OleDbCommand DBCommand;
DBConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + @"c:/temp/lpa.xls" + ";"
+ "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"");
string SQLString = @"Select [F1],[F2],[F3],[F4],[F5],[F23] FROM [Sheet1$Print_Area]";
DBCommand = new OleDbCommand(SQLString, DBConnection);
try
{
DBConnection.Open();
DataTable ExcelData = new DataTable();
ExcelData.Columns.Add("Item", typeof(int));
ExcelData.Columns.Add("Description", typeof(string));
ExcelData.Columns.Add("Station", typeof(string));
ExcelData.Columns.Add("Location", typeof(string));
ExcelData.Columns.Add("SampleSize", typeof(string));
ExcelData.Columns.Add("ReactionPlan", typeof(string));
DBReader = DBCommand.ExecuteReader();
int num;
while(DBReader.Read())
{
bool isNum = int.TryParse(DBReader[0].ToString(), out num);
if (isNum)
{
ExcelData.Rows.Add(num, valid(DBReader, 1), valid(DBReader, 2), valid(DBReader, 3), valid(DBReader, 4), valid(DBReader, 5));
}
DB.Connection.Close();
}
}
catch (System.Exception ex)
{
DBConnection.Close();
if (DBReader != null)
{
DBReader.Close();
}
MessageBox.Show(ex.Message.ToString());
}
finally
{
MessageBox.Show("Data Inserted");
}
Try using ODBC-driver instead of Oledb
ASKER
The ODBC-driver did not work since it has a bug with the First Column Name.
I found a registry key that allowed me to change the row scan value from 8 to 30. It sees the Cells as Memos now and reads all the chars.
Thanks anyways for you help.
I found a registry key that allowed me to change the row scan value from 8 to 30. It sees the Cells as Memos now and reads all the chars.
Thanks anyways for you help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.