By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

MSSQL Query Extracting Dates in Filename

P: 142
Sample A: Enrollment_20081211.txt
Sample B: Enrollment_20081211_01.txt

declare @date varchar(8)
, @cmd varchar(255)

set @date = REPLACE(CONVERT(VARCHAR,GETDATE()-1,111),'/','')

truncate table bow_flat
select @cmd ='BULK INSERT [1400_byte_layout].dbo.IKA_Flat FROM ''\\test\ArcadianEnrollment_'+@date+'.txt'''
exec (@cmd)

The code above BCP Sample A file into a table based on the current date @date, and this process is fine. But I am having issues with Sample B where the filename has an increment number before the .txt filename, I having difficulty modifying the code above to make it work for sample B.

Your assistance regarding this matter is aprpeciated.
Dec 12 '08 #1
Share this Question
Share on Google+
3 Replies

Expert 2.5K+
P: 2,878
Why not grab all the name of the files that are candidate for uploading, store it in a table, BULK INSERT each record.

-- CK
Dec 13 '08 #2

P: 142
ck that was my problem I don't know where to start. Also, the format of the file is PIPE delimited. I created a table to import the file, but how do i do it if i have multiple files. Import process is done by date dynamically as indicated in the text filename.

Dec 13 '08 #3

Expert 2.5K+
P: 2,878
1. Use xp_cmdshell to grab all the files on a predetermined directory.
2. Store the result to a table.
3. Loop through the table and BULK INSERT each filename into a table with the same name. If you don't need th eactual table, use temp or variable tables and process as necessary. BULK INSERT has a parameter to define what delimiter to use.

-- CK
Dec 13 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.