Importing txt File into SQL Server

Question

Importing txt File into SQL Server

rakkpra

Old Hand

Points: 304
More actions
November 9, 2011 at 10:39 am

#150618

I want to import the a txt file into SQL Server . I Am using BCP in to import. Importing works fine for me . My question is we will be having the file in the location like ABC_11012011.txt . In my code i will
declare a @FileName . but how do i set the file name? I know that there is no way i can say Set @FileName Like 'ABC%'
Any help is appriciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Oksana March SSChasing Mays Points: 603 More actions · Answer 1

I would be very interested if there is a way to do it from within SQL Server without third-party programming.

I have a similar situation, we are receiving files via FTP with different filenames. We coded an ASP "listener" that "listens" on specific folder and loads any file that arrives there into staging table.

Lowell SSC Guru Points: 323518 More actions · Answer 2

if you can install a CLR, that's the way to go; there a re a number of examples out there ,

i personally like fellow poster Elliot Whitlow's contribution on http://nclsqlclrfile.codeplex.com/

one of the CLR functions he wrote is called like this:

SELECT * FROM dbo.MFGetDirectoryList('C:\Data\')

--returns 3 column table FilePath/FileName/FileNameWithPath

--WHERE FileName like '%.txt'

you could then check things like if the file extension from those results end in the expected extension (ie my WHERE example) and do whatever work you were going to do.

if you cannot do CLR, you can do something similar with xp_cmdshell.

create table #Files (

FileName varchar(1000))

insert into #Files (FileName)

exec master..xp_cmdshell 'dir c:\Data\*.txt /b'

select * from #Files

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Taggs Ten Centuries Points: 1184 More actions · Answer 3

Very similar Code but this is what I have done in the past.

It may help 🙂

Declare @date varchar(10),@BulkCSVImport varchar(200)

set @date = convert(varchar(10),getdate(),105)

set @BulkCSVImport = 'BULK Insert HDD_Info_temp from ''\\server-01\bulkimport\HDD-REPORT-SUCCESS_' + @date + '.csv'' with (fieldterminator = '','',rowterminator ='''')'

Exec (@BulkCSVImport)

Insert into HDD_Info ([Client_name],[auditdate],[driveletter],[driveserial],[drivelabel],[filesystem],[totalsize],[totalused],[systemdisk],[pagefile])

(select * from HDD_Info_temp)

go

rakkpra Old Hand Points: 304 More actions · Answer 4

Thank you .I do not want to take the date part because we get the file once a month and we are not sure we will be importing the file the same date we received. the my file name Contains the date where the date is day Vendor created the file

rakkpra Old Hand Points: 304 More actions · Answer 5

The Option i was thinking is while we are receiving the file from the vendor we use FTP to do that i was thinking of renaming the file to truncate the date and then import it . Seems like a easy fix.