What is the best way to import excel data to sql server?

We can import excel data in sql server using many ways like - 1. Import/export wizard 2. SSIS 3. OpenRowSet

But common issue in all this is

  1. Excel by default decides its own data type and size for the columns. Many times it takes wrong value due to this

  2. Lot of issues are there for importing date, time, float values

  3. Excel decides datatype and size depends on first 8 Rows. Scenario does not change even if you set IMEX or by changing the registry settings. And if you have files with more than 200 columns and such 50 files then this is really headache.

  4. If in first few rows, data is unavailable then some times excel ignores the rest of data in columns

  5. We can not import more than 255 excel columns in 2008

Even if you choose any of above mentioned method to import issues almost remains same. One way is the to convert all the columns in text format apart from date columns. But task requires lot of hard work. Is there any alternative?

Conversion to CSV and then import is also fails if data is more or data is complex.

more ▼

asked Jan 20, 2014 at 11:23 AM in Default

avatar image

20 2 2 4

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The best way is designing a package in SSIS (Integration Services). With SSIS you have many transformation tools to control data types an data complexity.

More simple, if you are going to use CSV format, you can use BULK INSERT command with a format file wich contains metadata to set destination structure and data types.

Other tool similar to BULK INSERT is bcp (bulk copy program) tool, wich is a command promp tool and can also use a format file.

Here you can see how to use a format file to import data: http://technet.microsoft.com/en-us/library/ms178129.aspx

Good luck!


more ▼

answered Jan 20, 2014 at 09:17 PM

avatar image

221 3 5 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 20, 2014 at 11:23 AM

Seen: 2904 times

Last Updated: Jan 21, 2014 at 01:16 PM

Copyright 2018 Redgate Software. Privacy Policy