• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 815
  • Last Modified:

Import Data from Excel to Sql Server

Hi all
Is any one help me the query to use uing bcp utilty to Import Data from Excel to Sql Server.


1 Solution
Hi Arun,

Try the following steps in Sql Server.I hope it will help you...

1. In Sql Server Enterprise Mangaer, go to Data Transformation Services, right click on Local Packages and select  New Package from the menu.

2. On the left you will see Connections and Tasks. Form Connections, click on the Excel Icon.
A wizard will pop up and let you browse to the Excel file. If the file data types give you trouble,
save the file as .CSV and choose the text connection instead.

3. Now you must tell the DTS package where you are going to PUT the data, so choose the first icon that looks like a server.
   Here you will tell it (local) if it is for a database on the same SQL Server, otherwise, choose the server and pick the database in the drop down.

4. Finally you must create a task that will move your data FROM the spreadsheet TO your database table.This is done by selecting the TRANSFORM DATA task.
  This takes three clicks
  1) select the task
  2) click on the SOURCE (excel icon)
  3) click on the DESTINATION (server icon).

  You should now have three things in your package: Excel icon, server icon and a "pipe" between them.
  To configure this "pipe" double click on it and dialog box pops up for you to specify the source
 (which should already be the excel file, but if your source was another database here is where you pick the table) the destination which is a table in your database and transformation which is where you map the fields from the file to the table.

5. Save Your PACKAGE.

6. You can Execute the whole package (right mouse click any white space)
   or execute one step (right mouse click the transform task you created)

If you want, you can save it as an external file .dts and execute it from a stored procedure.
Or you can shedule it to run every day (save and close the package, find it on the list and use right mouse click Schedule Package).

Atul Khare
arun_sabatAuthor Commented:
Hi Atul

Thank U for giving me a nice explanation. And it is working fine also.

But my aim is some thing defferent I want to create a procedure which may take some parameters such as file name,and table name Make transform. This i got using bcp.

This bcp utility works fine if i will take the columns of csv file with out null. If I am taking a column with null and the table having thousands of data It is not working

earlier it was showing me a error named Data truncation.

Later I got a solution for this as bcp format file. Now it is not showing error. But problem is it is not copying all the rows. This is bcz of the nUll columns what i am taking It is filled by other column datas.

I a not getting what to do

I am Sending here the format file plz check it if so.

1  SQLCHAR  0  30 ","           1     Segment                       SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  30 ","           2      Segment_Desc               SQL_Latin1_General_Cp437_BIN
3  SQLCHAR  0  30 ","           3      Grp                               SQL_Latin1_General_Cp437_BIN
4  SQLCHAR  0  30 ","           4      Product_line_cd                    SQL_Latin1_General_Cp437_BIN
5  SQLCHAR  0  30  ","          5      Product_line_desc                  SQL_Latin1_General_Cp437_BIN
6  SQLCHAR  0  30  ","          6      Country                               SQL_Latin1_General_Cp437_BIN
7  SQLCHAR  0  30  "\r\n"      7      Sold_To_Name                  SQL_Latin1_General_Cp437_BIN

Using above bcp format I created two csv files with some middle column null and mke another one last column null. Both are not working properly.

And If u can plz check if the column contains both null and Data fiels how it works.

Thank U

Arun  Sabat


Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now