Search code examples
sql-server-2017

Get data from Excel, csv into SQL Server tables

What is the best solution to transfer Excel and csv data from files to SQL Server tables and use trigger to automate it to other tables?

Solution

  • The safest way would be to create an SSIS job for reading the excel/csv files run it via the Sql Server Agents. You can find details here https://learn.microsoft.com/en-us/sql/integration-services/load-data-to-from-excel-with-ssis?view=sql-server-2017

    SQL statement for importing excel file

    SELECT * INTO Data_dq
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=D:\Desktop\Data.xlsx', [Data$]);

    https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-2017

    For CSV files https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-flat-file-wizard?view=sql-server-2017