Retrieving data from excel

[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

 

[sloan]

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.

 

[indrajith_varma1]

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 -

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

 

[Robson Siqueira]

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

 

[sloan]

http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!148.entry


Create / New Object / DataSet

Add a Table

Add some columns.

If you're in 2.0, remove/delete the table adapter that is created
"below/under" the dataset (table) definition.