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  11859 
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.
What "issue" are you having?
~~ CK
NeoPa 32,556
Expert Mod 16PB
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,556
Expert Mod 16PB
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.
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...
|
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...
|
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...
|
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...
|
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...
|
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?...
|
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...
|
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?
...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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: 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...
|
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: 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...
|
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...
| |
