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.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.