SQL Server Performance

Bulk Insert from Excel

Discussion in 'SQL Server 2005 General Developer Questions' started by rogilvie, Apr 23, 2007.

  1. rogilvie New Member

    Hi,

    I'm having a slightly annoying problem with importing data from Excel in SQl. My data is in columns A and B of Sheet1 of an Excel spreadsheet. The code I have come up with is:

    bulk insert [db].[user].table_t
    from '\addressexcel.xls'
    with
    (
    datafiletype = 'char',
    firstrow = 2,
    fieldterminator = ' ',
    rowterminator = '
    '
    )

    This works fine for an equivalent text file, but I don't want to have to export from Excel to the text file and then import to SQL. I'm fairly sure that I'll have to do something with altering the delimiters. Both columns contain text data.

    Thanks
  2. satya Moderator

    http://www.sqlteam.com/item.asp?ItemID=3207

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  3. rogilvie New Member

    Thanks, but I can already do the process fine for a txt file, just not an xls.
  4. satya Moderator

    Are you getting any error in this case?


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  5. rogilvie New Member

    not syntax wise. I am getting (0 rows affected) rather than the usual (770 rows affected) that the text version creates.
  6. Adriaan New Member

    An Excel sheet is not a flat text file, so try OPENQUERY, OPENROWSET or OPENDATASOURCE instead.

Share This Page