467,149 Members | 1,334 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,149 developers. It's quick & easy.

MSSQL Query Extracting Dates in Filename

benchpolo
100+
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.
Dec 12 '08 #1
  • viewed: 2100
Share:
3 Replies
ck9663
Expert 2GB
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
benchpolo
100+
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
Dec 13 '08 #3
ck9663
Expert 2GB
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.

Similar topics

14 posts views Thread by Kukurydz | last post: by
2 posts views Thread by Ian Hinson | last post: by
2 posts views Thread by jblankenburg@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.