MSSQL Query Extracting Dates in Filename

[benchpolo]

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

CODE
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)

PROCESS
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.
Thanks.

[ck9663]

Why not grab all the name of the files that are candidate for uploading, store it in a table, BULK INSERT each record.

-- CK

[benchpolo]

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.

Thanks

[ck9663]

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