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