OpenDataSource import with leading zeroes in Excel doc fields
-
I'm writing a stored procedure that uses OpenDataSource to insert an excel file into a table. The Excel file contains zip codes, some of which have leading zeros. In Excel, the leading zeros appear, but upon importing the into the DB, the leading zeros disappear, leaving me with 4-digit zip codes. I am inserting the zip codes into a field defined as varchar(9).
I'm wondering if there's any way to tell OpenDataSource to retain those leading zeros when doing the import.
Any help would be appreciated. Thanks.
-
Just checking - are the zip codes defined as text or numeric within Excel? If they are numeric, that is your problem (regardless of how they are displayed in Excel).
Alternatively, why don't you just add the leading zeroes back in as part of the import?
(Please excuse any lack of knowledge of zip code formats in the above
) -
I'm sorry it's taken so long to get back to you. I got sidetracked on other things.
This is not an issue for me any longer (I was able to take an alternative route, due to a change in business rules), but I wanted to get back to you regarding your suggestions.
The zip code field is defined as "Special: Zipcode" in Excel, which makes sense. The Excel file is from an outside vendor, and so I have little control over how the document is formatted--unless I created a vbscript which would re-define the zipcode columns.
However, I wrote my own test excel file with zip code fields defined as "Special: ZipCode," and it worked perfectly.
I'm not sure why one file works but not the other.
Also, I could just add the leading zeroes back in on import...an excellent suggestion.
As I said, this is not an immediate issue for me right now, but I wanted to get back to you and thank you for your input. Moreover, I never know when this info might come in handy again!
Thanks.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply