Sql multiple file import

Question

I want to insert multiple csv file to sqlserver table. I have a procedure for doing that. It works but my all files have same column name. So instead of importing all records it just imports column name which is the first row. If I manually delete first row then it imports the other records, I have thousands of files so I cant work manually. I am posting store procedure here. Please tell me if I can change something to make this work.

ALTER procedure [dbo].[usp_ImportMultipleFiles] @filepath varchar(500), 
    @pattern varchar(100), @TableName varchar(128)
as
set quoted_identifier off
declare @query varchar(1000)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
set @count1 =0
create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x 
drop table #x
set @max1 = (select max(ID) from #y)
--print @max1
--print @count1
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @Query ='BULK INSERT '+ @Tablename + ' FROM '''+ @Filepath+@Filename+''' 
    WITH ( FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'')'
--print @query
exec (@query)
insert into logtable (query) select @query
end

drop table #y

M.Ali · Accepted Answer · 2014-04-29 19:01:05Z

1

You can use the First Row option in your bulk insert statement something like...

BULK INSERT Table_Name 
FROM 'C:\FilePath'
WITH 
     (
       FIELDTERMINATOR = ',' 
      ,ROWTERMINATOR   = '\n'
      ,FIRSTROW        = 2    --<-- This option here
     )

Edit to your proc

DECLARE @Query NVARCHAR(MAX);

SET @Query = N'BULK INSERT '+ @Tablename + 
             N' FROM '''+ @Filepath+@Filename + 
             N'''  WITH ( FIELDTERMINATOR = '',''
                         ,ROWTERMINATOR = ''\n'' 
                         ,FIRSTROW = 2
                       )'

edited Apr 29, 2014 at 19:01

answered Apr 29, 2014 at 18:53

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

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

3 Comments

pritesh Over a year ago

M.Ali thanks for reply. I tried using your code but its not working

M.Ali Over a year ago

What do you mean by its not working? do you get an error? the code didnt execute at all? if you give more information maybe we can find a solution :)

pritesh Over a year ago

code executes perfectly but there are no records in the table. I can share result ..

Collectives™ on Stack Overflow

Sql multiple file import

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related