Sql Import wizard imports 1022868890 as 1.02287e+009

I'm trying to import some data from Excel to SQL Server, I have a TEXT column with some numbers in it, some of the numbers go over just fine, and some turn into scientific form.

Column in Excel is "text" type, column in the target sql table is varchar(255)

Here are some examples:

Excel [text] -> SQL Server [varchar(255)] 0313852230 -> 0313852230 1024869004 -> 1024869004 1022868890 -> 1.02287e+009 1022868899 -> 1.02287e+009 1022868907 -> 1022868907 1030869319 -> 1030869319 1106869726 -> 1106869726 SomeText -> SomeText

Please help!

SOLUTION: Formatting the column as [Number] with 10 leading zeros worked for me, since all my numbers are 10-digit numbers

Answers


You are certain both Excel columns are text and SQL column is VARCHAR?

I know SQL imports into float columns as exponential (which is the correct floating representation)


When importing to Access, I have found the only column type that works for a straight import of numbers of this type is Zip (Format->Cells->Special->Zip Code). This may also be true with SQL Server.


SOLUTION: Formatting the column as [Number] with 10 leading zeros worked for me, since all my numbers are 10-digit numbers


Another tip, I prefer to use the SQL Server 2000 tool "Import and Export Data" or "DTS Import/Export Wizard"

This one is way less strict compared to the SQL Server 2005 import tools. Quite often works for me to get data imported in my database.


Need Your Help

ActiveRecord: Mock has_many relation call

ruby-on-rails activerecord rspec mocking

I'm fairly new to testing, so I've been struggling with using the correct syntax, especially regarding mocks.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.