Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 6 down vote favorite
1

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)?
share|improve this question
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

3 Answers

up vote 5 down vote

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 conver the whole BI stack) at http://bit.ly/pwwebinars

share|improve this answer
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
@kodvavi; you can invoke SSIS packages via .net code... – gbn Jun 2 '11 at 8:35
@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
up vote 4 down vote

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

share|improve this answer
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
up vote 3 down vote

Use SQL Server Integration Services. It is that simple.

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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