How to Import EXCEL file data toSQL server using BULK INSERT

• Sundaraguru S

  Mr or Mrs. 500

  Points: 518

  More actions

  January 28, 2009 at 7:34 am

  #127695

  Can anyone help for the following database scenario:

  I have Excel file contains data that needs to be inserted into tables using BULK INSERT

  Using the following syntax I can able to import the file (Pipe line deli meted file)

  BULK INSERT #temp

  FROM 'E:\FINAL\a.txt'

  WITH

  (

  FIELDTERMINATOR ='|',

  ROWTERMINATOR =''

  )

  GO --> This working is Successfully.

  I used the following query for importing the Excel file data into the table.

  BULK INSERT #temp

  FROM 'E:\FINAL\a.xls'

  WITH

  (

  FIELDTERMINATOR ='|',

  ROWTERMINATOR =''

  )

  GO

  I received the following error message.

  Msg 4864, Level 16, State 1, Line 1

  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (payer_counter).

  Msg 4832, Level 16, State 1, Line 1

  Bulk load: An unexpected end of file was encountered in the data file.

  Msg 7399, Level 16, State 1, Line 1

  The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

  Msg 7330, Level 16, State 2, Line 1

  Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

  How I can read Excel with the specified sheet name.????

  Thanks in advance. :unsure:

  Plat form:

  1. SQL Server 2005

  2. XLS file with more than 1 sheet (Created using MS Office 2003)

  ---

  Kind Regards,

  Sundaraguru S