x

Getting the newest posted CSV/Flat file

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.

more ▼

asked Jun 15, 2010 at 10:42 AM in Default

justice gravatar image

justice
35 2 2 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

more ▼

answered Jun 15, 2010 at 11:27 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.2k 47 49 76

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
Jun 15, 2010 at 11:44 AM justice
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jun 15, 2010 at 12:29 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jun 15, 2010 at 08:19 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 10 13 14

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
Jun 17, 2010 at 03:33 AM justice
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x66
x42

asked: Jun 15, 2010 at 10:42 AM

Seen: 1152 times

Last Updated: Jun 15, 2010 at 11:27 AM