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
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.
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
answered Jan 20, 2014 at 09:17 PM