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

Asked

Viewed 1,553 times

0

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

sql sql-server sql-server-2014
  • 1

    Right click on top of your database -> tasks-> Import Data. In the window that opens click on avenging and choose the data source as Microsoft Excel

  • @Jefersonalmeida, it’s not quite so. As I said earlier is the following: In the spreadsheet I have the field Dt_proc and Num_proc. These fields will be populated. There is also a field that is Id_func. For this field I need to make a Join with another table for popular, since the table that will be populated does not have this field. That is, I need to "join" two tables to popular a third. This is the imbrolho that step.

  • The way q I said vc will create a new table with all the excel data and through this new table, vc can do all its joins to popular its true table, at the end of the process vc deletes the table that was created from excel.

  • Do you say create a temp? And popular everything with excel and then popular the other table?

  • That’s right, this way it becomes much easier for you to handle your data

1 answer

1

You can make a select directly in your spreadsheet:
(You will need to run the initial settings with the 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 select above, the OPEMROWSET will behave like a table, so you can do whatever you want, including popular the 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) must have access to the folder where your Excel file is; in the above example, "C: " is directly the server root.

  • Check which Provider you have installed:

Microsoft.ACE.OleDB.12.0

Browser other questions tagged sql sql-server sql-server-2014

You are not signed in. Login or sign up in order to post.