How do i get the newest CSV and import it to sql table using BCP. I receive files based on the date and time, so I need a way to get files that a the latest from the time i imported the last one. Please help.
asked
Getting the newest posted CSV/Flat file
If the files have date and time in their name, then simply maintain a list of imported files in some sort of control table.
If not then either, make them, or maintain the list based on name and date/time.
Without more info, it's a bit of a guess...
Thanx for your quick respond. The files have date and time. For example: "Just_20100615_162301558" I have to receive few files every minute, after receiving the file I have to pust the data to sql. The flat file format looks something like: 1|Just|TC|25 Thanking you in advance
I think the most elegant solution involves stepping outside of T-SQL and using something like PowerShell or Python to write a routine that will handle this. PowerShell and Python can work closely with the OS to easily get the timestamp of the file itself or to easily parse the file names since you mentione din your comment to Kev that the file name includes the date and time.
If you want to keep it inside of T-SQL, then I agree with Kev that a control table is the best way to go.
Of course, whether you choose PowerShell, Python, T-SQL or something else, remember that after BCP is done with the file you can move it to an archive directory. That could simplify the entire flow.
I think that SSIS would be the best tool to use. You would still have a control table that holds the previous files, and you would put the imported files into an archive folder and delete when necessary.
The you can simply use a for each loop container in the control flow to get the filenames, and a data flow task to import the file, then a file system task to move the file to the archive folder. Seriously, it would only take about 10-15 minutes to setup.
Pls help, this is what I have. Can u give me a guide on SISS as I haven't used it before? Declare @Filename varchar(50) ,@BCPString varchar(8000) ,@rc int ,@FileID varchar(100) ,@now datetime SET @FileID =convert(varchar,getdate(),112) SET @now= replace(convert(varchar,@now,114),':','') SET @Filename = 'MCS_'+Cast(@FileID as Varchar(10))+'_'+ cast(@now as Varchar(10)) +'.txt' select @BCPString = 'BCP "dd_dev.dbo.Data_Staging" in "\\am_dev\d$\data\'+ @filename +'" -Usa -Ppass -c -Sam_dev' Select @BCPString exec @rc = master..xp_cmdshell @BCPString
