Suggestion for Bulk Data Import

Question

We are working on a project which requires import data from Excel spreadsheet on daily basis. The data will be import from pre-define template and what we're thinking, first we will upload data in temporary table and perform cleaning operation (removing unnecessary information, add new rows in parent table and get their FK, etc.).

• I need to know is there any tool or utility available which can reduce our efforts.
• What is the best way to INSERT bulk record from different sources (mostly from Excel spreadsheets)?

Answer 1

SSIS is the way to go on this. If you've never built a package before, and you know your source files (read also: spreadsheets) are always going to be the same ones, what you can do is use SQL Server's Import/Export wizard. In SSMS right-click database and select Tasks > Import (or Export) Data...

This opens a wizard which walks you through the steps of selecting your source/destination files as well as destination tables (can create tables if they don't exist already). You'll have to map which columns go where but the wizard is pretty straight forward. When you're done it will ask you to run or save the package (or do both). Save the package. This will save your package in .dtsx format if you choose to save it to file system. Your other option is to save the package in SQL Server itself, which would then keep them in the msdb system database.

Once you've saved your package, you can create a SQL Agent job to run that package periodically (you specify the schedule) so that you can always load your tables using SSIS. If you'd like to learn more about SSIS, check out my company's free webinars (we cover the whole BI stack) at PragmaticWorks

Answer 2

Here is one example on how to import data from excel to SQL Server. One of the main problems is making sure you use Data Conversion component between Excel and SQL Server and do a conversion from NVARCHAR to VARCHAR as excel treats the data as NVARCHAR.

http://www.mssqltips.com/tip.asp?tip=1393

Answer 3

Use SQL Server Integration Services. It is that simple.

We can't show you how here: it's too general

Answer 4

I wouldn't discard anything from mr. gbn,Sankar Reddy,SQLChicken . But i would like to say that there is simple way to import excel sheet in SQL is through 'OPENROWSET' TSQL.

For Example to import user excel sheet in TestDB Database. Suppose that i have already created the [user] table in TestDB Database. Then the simple openrowset TSQL would be below mention like that

Use TestDB;
    Go
    select * INTO [User]
    from openrowset('MICROSOFT.ACE.OLEDB.12.0',
    'Excel 12.0; Database=C:\TestFolder\User.xlsx; HDR=yes; IMEX=1',
    'select * from [User$]');
    Go

Note : I am writing this TSQL in SQL Server 2012 environment. For Every SQL server openrowset OLEDB provider will be different. Please check the OLEDB link server->provider supported for that version of SQL server. In my case it is ACE.OLEDB.12.0. Before import make sure that Allow inprocess is checked or not of that MICROSOFT.ACE.OLEDB.12.0 provider. It should be checked like that

Note:- It's Tested in production environment. But in your case without to test in test environment. Don't directly run in Production Environment.