Retrieving data from excel

  • Thread starter Thread starter indrajith_varma1
  • Start date Start date
I

indrajith_varma1

Hi
I am working on an application to retrieve data from excel and insert
into SQL Server database. Yesterday, I faced a strange problem. In a
column in the excel, if 1st 8 rows are number, then as soon as an
alphanumeric or string value is encountered, the string becomes null
and put into the data set. IMEX property of the excel connection
object is set as 1. Is there any solution in the .NET code to fix
this issue?

In anticipation of an immediate response

Indrajit
 
Did you try making a (strongly typed) dataset? and filling it?

The "auto create me a dataset" is probably going to use the datatypes of the
first row it encounters. (Guessing here, but that's what I would think).

Create a strong dataset, and make the column a string.
 
Did you try making a (strongly typed) dataset? and filling it?

The "auto create me a dataset" is probably going to use the datatypes of the
first row it encounters. (Guessing here, but that's what I would think).

Create a strong dataset, and make the column a string.








- Show quoted text -
Click to expand...

thanks a lot for the response. By the way, how do we create a strong
dataset & make the column string..technically.
 
Dude,

What method are you using to read the data? I've been using OleDbConnection quite successfuly here and having the same scenario as yours. Just as a FYI, a piece of code that does that:

using (OleDbConnection cn = new OleDbConnection(cnnStr))
{
try
{
cn.Open();
foreach (string sheetName in sheetNames)
{
string query = "SELECT * FROM [" + sheetName + "$]";
using (OleDbDataAdapter ad = new OleDbDataAdapter(query, cn))
{
if (ad.Fill(ds) > 0)
ds.Tables[ds.Tables.Count - 1].TableName = sheetName;
}
}
}
finally
{
cn.Close();
}
}

In my cnnStr variable I use this:

string cnnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2""";

And then I concatenate the data source name based on the Excel file I want to read.

Regards,

Robson Siqueira
Enterprise Architect
 
Back
Top