0

I am NOT working with SSIS, I need to use OPENROWSET and bcp to make this work:

I am having a folder that is frequently updated with csv files which need to be imported into a database.

At the moment only ONE file is imported into the database, how can I create that process so all the files in X:\project\Input\input\ are going to be imported? Again, SSIS is not an option though it would make things easier here.

(ID)
select 
case
-- when only one semi-colon---
when charindex(';',(substring(a.text,charindex(';',a.text,1)+1,99))) = 0
-- then extract the full string from first semi-colon to the max length of 99
then ltrim(rtrim(substring(a.text,charindex(';',a.text,1)+1,99)))
-- else extract the full string from the first semi-colon up to second semi-colon
else ltrim(rtrim(substring(
       /* <text>> */a.text, 
       /* <start > */charindex(';',a.text,1) + 1,
       /* <length> */charindex(';', substring(a.text, charindex(';', a.text, 1) + 1,99)) - 1 
        ) ) )
end as ID
from openrowset(bulk 'X:\project\Input\input\input.csv',---needs to be dynamic----
formatfile = 'X:\project\Input\input\formatfile.txt',firstrow=2, format='csv'
) as a;
go
vincevangone
  • 3
  • 1
  • 1
    You're very likely going to need to do this *outside* of SQL and loop through the files, calling SQL Server in each loop with a statement. – Thom A Sep 30 '21 at 08:43
  • If it's enabled and you have permissions you _could_ use xp_cmdshell to get the folder contents with DIR and loop through them in a cursor. We do this in our systems in my team. But xp_cmdshell is not to be taken lightly due to may security concerns. Outside of SQL Server is a better option if it's available. – squillman Sep 30 '21 at 13:46
  • `SSIS is not an option`: Why?!? Maybe you are going the wrong approach? please explain why SSIS is not an option. In the same time, think about using Azure Data Factory which has the ability to do exactly this. And finally if need to do it using T-SQL then here is the full solution: https://ariely.info/Blog/tabid/83/EntryId/123/Dynamically-Bulk-Insert-All-Files-In-Folder.aspx – Ronen Ariely Sep 30 '21 at 16:09
  • 1
    @RonenAriely it's not an option as the assignment my teacher gave me was solving it without SSIS to see what works better with SSIS and what doesn't :) thank you though! – vincevangone Oct 01 '21 at 12:45
  • Hi @vincevangone, `assignment my teacher gave` : this is the best reason. For the sake of learning we limit users many times so they will practice the topic they learn. Glad to hear you are studying in an orderly fashion. Did you check the post in the link I gave you? It should cover what you need `:-)` but for the sake of learning, you should have done it yourself – Ronen Ariely Oct 02 '21 at 06:26

0 Answers0