Import Data from Excel to  Sql Server

Posted on 2004-11-19
Last Modified: 2008-03-04
Hi all
Is any one help me the query to use uing bcp utilty to Import Data from Excel to Sql Server.


Question by:arun_sabat
    LVL 1

    Accepted 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

    Author Comment

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now