8

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)?
Improve this question
1
  • The application we're going to create is web based application where user have limited control over SQL server. User will upload the file through browser and rest of the operation will be done at server side. – kodvavi Jun 1 '11 at 21:28
9

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

Improve this answer
4
  • Actually we're creating web based application so we won't allow user to access database interface directly. Our application first import the file and then we do rest of the operation through our application. – kodvavi Jun 1 '11 at 21:26
  • 1
    @kodvavi; you can invoke SSIS packages via .net code... – gbn Jun 2 '11 at 8:35
  • 1
    @kodvavi, I'm not sure what you mean by that statement. Web apps typically don't have the job of initiating a data load anyways so why would that matter? The ETL portion of database and web application interface are two different things. – SQLChicken Jun 6 '11 at 2:35
  • I know that many people initiate SSIS packages from the web but to me it seems to be a bad idea, given how much can go wrong with excel sources. – Nick.McDermaid May 8 '17 at 13:05
5

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

Improve this answer
1
  • nvarchar is fine with us coz our database going to support multilingual attributes. I am reviewing the topis. thanx – kodvavi Jun 1 '11 at 21:25
4

Use SQL Server Integration Services. It is that simple.

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

Improve this answer
0

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 enter image description here

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

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.