none
Automatically open, save and close excel? RRS feed

  • Question

  • I need import data from Excel to SQL.

    Now, I use tSQL to directly read the excel files and transform to the SQL table.

    As the user Excel is not a good format for SQL to read, I need use formula to transform the excel to be a SQL readable format.

    I am thinking to I use a separate Excel file to use formula to link up to the data from the the source excel.

    Then, I need find some method to open that excel, let the content refresh from the source excel, save and close it.

    Then I can directly use this excel for data import.

    Is there any simple way to do so? I don't know marco much...

    Ivan

    Thursday, July 31, 2014 5:37 AM

Answers

  • Hi,

    According to your description, you want to import an Excel workbook into a table of SQL Server. So I think it is in the side of SQL Server. Whether we can automate the Excel workbook depends on the SQL Server. As far as I know, there are much ways to import Excel workbook into SQL Server table as followed (How to import data from Excel to SQL Server). But they are all using T-SQL to read data of workbook. I'm afraid it's hard to run a code to automatically operate workbooks in SQL Server. About this point, you could ask help in SQL Server forum to confirm.

    • SQL Server Data Transformation Services (DTS)
    • Microsoft SQL Server 2005 Integration Services (SSIS)
    • SQL Server linked servers
    • SQL Server distributed queries
    • ActiveX Data Objects (ADO) and the Microsoft OLE DB Provider for SQL Server
    • ADO and the Microsoft OLE DB Provider for Jet 4.0

    By the way, if you haven't installed Excel application in the SQL Server side, we cannot operate Excel workbook. In addition, if your SQL Server is in the server side, Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

    For more workarounds about the format issue when importing Excel workbook in SQL Server, I suggest you posting in SQL Server forum for more effective responses.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, August 1, 2014 3:22 AM
    Moderator
  • You posted the following:  'I need import data from Excel to SQL.'

    You may be over-complicating this.  Please take a look at these links for some ideas of how you can do this, quite easily.

    http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm#Excel%20Data%20Export%20to%20SQL%20Server%20Test%20Code 

    http://www.mssqltips.com/sqlservertip/1540/insert-update-or-delete-data-in-sql-server-from-excel/

    https://www.simple-talk.com/sql/database-administration/getting-data-between-excel-and-sql-server-using-odbc--/


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by Ivan Kan Monday, August 11, 2014 2:51 AM
    Saturday, August 2, 2014 9:46 PM

All replies

  • Hi,

    According to your description, you want to import an Excel workbook into a table of SQL Server. So I think it is in the side of SQL Server. Whether we can automate the Excel workbook depends on the SQL Server. As far as I know, there are much ways to import Excel workbook into SQL Server table as followed (How to import data from Excel to SQL Server). But they are all using T-SQL to read data of workbook. I'm afraid it's hard to run a code to automatically operate workbooks in SQL Server. About this point, you could ask help in SQL Server forum to confirm.

    • SQL Server Data Transformation Services (DTS)
    • Microsoft SQL Server 2005 Integration Services (SSIS)
    • SQL Server linked servers
    • SQL Server distributed queries
    • ActiveX Data Objects (ADO) and the Microsoft OLE DB Provider for SQL Server
    • ADO and the Microsoft OLE DB Provider for Jet 4.0

    By the way, if you haven't installed Excel application in the SQL Server side, we cannot operate Excel workbook. In addition, if your SQL Server is in the server side, Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

    For more workarounds about the format issue when importing Excel workbook in SQL Server, I suggest you posting in SQL Server forum for more effective responses.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, August 1, 2014 3:22 AM
    Moderator
  • You posted the following:  'I need import data from Excel to SQL.'

    You may be over-complicating this.  Please take a look at these links for some ideas of how you can do this, quite easily.

    http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm#Excel%20Data%20Export%20to%20SQL%20Server%20Test%20Code 

    http://www.mssqltips.com/sqlservertip/1540/insert-update-or-delete-data-in-sql-server-from-excel/

    https://www.simple-talk.com/sql/database-administration/getting-data-between-excel-and-sql-server-using-odbc--/


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by Ivan Kan Monday, August 11, 2014 2:51 AM
    Saturday, August 2, 2014 9:46 PM