Zip Codes in XLS from ADO.NET

[Guest]

Hi,

I have an XLS that I am looking to open in a web form and grab it's information into a Dataset. I have been able to do so but am having trouble with Zip Codes. When the Zip Code column in the XLS is formatted as "Special--Zip Code" the ADO.NET provider interprets it as a floating point number for some reason (so the zip code 07039 is read as 7039.0). This occurs both with SQL Server 2000's DTS Import Wizard and ADO.NET from a web form. ADO.NET reads the zip code as a floating point number and not as a string regardless of the IMEX value in the connection string (0, 1, or 2).

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\XLS\People.xls;Extended Properties='Excel 8.0;IMEX=1'

How do I tell ADO.NET to treat that column as a string and not a float? I am not able to change the XLS since I am building an automatic XLS parser. Any suggestions would be appreciated! Thanks!

 

[Mark Rae]

How do I tell ADO.NET to treat that column as a string and not a float?

What are you doing with the data once you've read in each record? Are you
displaying it on a web page? If so, can't you just use the .ToString()
function to format it the way you want e.g. .ToString("00000") or something
similar.

 

[Guest]

Mark,

Thanks for the reply. You have always been so helpful!

I am storing the zip code along with other data from the XLS in the SQL Server 2000 database. Basically I am building a membership database import feature and have to insert/update member information from the XLS. I also display some of the information on a confirmation web page as a preview of the imported data. I am not able to use "ToString("00000")" because

1) some of the records have the zip+4 format
2) if the zip is 11230, ADO.NET reads it as 1123.0 and the ToString("00000") would give 01123 which is the wrong zip code

It seems as though the problem must be addressed somewhere in the connection string or the way ADO.NET reads the data. Once ADO.NET has parsed the zip code column into a float it's too late (too much information is lost). I'm really surprised that such a common XLS data type is misread by default in ADO.NET and would assume that there is a work-around but I'm just not sure what that is. Do you have any suggestions? I really appreciate it!

 

[Mark Rae]

Mark,

2) if the zip is 11230, ADO.NET reads it as 1123.0 and the

ToString("00000") would give 01123 which is the wrong zip code

Ah - that's something more fundamental, then. If 11230 is being read as
1123.0, not only is it be parsed as the wrong datatype, it's value is being
divided by 10. Under the same set of circumstances, would 00001 be read as
0.1?

You mention that this happens both through DTS and ADO.NET on a web page.
When you're accessing the data on a web age via ADO.NET, what SQL query
string are you using?

Mark

 

[Mark Rae]

I am totally embarrassed. You are right. It doesn't divide by ten or anything so your suggestion
of .ToString("00000") works perfectly, even though ADO.NET is interpreting the zip code as the
wrong data type. Thank you so much!

Don't mention it. Also, if sometimes you have "12345" and other times
"12345-6789", you might want to apply different formatting in each case, so
you could either interrogate the length of the string, or look for the
presence of the hyphen character...

 

[Mark Rae]

That actually raises another question I had. In the XLS I have mostly 5

digit zip codes but some zip+4 codes.

I would like to be able to handle the zip+4 entries but for some reason

ADO.NET is reading them as System.DBNull.

Value instead of a double or string so I have no way to get to the data.

Do you know how to get it to read these values

without reporting them as null values?

I would say, but I'm not an expert in this area, that ADO.NET has already
"worked out" what it thinks the data type of the field in question is from
the first few records and, in this instance, has decided that it's numeric.
Then, when it sees a value which isn't numeric, it's returning it as a
DbNull.

Are you able to modify your SQL query string to convert the datatype of this
field explicitly to a text field? E.g. if you're querying SQL, you could
write something like:

SELECT CONVERT(varchar(10), <fieldname>) AS <fieldname> FROM
plot_LoanProducts

 

[Steven Cheng[MSFT]]

Hi Mark,

As for the problem you encountere, it is because the OLEDB provider haven't
any buildin support for explicitly specifying datatype for the field. As
for the xls file, it will scan the first serveral rows(by default is 8) and
determine the column's type. So if there are any different style datas in
the sequential rows, it will be changed to null.

However, we can try setting the column's cell format in excel file. For
example, set the Zip_Code columns's
cell format as "text". I've done a test and it seems work. And here is a
tech article which has mentioned this:

#Excel Inserts Null Values
http://www.sqldts.com/default.aspx?254

In addition ,here is another kb article detailed discuss data
manipulication with excel workbook

#How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook
With Visual Basic .NET
http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934

Hope also helps. Thanks.


Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Get Preview at ASP.NET whidbey
http://msdn.microsoft.com/asp.net/whidbey/default.aspx

 

[Mark Rae]

However, we can try setting the column's cell format in excel file.

That was my first thought too but, as the OP said he wasn't able to modify
the Excel file, I didn't suggest it...