Import from text file problem

Question

Post reply

Import from text file problem

Saurabh Mathur

Mr or Mrs. 500

Points: 531
More actions
October 9, 2005 at 6:43 pm

#66912

I want to import data from a text file, which is placed in a particular folder . The thing is that the name of the file changes everytime an application is run, and the DTS needs to pick up the new file, and import it. I cant figure out how to get this.
The other dilema is that the text file(s) come from different sources, and depending on what the first character is in the file, it would copy the data to different columns of the destination table. How can I do this with ActiveX scripts or any other methods.
Any help would be appreciated.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

A Text file is a connection. Use activex to name your connection "Your file" at runtime

http://www.sqldts.com/default.aspx?200

Other File management examples

http://www.sqldts.com/default.aspx?292

Adam-194437 SSC Rookie Points: 33 More actions · Answer 2

Here's a relatively simple version :

1. Find the file name. The easiest way to do this is to write a stored procedure and return the filename to a Global Variable.

You could use ActiveX as well.

2. Set the File Transfer Task source using a Dynamic Properties task.

3. Load each row of the file into a single field of a temp table(or two fields - one for the 1st char, one for everything else

4. Use TSQL to pull apart the contents of the temp table and store them as required.

This isn't the most elegant way of doing things, but it is fasst and simple.

Good Luck.

Rob Sanguin SSChasing Mays Points: 610 More actions · Answer 3

To list the names of files in a directory you can use the 'dir' command. Execute it using xp_cmdshell.

There might be an extended procedure to do it for you too. I know there's one to indicate if a named file exists (master.dbo.xp_fileexist). keep searching and you might find that someone else has done just what you need!

Saurabh Mathur Mr or Mrs. 500 Points: 531 More actions · Answer 4

Thanks guys,

I am infact using Adam's approach regarding putting the data into a temporary table and extracting it from that later on.

RE the getting the file from the directory, still lookin arnd.

Appreciate the help.