0

I have an ETL process that is entirely written using T-SQL scripts. The script grabs an Excel spreadsheet and imports it using openrowset. Once I get the transformed data I copy with headers and paste into a blank spreadsheet. I will have about 50 different spreadsheets that need to go through this process.

Without using SSIS (due to budget reasons I can only work with SQL Server Express 2012 or higher), how can I dynamically/automatically grab a spreadsheet in a local folder for import into my database? Then at the end of the process what method should I use to automate the export to an Excel spreadsheet?

Here is my import script:

SELECT * INTO XLS_IMPORT
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\RSG_ETL_Tool\Ohio\OH.xls; HDR=YES; IMEX=1',
'SELECT * FROM [OH$]');

The 50 spreadsheets are for the 50 different states. Somehow I need to have the OH.xls filename be dynamic instead of hardcoded so that no matter which state spreadsheet is in the Import folder (C:\import) it will be imported into my database table.

My export script is done using a powershell script to dump the results to a csv file.

Invoke-Sqlcmd "SELECT * FROM ETL.DBO.XLSEXPORT;" -ServerInstance "DESKTOP-MBNDM2Q\SQL2012" | Export-Csv -Path "C:\RSG_ETL_Tool\Exports\SQLExport.csv" -NoTypeInformation
  • 1
    You are already part way there, just use Powershell for the entire process. It can work with Excel just as easily as it can work with just about anything else. – Jonathan Fite May 13 '16 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.