How can I export numbers from excel to sql server as text without truncation of leading zeros

I have an Excel spreadsheet with a column containing text strings of the form dddd.dddd which are not actually numbers but look like decimal numbers.  I want to import them to SQL Server 2008 keeping any leading zeros.  How can I most easily accomplish this?
yogivictorAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
DBAduck - Ben MillerConnect With a Mentor Principal ConsultantCommented:
Did you change the Format Cell to be Text instead of General?  I believe that it would come across in the import.
0
 
TracyVBA DeveloperCommented:
Assuming your value is in A1, try this:
=Text(A1,"0000.0000")
0
 
yogivictorAuthor Commented:
I have hundreds of rows and would like to avoid editing each one ...
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jaan33Commented:
import the column as a varchar/text datatype instead of numeric column

also, once in sql server you can use the replicate function to re-pad your zeros if applicable
0
 
yogivictorAuthor Commented:
I tried importing to Sql Server as nvarchar but it still showed the Excel spread sheet source column as containing floating point numbers float and truncated leading zeros.  I can't automatically replicate as I do not always have the same number of leading zeros.  I can mot change the source
0
 
TyugCommented:
As bromee suggested, if you do that in a column next to your A1, you can just double click the bottom right corner of the cell, it'll be extended down to your last row automatically (assuming excel 2003) Shouldn't take too long.
0
 
yogivictorAuthor Commented:
Creating a duplicate column as Tyug suggests doesn't solve my problem.  All that accomplishes is copying the value in Excel to a new Excel column.  I need to import it (with leading zeros) to sql server as a text field with any leading zeros.
0
 
yogivictorAuthor Commented:
I did try selecting the entire column and changing the format to text.  However, it still came acreoss as numbers with the leading zeros stripped.
0
 
yogivictorAuthor Commented:
The original Excel table came in the older .xls form with protected macros.  When I saved it without macros as a .xlsx file and then selected the entire column and saved it as text as suggested by dbaduck I was able to properly import it.  So, I am accepting that solution.

Thanks to all.
0
 
yogivictorAuthor Commented:
Thanks again!
0
All Courses

From novice to tech pro — start learning today.