Excel as a data source - missing data! 1

[JCruz063]

Hello All!

I have an excel file which I use as a data source for a dataset of mine. My C# code is as follows:

[green]// Variable declarations ommitted for clarity[/green]
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + this.RundownFile + ";" +
"Extended Properties=Excel 8.0;";

OleDbConnection cn = new OleDbConnection(connStr);
cn.Open();

OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + SHEET_NAME + "]", cn);

OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();				
ad.Fill(ds, SHEET_NAME);

The code above works fine and I'm able to load the dataset with the data of the excel file.

The problem is this:
In one of the columns of the excel file, some cells contain strings, while other cells contain numbers. When I'm looping through the rows of the table in the dataset to get the data for this column, the cells containing text return null. I have the following code:

[green]// Variable declarations ommitted for clarity[/green]
DataTable data = ds.Tables[SHEET_NAME];
for (rowIdx=0; rowIdx<totalRows; rowIdx++)
{
   [green]// for the column with the problem, this 
   // returns a proper value for numbers but
   // it returns an empty string for the cells
   // that contain strings[/green]
   val = data.Rows[rowIdx][colIdx].ToString()
}

As I mentioned before, I only get data for the cells that contain numbers. For those that have text, data.Rows[rowIdx][colIdx].ToString() is returning an empty string. Can anyone one suggest what's happening?

The data in the column in question will always be either a number by itself, a number followed by an asterisk (*) and the letter B, or nothing at all. Some of the values in the file are:

2260
2261*B
[blank]
[blank]
2283
2284*B

I manually edited the file and removed the *B from some of these numbers, and guess what, I get the data. Put the *B back again... no data! Can anyone please shed some light?

Thanks!

JC

_________________________________________________
To get the best response to a question, read faq222-2244.

 

[JCruz063]

Anyone ???

_________________________________________________
To get the best response to a question, read faq222-2244.

 

[obislavu]

As a table in a database, all data in a column has the same type.
The type of column in the returned DataTable are retrieved from the first row and if the doesn't match for subsequent rows then NULL is returned.
Try to create a linked server to a .xls file from the MSSQL database and you will notice that the returned set contains NULL for the cell that doesn't match the type.
For example, let be the following PriceData named range in myfile.xls (linked with MYEXCEL server from a SQL database) :
Jan Feb Mar
1.00 200 300
2.00 abc 400
Select * from MYEXCEL...PriceData
will return:
Jan Feb Mar
1.00 200 300
2.00 NULL 400
The workaround is to have the same type of data in each column in the named range.
obislavu

 

[JCruz063]

Thanks obislavu,

The workaround is to have the same type of data in each column in the named range.

I'm not the one creating the file and therefore I can't restrict the type of data that goes in it. And, to make things worse, this column requires that the data be both strings and numbers. Is there any other workaround to this problem?

I modified the excel file and formatted the entire column as "Text" so that all its entries would be considered text even if they were numbers, but that didn't work.

One thing I can do is update the file before I load it into the dataset. I can, loop through each row and append a string to the data so that every row is considered as string. Then, when I read the data, I can just read the actual data and ignore that string. But I shouldn't have to do that! There should just be a way for me to read the data in a column as is. Isn't there any other workaround?

JC

_________________________________________________
To get the best response to a question, read faq222-2244.

 

[obislavu]

Workaround:
If a column has mixed data:
Format the column as Text and cells with
- integers - prefix all integers with an apostrophe.
- date - as is
- float - prefix with an apostrophe
If the column has the same data such as : int, float, datetime do nothing.
As I said, the symptom is the same when you query such .xls file from a SQL database using a linked server.
With the above workaround, there is no NULL values returned.
This should work also in your case because the both scenarios are the same.
obislavu