CODEWITHSUNDEEP
sqlsql-server-2005sql-server-2008ssis
Vikrant More
Vikrant More

Reputation: 5442

Which one is good way to Import Excel to Database?

Hi i am using SQL Server 2008.

How can I import an Excel file into the database, which is the easiest way and simple to do?

  1. OpenRowSet
  2. BulkCopy
  3. Linked Servers
  4. SSIS

I have the above options to Import Excel to Database.

Upvotes: 0

Views: 802

Answers (2)

Upendra Chaudhari
Upendra Chaudhari

Reputation: 6543

In my opinion SSIS wizard is best way to import excel data where you get row and column wise whole view of table data which will be inserted and also specify column names and contraints and parse data using query.

UPDATE :

enter image description here

Upvotes: 2

argyle
argyle

Reputation: 103

If the data in your excel file does not require any processing to match your database table then I recommend you save your excel file as a csv and use a combination of BULK INSERT and the BCP.exe program.

To use BULK INSERT you will need a format file which defines how your datafile matches up to your database table. You can write this by hand to match the existing database table or you can use the following command to generate the format file you need:

bcp [ServerName].[SchemaName].[TableName] format nul -c -f [FormatFileOutputName].fmt -S[ServerHostName] -U[DbUserName] -P[DbUserPassword]

Now you will have 2 files:

  • DatafileName.csv
  • FormatFileName.fmt.

Use BULK INSERT within Sql Server to insert your data.

Note: If the columns in your datafile are in a different order than your database table then you can simply edit the generated format file to have them map correctly.

Upvotes: 2

Related Questions