0

I have a ton of excel files which I'm trying to import to SQL Server from the backend and then automate it using a batch file.

I know that we can use OPENROWSET inside a T-sql script and load the excel files. I'm also aware of using SQLCMD or BCP options. All of these will work for excel sheets which are straightforward grids.

However, the challenge is, I only need to load a specific region/range of excel cells from a sheet. For example: if the sheet has the below info, I need to only load the below columns:

    Date, Group1, Group2 and Group 3

until it hits the "Blank Row" and ignore everything below it.

              Date   Group1 Group2 Group 3
              Jan-13  25     26      27
              Jan 18  35     29      19
                             20      15          
          <empty row>          <empty row>

               Y/Y %  YTD %  Group %
               15      20     40

So, my question is: is it possible to implement this functionality using OPENROWSET in T-SQL? IF so, can you please point me to any links/example on how I can do this? I tried digging around a bit on the MSDN site but couldn't find any.

If this cannot be done in T-SQL, any ideas on how I could implement it from the backend?

Thanks in advance, Bee

Bee
  • 341
  • 1
  • 6
  • 17
  • Might be a duplicate... http://stackoverflow.com/questions/5271104/how-to-selectively-import-an-xls-into-an-sql-server-2008-table – Rob Stewart Jun 12 '15 at 22:03
  • I'd recommend SSIS. It's probably the best way to achieve what you want on a repeatable basis. Once you've created the package, you can put the package on a schedule within sql server (sql server agent), schedule it in windows, trigger it from a batch file, ... , ... – CustodianOfCode Jun 15 '15 at 05:36
  • @CustodianOfCode Thank you for your response. How do I acccept your answer? Will install SSIS - I believe its now called Business Data Tools for VS 2013? Running into install issues, so our Helpdesk apparently has to rebuild my machine and then install the tool at the time of building. Fun :) – Bee Jun 17 '15 at 01:20
  • No worries Bee. All good, it's more of a suggestion than an answer. I hadn't realised that, but yes it appears microsoft has renamed the business intelligence development studio (bids), which was the IDE for SSIS, to something else. I'm still using 2010 so I can't really comment on that. But the tool itself is really useful for the sort of thing you're doing. Good luck. – CustodianOfCode Jun 18 '15 at 03:18

0 Answers0