none
import from excel sheet - 2 ranges RRS feed

  • Question

  • hi,

    i need to import from Excel file 2 ranges -

    like : customer, street, var2, var3 , var4 , var 5

    it works if to import 1 range - for example - till variable 3 :

    select *
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=D:\tests.xls; HDR=YES; IMEX=1', 'SELECT * FROM [Form$a4:d10]')  -- table  starts from row 4

    but i need to skip 2 columns - variable 2 & variable3 , as they variable names are the same as var 4 and var5,  and  import  something like this :  [Form$a4:b10] + [Form$e4:f10] 

    cant find how.

    variable 2-5 = variable column names, that changes (moving year&month names)



    Wednesday, August 7, 2019 4:22 PM

Answers

  • but i need to skip 2 columns - variable 2 & variable3 , as they variable names are the same as var 4 and var5,  and  import  something like this :  [Form$a4:b10] + [Form$e4:f10] 

    It seems to me that you can define a single range and then ignore columns B and C in SELECT.

    -- code #1
    SELECT customer, street, var4, var5
      FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0; Database=D:\tests.xls; HDR=YES; IMEX=1',
    'SELECT * FROM [Form$a4:f10]') as T (customer, street, var2, var3, var4, var5);
     



    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    Wednesday, August 7, 2019 9:02 PM
    Answerer