Impot data from EXCEL file

• SQLRO

  SSCrazy

  Points: 2923

  More actions

  January 26, 2006 at 8:44 am

  #67290

  Hi,

  I want to Import the data from an EXCEL file which is being uploaded  by a web application. Name of the file will be different every time. I have to use Stored procedure for acheiving this. I need to import using a column list because few columns are auto generated in the back end. I am too new to DTS and do not know whether it will work here. Is there any better way to do it?

   

   

  Thanks in advance

  Rohit

• Jonathan Stokes

  SSCrazy Eights

  Points: 9861

  More actions

  January 26, 2006 at 9:57 am

  #617234

  If:

  a)The excel spreadsheet always has same number of columns

  b)You want to import the same columns

  c)The excel tabname is always the same

  ...You can use a dynamic properties task to change the name of the source spreadsheet dynamically.

  Effectively this repoints the package to a different source WITHOUT losing the transformation mappings.

  If any of the above answer false, then dts is not the way to go unless you bring in the entire spreadsheet into a table then use an sp to write to the target.

  This effectively means creating a table with a matching column for the maximum number of columns in a spreadsheet, naming them F1, F2...etc

  ---

  ------------------------------
  The Users are always right - when I'm not wrong!

• Todd Painter-134230

  SSC Veteran

  Points: 207

  More actions

  January 27, 2006 at 2:57 pm

  #617583

  If you must do it from a stored procedure you can use the following syntax to select from the excel spreadsheet:

  insert into table

  select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

  'Excel 8.0;DATABASE=d:\excelfile.xls', 'Select * from [sheet1$]')

  It requires that the worksheet name (sheet1$) is always the same. If the name of the file itself is always changing and it's impossible to push back on the user community to follow a standard then you have at least two options.

  1. rename the spreadsheets in your directory one at a time to a common file name as you load them using xp_cmdshell and the ren command

  2. use xp_cmdshell and the dir command to get a list of excel files

  ex. insert into table1 --table1 should have one column 256 characters wide

       exec master..xp_cmdshell 'dir d:\*.xls'

  You can then use string functions like substring and charindex to get the name of any excel files in the directory

  You would then build a query string and replace "d:\excelfile.xls" in the query above with your result. Then run the query using sp_executesql

  I'd probably use option number 1 or push back on your users to name their files a common name.

• SQLRO

  SSCrazy

  Points: 2923

  More actions

  January 29, 2006 at 10:49 pm

  #617667

  Thanks a ton for your reply. The option of using OPENROWSET is best suited fot my application.

   

  Thanks a ton

   

  Regards

  Rohit