Forum Discussion
Create a database form excel files updated daily
Hi,
I am new to SQL and used it mostly as a source of Power BI reports.
there are Excel files that are daily updated on the server I used as a zip file; they are from a third party and this is the only way to access the data. Right now every day I copy and paste them in the Excel file that is used as a Power BI data source.
I am thinking of creating a database in SQL Server Management to automatically update rather than manually copy and paste.
Please share an article, video, or any other source that can help me.
- When you extract the file from the zip, what is the resulting file format (.txt, .csv, something else)?
- CSV
- You are looking to run an ETL (Extract transform load process) process. The solution depends on what environment you are set up in. If you are working in Azure then you could for example create a data factory pipeline that would be triggered when you drop the Excel files into blob storage in Azure. You could then create a copy task to read the data in the Excel files and then write them directly into the database. Then all you would have to do is drop the files into a folder when you receive them.
You can also run a query directly from SQL to read an Excel file with OPENROWSET:
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;Database=C:\data\yourExcelFile.xlsx;', sheetName);
You could then create a stored proc to open the data and insert it into a table. You could then schedule the store proc to run
