Import multiple text files into SQL Server

Question

I am using SQL Server 2017 Express edition on Microsoft Windows 10. I want to import all text files in a directory into a database at one time. My idea is that that SQL Server loops over the files in that directory and imports them all at the same time. Is there a way that I can achieve this?

Best Regards,

I don't know of a pure SQL solution, but it should be possible to do this with a bash script which invokes SQL Server. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 12, 2018 at 14:24
I am posting the question so maybe I can get an answer I don't know yet. Let's see what others have to say about — GitZine
– GitZine, Commented Aug 12, 2018 at 14:45
Do you know the format of the data? Is the definition the same in everyfile? What is the definition? What is the destination table? What is the definition of the destination table? There's a lot of questions that need answering here to provide an in-depth solution. Also, have you tried anything? If so, what? Could you provide the code and describe the issues/errors you've encountered? — Thom A
– Thom A ♦, Commented Aug 12, 2018 at 15:17
I know it's possible in SQL Server Integration Services (SSIS) using a for-loop. In that case, there is a special option to loop through every file in a folder (using wildcards if desired). — Zorkolot
– Zorkolot, Commented Aug 12, 2018 at 15:23
@Larnu all the files are with the same format. All the files have the same definition with different size. The destination table should have the same name as the source file. — GitZine
– GitZine, Commented Aug 12, 2018 at 15:38

BoCoKeith · Accepted Answer · 2018-08-12 18:26:17Z

2

declare @dir varchar(1000) = 'C:\Temp';
declare @command varchar(1000);
declare @files table (id int identity, filename varchar(1000));

set @command = 'dir /b ' + @dir;
insert into @files execute xp_cmdshell @command;

declare commands cursor for 
    select 'bulk insert <your table name here> from ''' + @dir + '\' + filename + ''' <other options, WITH FORMAT, etc. here>;' from @files;
open commands;
fetch commands into @command;
while (@@fetch_status = 0) begin
    --print @command;
    execute(@command);
    fetch commands into @command;
end;
close commands;
deallocate commands;

Modify @dir and the bulk insert command that is being build and you're done.

You may have to enable 'xp_cmdshell', and this could be a problem for your DBA; and using 'execute' is always a potential issue (SQL injection, etc.).

To enable xp_cmdshell:

-- To allow advanced options to be changed.  
EXEC sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO

edited Aug 12, 2018 at 18:26

answered Aug 12, 2018 at 15:05

BoCoKeith

1,0042 gold badges12 silver badges23 bronze badges

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

9 Comments

BoCoKeith Over a year ago

It does if you replace the 'print' statement with a 'bulk insert' statement, as I suggested above.

GitZine Over a year ago

I used your query @BoCoKeith and added the directory instead of the C:\temp but it still does not work

Thom A Over a year ago

I had missed that, sorry.

GitZine Over a year ago

Can you write all the query with this directory as an example and with the bulk insert to see if it works? Directory: C:\Users\zineeddine.bouzennou\Documents\TS\Mini_Bundle_1000\Mini_Bundle_1000_v2

BoCoKeith Over a year ago

it would be better if you posted the query your are trying to use so that I could make suggestions. I don't have your directory, files, schema, etc., so I really can't duplicate what you are doing on my end very easily.

|

Alex Kudryashev · Accepted Answer · 2018-08-12 19:56:49Z

As noted in another answer, xp_commandshell is problematic. SQL Server 2016+ allows another approach. See example

declare @tbl table(fn varchar(255), depth int,[isfile] int,primary key(fn))
declare @dir varchar(50)='C:\temp\' --'starting dir
insert @tbl
EXEC Master.dbo.xp_DirTree @dir,1,1 --dir, depth (1 - current only), file (0 - dirs only, 1+ - dirs and files)

delete @tbl where [isfile]=0 or fn not like '%.txt' --keep .txt files only
--select * from @tbl
--will insert into this table
create table #fileTbl (id int identity(1,1) primary key,fn varchar(255),txt varchar(max))
declare @fn varchar(255), @query nvarchar(4000)
select top 1 @fn=fn from @tbl
while @@ROWCOUNT>0--number of rows from the last query executed
begin
--dynamic query required to build OPENROWSET
set @query='insert #fileTbl(fn,txt) select '''+@dir+@fn+''',BulkColumn from openrowset(bulk ''c:\temp\'+@fn+''',single_blob) t'
exec sp_executesql @query
delete @tbl where fn=@fn
select top 1 @fn=fn from @tbl --proceed
end
select * from #fileTbl

Collectives™ on Stack Overflow

Import multiple text files into SQL Server

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related