TSQL Excel Import: default, or ordinal sheet?

• R. van Laake

  Newbie

  Points: 0

  More actions

  July 26, 2008 at 4:10 am

  #190216

  Hi everyone,

  I am importing data from Excel to SQL2005 with this query:

  EXEC('SELECT * INTO [IMPORTTABLE] FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;HDR=YES;Database=' + @ExcelFileName + ''', ' + @ExcelSheetName + ')')

  Question: is it possible to do this without giving a sheetname? The query should then import from a default sheet. Or, would it be possible to give an ordinal number (for example: "2" -> the second sheet)

  I could use this because my customers have a different sheet every day (or even hour) they have to import, and the sheetnames are always different and almost never the default "Sheet1$".

  Thanks,

  Raymond

• Steve Jones - SSC Editor

  Newbie

  Points: 0

  More actions

  July 26, 2008 at 10:12 am

  #848785

  I think you could possibly ignore the sheet name and it would take the first one, but I'm not sure.

  Instead, I'd require the clients to specifically set a name. It's not hard and it helps ensure that the correct sheet is imported. Or have them enter the sheet name somewhere, store it in a table, and read it dynamically from the table.