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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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
DBAduck - Ben MillerPrincipal ConsultantCommented:
Did you change the Format Cell to be Text instead of General?  I believe that it would come across in the import.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.