Stored Proc for csv import - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Stored Proc for csv import

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-06-22 : 17:03:32

Guys,

I have grabbed the below stored procedure from the following link.

http://www.nigelrivett.net/SQLTsql/s_ProcessAllFilesInDir.html

Stored Procedure importa csv text files from a directory into database table.

In the BCPData I want to store the name of the txt file from which the data is imported. Is there any way I coould the stored proc to do the same.

Any suggestions/inputs would help.

Thanks

ALTER procedure [dbo].[ImportFiles]
@FilePath varchar(1000) = 'c:\Transfer\' ,
@ArchivePath varchar(1000) = 'c:\Transfer\Archive\' ,
@FileNameMask varchar(1000) = '*.txt' ,
@MergeProc varchar(128) = 'MergeBCPData'

AS

set nocount on

declare @ImportDate datetime
select @ImportDate = getdate()

declare @FileName varchar(1000) ,
@File varchar(1000)

declare @cmd varchar(2000)

create table ##Import (s varchar(8000))
create table #Dir (s varchar(8000))

/*****************************************************************/
-- Import file
/*****************************************************************/
select @cmd = 'dir /B ' + @FilePath + @FileNameMask
delete #Dir
insert #Dir exec master..xp_cmdshell @cmd

delete #Dir where s is null or s like '%not found%'
while exists (select * from #Dir)
begin
select @FileName = min(s) from #Dir
select @File = @FilePath + @FileName

select @cmd = 'bulk insert'
select @cmd = @cmd + ' ##Import'
select @cmd = @cmd + ' from'
select @cmd = @cmd + ' ''' + replace(@File,'"','') + ''''
select @cmd = @cmd + ' with (FIELDTERMINATOR=''|'''
select @cmd = @cmd + ',ROWTERMINATOR = ''' + char(10) + ''')'

truncate table ##Import

-- import the data
exec (@cmd)

-- remove filename just imported
delete #Dir where s = @FileName

exec @MergeProc

-- Archive the file
select @cmd = 'move ' + @FilePath + @FileName + ' ' + @ArchivePath + @FileName
exec master..xp_cmdshell @cmd
end

drop table ##Import
drop table #Dir

-------------------------*****************-------------------------

ALTER procedure [dbo].[MergeBCPData]

AS
set nocount on

-- insert data to production table
insert BCPData
(
fld1 ,
fld2 ,
fld3
)
select
fld1 = DBO.GETTOKEN (S, ';', 1) ,
fld2 = DBO.GETTOKEN (S, ';', 2),
fld3 = REPLACE(DBO.GETTOKEN (S, ';', 3), CHAR(13), '')
from ##Import

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-22 : 17:15:16

After you do the import, update the ##Import with the filename in @file.


Update Import  Set FileName  = @file Where <condition>

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources