none
how to import data from excel intro sql db using ssis RRS feed

  • Question

  • Hi,

    I have a little problem ...

    How do I create a procedure within a cube so that I import data from an excel file in that cube? I tried OPENROWSET and OPENQUERRY but with no success. OS on which it is SQL Server Win Server 2008 x64 ...

    Does anyone have any idea how I could do that?

    Thanks a lot! :)
    Wednesday, May 18, 2011 11:38 AM

Answers

All replies

  • Hello,

    The following link tells you how to create a SSIS package to import xls files into SQL Server: http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205

    There is a problem when trying to import xlsx files though. Seems MS doesn't support this in SSIS 2008 "yet".

    You can import the xlsx files with a workaround: http://dataintegrity.wordpress.com/2009/10/16/xlsx/

     

     

    • Proposed as answer by LordOfFlies Wednesday, May 18, 2011 12:11 PM
    Wednesday, May 18, 2011 12:11 PM
  • Please check this http://consultingblogs.emc.com/jamiethomson/archive/2005/05/30/SSIS_3A00_-Enumerating-files-in-a-Foreach-loop.aspx

     


    http://uk.linkedin.com/in/ramjaddu
    Wednesday, May 18, 2011 12:23 PM
  • thanks but i was aiming for coding not clicking in ssis :D
    Wednesday, May 18, 2011 12:59 PM
  • here's what i've got so far...

    but this here snippet breaks my string into lines and not columns... :(

     

    declare @Delimiter varchar(1)
    declare @String varchar(8000)
    declare @idx int   
    declare @slice varchar(8000)   
      
    select @idx = 1   
    set @string = 'Mihai, Ion, Popescu, Nicolaescu'
    Set @delimiter = ','
    -- if len(@String)<1 or @String is null return   
      
     while @idx!= 0   
     begin   
      set @idx = charindex(@Delimiter,@String)   
      if @idx!=0   
       set @slice = left(@String,@idx - 1)   
      else   
       set @slice = @String   
      select @slice
    --  if(len(@slice)>0)
    --   insert into @temptable(Items) values(@slice)   
    
      set @String = right(@String,len(@String) - @idx)   
      if len(@String) = 0 break   
     end
    

    Wednesday, May 18, 2011 1:10 PM
  • why don't you use bulk insert check here

    http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

     


    http://uk.linkedin.com/in/ramjaddu
    Wednesday, May 18, 2011 1:18 PM
  • one of the steps can be bringing the data to  staging table something like http://plexussql.blogspot.com/2010/04/looping-through-excel-files-and-sheets.html

     I can even email you the package if you like (SNikkhah@live.ca)


    Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Wednesday, May 18, 2011 2:35 PM
  • tooked me a while to get it done...but it's done :) it's working :) thanks :)

    can you send me the dstx file also? this is my email adress: bogdan.cazacu@muller.com.ro (or try this on the the 1st fails: cazacu.bogdan@gmail.com.ro) i really wanna compare it to mine to see the differences... thank :)

    ps: sorry for the delay :D
    Monday, June 20, 2011 8:33 AM
  • I just emailed you a sample

    good luck


    Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Monday, June 20, 2011 8:14 PM