Popular table in sql server with data coming from an excel spreadsheet

Navigation
0

How do I create a script or via DTS , a way to populate a table in sql server 2014 , with information coming from a excel worksheet? The spreadsheet has several columns, but only two columns will be populated, and two other columns inform me the employee code and procedure so that it is populated correctly.

    
asked by anonymous 18.04.2017 / 19:47

1 answer

1

You can make a select fine directly in your spreadsheet:
(You will need to perform the initial configuration with sp_configure ) 

sp_configure 'show advanced options', 1
reconfigure

exec sp_configure 'Ad Hoc Distributed Queries', 1 
reconfigure

SELECT * FROM OPENROWSET ('Microsoft.ACE.OleDB.12.0', 
                          'EXCEL 8.0;Database=C:\importacao.xlsx',
                          Planilha1$)

In the above select, OPEMROWSET it will behave like a table, so you can do whatever you want, including popular tables with the fields you want. 

SELECT COD_FUN, COD_PROCEDIMENTO INTO TabelaTeste
FROM OPENROWSET ('Microsoft.ACE.OleDB.12.0', 
                 'EXCEL 8.0;Database=C:\importacao.xlsx',
                 Planilha1$)

Requirements

  • The Server (where SQL Server is installed) should have access to the folder where your Excel file is located; In the above example, "C: \" is directly the root of the server.

  • Check which Provider you have installed:

    
18.04.2017 / 20:54
Pass variable to html page with NodeJS What is an API Rest for front and backend communication? [duplicate]