Import text file to SQL Server

Tarana

I have a project which I need to do the following steps:

1.Look for a partular file .txt in a directory. If the fil exists, loadn txt file ino SQL Server.

2. If # 1 was sucessful, then delete the source file.

Every day, do the same thing ie check for existence of file, import into sql table, then delete the file.

Pl advise the best way to do this:

Arnie Rowland

You will want to create a SQL Agent Job.

In step one, use BULK INSERT to load the data. (See Books Online for Bulk Insert.)

In step two, use delete the file.

Tarana

Before loading the data, I need to check if the File exists- What command do I use to do that

DaleJ

Code Snippet

create table #fileinfo( [File Exists] int, [File is a Directory] int, [Parent Directory Exists] int)

insert into #fileinfo

exec master..xp_fileexist 'c:\myfile.txt'

if exists( select * from #fileinfo where [File Exists] = 1)

Begin

Print 'do bulk insert'

End

else

Begin

Print 'do not do bulk insert'

End

Arnie Rowland

You could use the undocumented command: xp_fileexists.

See:

Functions -UnDocumented Check to Verify File Existance
http://www.sqlservercentral.com/columnists/bknight/xpfileexist.asp

DaleJ

Another option, if you want to avoid the undocumented xp_

Code Snippet

create table #fileinfo(output varchar(4000))

insert into #fileinfo

exec master.dbo.xp_cmdshell 'dir C:\myfile.txt'

if exists( select * from #fileinfo where output like '% myfile.txt')

print 'do bulk insert'