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! 7 1394
"Solel Software" <So***********@newsgroup.nospam> wrote in message
news:F9**********************************@microsof t.com... 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.
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!
--
Sincerely,
Mark Fox
"Mark Rae" wrote: "Solel Software" <So***********@newsgroup.nospam> wrote in message news:F9**********************************@microsof t.com...
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.
"Solel Software" <So***********@newsgroup.nospam> wrote in message
news:43**********************************@microsof t.com...
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
"Solel Software" <So***********@newsgroup.nospam> wrote in message
news:F7**********************************@microsof t.com... I am totally embarrassed. You are right. It doesn't divide by ten or
anything so your suggestionof .ToString("00000") works perfectly, even though ADO.NET is interpreting
the zip code as thewrong 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...
"Solel Software" <So***********@newsgroup.nospam> wrote in message
news:31**********************************@microsof t.com... 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
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...b;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
"Steven Cheng[MSFT]" <v-******@online.microsoft.com> wrote in message
news:83**************@cpmsftngxa06.phx.gbl... 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... This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Michael Bendzick |
last post by:
Is there a simple way in python to read a keyboard scan code? I'm
working on a shell script that interfaces with a proprietary keyboard
device (extra buttons) and need to be able to distinguish...
|
by: Yong Wang |
last post by:
Hi, All:
We have a network management system written in C++, MysQL, and Hp
SNMP. It works in Solaris command line. When I wrote a similar
python codes which call compiled C++ and mysql codes in...
|
by: Maurice LING |
last post by:
Hi,
I remembered reading a MSc thesis about compiling Perl to Java bytecodes
(as in java class files). At least, it seems that someone had compiled
scheme to java class files quite successfully....
|
by: Shane Groff |
last post by:
I know this is a recurring discussion (I've spent the last 3 days
reading through threads on the topic), but I feel compelled to start
it up again.
After reading through the existing threads, I...
|
by: Josh Mcfarlane |
last post by:
I keep trying to get myself out of the return-code mindset, but it
doesn't seem to work. They are suppose to get rid of if-then statements
of return codes, but you still have to do an if statement...
| |
by: aman |
last post by:
i know how to get scan codes and ascii codes for special keys.
i can get ascii codes of alphabetic keys.
how does one get scan codes of alphabetic keys??
|
by: Steve Litvack |
last post by:
Hello,
I have built an XMLDocument object instance and I get the following string
when I examine the InnerXml property:
<?xml version=\"1.0\"?><ROOT><UserData UserID=\"2282\"><Tag1...
|
by: c# beginner |
last post by:
we are trying to standardize return codes across our .NET
applications (that are soon to be developed.) What is the
best practice for standardizing return codes? I know of
only the following...
|
by: PerlPhi |
last post by:
hi! i have a Perl code in here that when ran the program accepts any Perl codes from the user input (<STDIN>, of course use no syntax errors), then after breaking the multiline input, the inputs will...
|
by: =?GB2312?B?17/HvyBaaHVvLCBRaWFuZw==?= |
last post by:
Hi,
I would like to have someone comments on what's the best practice
defining error codes in C.
Here's what I think:
solution A:
using enum
pros: type safe. better for debug (some debugger...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |