Looking for a way I could automate a daily task where I do a flat file csv import into my db. What I do now is from db, rc the db name>tasks>import data> Flat file Source> browse to file> enter " in text qualifier> next>Change destination to SQl SERVER NATIVE CLIENT 11.0> enter SQL Server Authentication> Next> Change table name in destination>Edit Mappings> select Delete rows > next> finish> finish
How can this be automated? I'd like to hand this off to an associate but don't want him in the db to run it. Can a bat file be created and it run as a scheduled task?
Yes that can be done many different ways
Ssis
Bat
Powershell
Python
Etc
Any good references I could use to write a bat file for that? It will be my first bat file. Never done it before. I've never done the other either.
take a look bcp
download bcp if you do not have it
csv file is as follows
id,name,job
1,"cardgunner","sql dev"
create table cardgunner(id int, name varchar(50), job varchar(50))
bcp dbo.cardgunner in C:\Users\cardgunner.csv
-S localhost -d sqlteam -c -T -t ","
I did a quick search - the top item was a link to this article: https://devblogs.microsoft.com/scripting/four-easy-ways-to-import-csv-files-to-sql-server-with-powershell/
There are many articles - but I would not recommend a batch file. If you need to do something like that then powershell would be my recommendation. You have much more control over the process.
If you decide to go that route - lookup up dbatools.io (https://dbatools.io/) - which has many built-in functions to do almost anything you need to do.
1 Like