asked
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.
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! Alberto
15 People are following this question.
