Upload text files to MS SQL server automatically

Question

I frequently receive text and excel files which I have to upload in local MS SQL Server. Is there any tool or command available that can automate this task. For Bulk upload provided by MS SQL you have to create the blank table first, I dont want to create a new table manually each time. Please help

If not SSIS, Powershell could do this in some lines of code as well, using SqlBulkCopy and then run it with a Windows task. — Rigerta
– Rigerta, Commented Jul 27, 2017 at 9:29
Are all the files so different that they need a special table each? — Nick.Mc
– Nick.Mc, Commented Jul 27, 2017 at 12:49
Yes they have completely different field names and data types. — Anuj Agarwal
– Anuj Agarwal, Commented Jul 27, 2017 at 14:03

Adikari Nadeesha · Accepted Answer · 2017-07-27 10:34:37Z

3

Don't need to create a data table manually . In MS Sql server there is a function for that .

Right click on Database Name
Then go to 'Tasks' and then go to the tab 'Import Data' & click on that
you will get the data export , import wizard, Click next.
If excel file , Select data source as 'Microsoft Excel' then browse the excel file (make sure your excel file save in 97-2003 format). if text file , Select data source as 'Flat File Source' , then browse the file .
Proceed with the wizard , you will get the data table with relevant data.

answered Jul 27, 2017 at 10:34

Adikari Nadeesha

3123 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anuj Agarwal Over a year ago

Yes I know this feature & I currently use that only but to do it manually for say 10 files is cumbersome. I want that all the files if they are 10 or 20 inside a specific folder should get uploaded at once in sql server.

Rigerta Over a year ago

@AnujAgarwal check out this link: How to loop through files in a specified folder, load one by one and move to archive folder using SSIS

Collectives™ on Stack Overflow

Upload text files to MS SQL server automatically

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related