Import Data from Excel to SQL 2005 table

Posted on 2008-11-06
Last Modified: 2012-05-05
Hi Experts,

Based on the examples I have got through EE website, I am importing Data from Excel to SQL 2005 table. My data in excel sheet is like folowing and getting an error (please see the code part). Please let me know how to fix the error.

 A-100001-GA-00       7/5/06      25845      0      0      1605.00
1517.00      0.00      (88.00)      (88.00)

A-100021-GA-00            7/5/07      25847      0      0
1232.00      8484.00      0      (32.00)      (32.00)


CREATE TABLE #Temp (pnumber Varchar(150),pEffdate smalldatetime,ID int,NBPR float,NBPR1 float,

ENDPR float, ENDPR1 float, diff1 float,diff2 float,FinalDiff float)


   FROM 'C:\MyReports\book1.xls'



Server: Msg 4864, Level 16, State 1, Line 1

Bulk insert data conversion error (type mismatch) for row 1, column 2 (pEffdate).

Server: Msg 4864, Level 16, State 1, Line 1

Bulk insert data conversion error (type mismatch) for row 2, column 2 (pEffdate).

Server: Msg 4863, Level 16, State 1, Line 1

Bulk insert data conversion error (truncation) for row 2, column 1 (pnumber).

Open in new window

Question by:Tpaul_10
    LVL 3

    Accepted Solution

    Sounds like the data in the spreadsheet doesn't match up with the data type of small date time and varchar. Look at the peffdate and pnumber columns in the spreadsheet using a data filter (that will show you unique values quickly) and see if you spot anything unusual.

    Have you tried using SSIS instead? I believe it comes free with SQL server standard edition and it allows you to easily convert values before you insert them.
    LVL 17

    Assisted Solution

    First, use SSIS - no doubts. You cannot import an Excel file using Bulk Insert, only csv files. But, if you have to use the Bulk Insert and you convert to csv then don't try to import directly into a specific data type column, bring it allin as either NTEXT or  NVARCHAR(255). Get it into the db and then manipulate into your correct data types.
    Here is some examples you might find useful if you cant use SSIS and it has to stay an .xls file.
    LVL 51

    Expert Comment

    by:Mark Wills
    No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

    I will leave the following recommendation for this question in the Cleanup topic area:
       Split: HoggZilla {http:#22901265} & dro_law {http:#22901265}

    Any objections should be posted here in the next 4 days. After that time, the question will be closed.

    EE Cleanup Volunteer

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    This video discusses moving either the default database or any database to a new volume.

    730 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

    19 Experts available now in Live!

    Get 1:1 Help Now