c# reading data from .xls file results in missing data

[ebenl555]

i am writing code to import data from an .xls file into a db using an
oledbadapter to fill a datatable. I can access most of the values
easily using this method. i do however have problems with a couple of
lines in the file. when i access these rows, they do not contain any
data. when i look at the file, the only difference that i can see is
that these problematic rows have merged cells. i have played around
with the merging and unmerging and it does not seem to have made a
problem.



when the first column is merged with x amount of columns, i can only
see the values for the columns following the first. anybody have an
idea why this might be happening?



the code for filling the data table is below



// gets the sheet name from the web.config file

string sheet2 =
System.Configuration.ConfigurationManager.AppSettings["SMARTDocSheet2"].ToString();

// Set up a new connection for the Excel file which was uploaded (will
be read like a table)

System.Data.OleDb.OleDbConnection lConn = new OleDbConnection();

lConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" + filePath + "; Extended Properties=\"Excel 8.0;HDR=No\";";

try

{

lConn.Open();

OleDbCommand command = new OleDbCommand("SELECT * FROM [" + sheet2 +
"$]", lConn);

// read the first sheet as a new table

OleDbDataAdapter daAllData = new OleDbDataAdapter(command);

DataTable table1 = new DataTable();

daAllData.Fill(table1);

}

 

[ebenl555]

i am writing code to import data from an .xls file into a db using an
oledbadapter to fill a datatable. I can access most of the values
easily using this method. i do however have problems with a couple of
lines in the file. when i access these rows, they do not contain any
data. when i look at the file, the only difference that i can see is
that these problematic rows have merged cells. i have played around
with the merging and unmerging and it does not seem to have made a
problem.

when the first column is merged with x amount of columns, i can only
see the values for the columns following the first. anybody have an
idea why this might be happening?

the code for filling the data table is below

// gets the sheet name from the web.config file

string sheet2 =
System.Configuration.ConfigurationManager.AppSettings["SMARTDocSheet2"].ToS­tring();

// Set up a new connection for the Excel file which was uploaded (will
be read like a table)

System.Data.OleDb.OleDbConnection lConn = new OleDbConnection();

lConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" + filePath + "; Extended Properties=\"Excel 8.0;HDR=No\";";

try

{

lConn.Open();

OleDbCommand command = new OleDbCommand("SELECT * FROM [" + sheet2 +
"$]", lConn);

// read the first sheet as a new table

OleDbDataAdapter daAllData = new OleDbDataAdapter(command);

DataTable table1 = new DataTable();

daAllData.Fill(table1);



}- Hide quoted text -

- Show quoted text -

I managed to solve my problem by modifiying the connection string to
include the IMEX property and set the value to 1. I am not yet sure
what exactly this does, but it has solve my problem. Will investigate
when i have the time.