We have a folder which every day receives a new CSV file from our remote plants. What we want to do is build a Powershell script that can be run on a schedule from within MS SQL and read these CSV files and insert them into a table. The file names are all the same name, but we need to read only the files we have not read in in the past.
Are there examples on this site which would help to write this powershell script and could you give examples of what I would use to get this working. I have about a day to get all this working.
If you'll type "csv sql" in the little search box next to the thread title towards the top of this page, it will give you a ton of excellent posts on different ways to import csv data into a SQL table. Honest, the folks here really know what they're doing.
Your bigger challenge may be setting up security and a proxy account to accomplish what you've described.
I would start with Four Easy Ways to Import CSV Files to SQL Server with PowerShell
I wrote Import-CsvtoSql which could help you. The syntax is as follows:
Import-CsvToSql -Csv C:\temp\housing.csv -SqlServer sql001 -Database markets -Table housing -Delimiter "`t"
That will take care of importing the CSV into SQL, so you'll just have to create the routine that checks to see what's already been processed. To keep track of which files have been processed, you can move the files, rename them, or keep a log in a table.
Powered by IDERA