I have an excel spreadsheet (.csv) format of list of zipcodes that I am trying to import to my custom table zipcodes.. I having issue in importing a zip code with leading zero. Can someone assist me on this? I'm using MSSQL server 2005 import/export wizard.
Thanks.
7  11915 
Make sure you're storing the zip codes in a string type field. Of course, there are no leading zeroes with numeric type fields, therefore I tend to think the Import/Export Wizard analyzed the data in the csv file and auto typed the zip field as a numeric type in the destination table.
Although truncating the leading zeroes is not the desired result, consider the storage, indexing, and querying implications of a CHAR(5) field versus an INT. The conversion truncates the leading zeroes, but does not loose any data that cannot be easily recovered through proper formatting.
But I need to see the leading zeroes in the table. For example, if I custom format the zip code as 08111 it should be 08111 also in SQL table.
ck9663 2,878
Recognized Expert Specialist
What "issue" are you having?
~~ CK
NeoPa 32,579
Recognized Expert Moderator MVP
I think you'll need to export the data as a string. This means to include quotes around it. Otherwise, data with only numeric characters in it is quite correctly interpreted as numeric. That's how it works.
I'm not certain that overriding that in SQL is impossible, but I strongly suggest that you work more naturally with your data where possible (IE. Store string data as strings rather than relying on any auto-conversions).
When I place the data onto an excel spreadsheet, the data looks lils 09999, but after importing to SQL 2005 tables the leading zero is ignored.
NeoPa 32,579
Recognized Expert Moderator MVP
It's not a good idea to use Excel to manipulate CSV files I'm afraid. It's very difficult to get it to stop making unfounded assumptions about your data. Numeric data which is enclosed in quotes is nevertheless treated as numeric data, even though the quotes indicate it should be treated as textual.
ck9663 2,878
Recognized Expert Specialist
True, Neo...
Bench, save your file as .CSV/.TXT and import that instead.
Good Luck!!!
~~ CK
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: yanir |
last post by:
Hi
I use reponse.contenttype = "application/vnd.ms-excel"
So the browser will show the data in excel format, but for
some fields I use leading zero's, which truncated by the
browser, at this content type.
If I concat a "'", or other none numeric char, the problem
is solved inconsistently!!!
But even this solution is problematic - is there any
solutions, explanation?
|
by: Myk |
last post by:
Hello All,
None of the solutions I have found in the archives seem to solve my
problem. I have a date column in my tables (stored as a char(10))
which I would like to append a leading zero to for those dates that
start with 9 or lower.
Any ideas?
Thanks,
|
by: Jeff Lowry |
last post by:
I'm pasing a zip code as a prameter to an Access stored procedure. In
Access the parameter is a text data type. It works for non-leading zero
zip codes but, apparently access (or ASP) is converting it to a value
first (dropping the zero) then sending that to my SP. Even if I use
cStr() to be sure the parameter is sent a string it still seems to drop
the leading zero. Any thoughts? Note: It needs to be a string for
canadian zip...
|
by: david |
last post by:
Hi,
I have 2 text boxes on an ASP form.
A user enters a Serial Number in TB1 such as 0105123456, presses tab to
move to TB2, TB2 then displays the value of TB1 after a calculation has
been done.
(Based on a Serial Number range).
i.e. a user enters 0105123456, TB2 then adds 'x' qty to this number
depending on how many serial numbers are required.
|
by: Joshua Ammann |
last post by:
Hello,
I'm trying to export a query containing contact information, including a
field. Some zip codes have one or two leading zeros, for example,
San Juan, PR (00927) and Springfield, MA (01104). When I export to a comma
separated value (.csv) file, the zip code for San Juan becomes "927" and for
Springfield becomes "1104". How can I prevent the leading zero(s) from being
trimmed in the exported .csv file?
Because the table contains...
| | |
by: GarryJones |
last post by:
I have code numbers in 2 fields from a table which correspond to month
and date.
(Month, Code number)
Field name = ml_mna
1
2
3
etc up to 12
(Data is entered without a leading zero)
|
by: FAQ server |
last post by:
-----------------------------------------------------------------------
FAQ Topic - How can I see in javascript if a web browser accepts cookies?
-----------------------------------------------------------------------
Writing a cookie, reading it back and checking if it's the same.
http://www.w3schools.com/js/js_cookies.asp
Additional Notes:
|
by: Andrew Poulos |
last post by:
In my limited testing with FF 2, IE 6 and Opera 9 when I divided a
positive integer, that is less than 100, by 100 I get a leading zero in
front of the decimal point.
For example 80/100 gives 0.8
This is what the server app is expecting a leading zero (it does indeed
fail without it). Do I need to do anything to ensure the leading zero is
there?
|
by: bobm2005 |
last post by:
Whatever format I try in Printf, an 'E' format number nearly always
has a leading non-zero:-
1.2345E7
-9.3456E8 etc.
Is it possible to force it (printf) always to have leading zero?
Thus, the above becomes:-
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| | |
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 last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
