Merge two .csv files and output as a new file 1

[Scott32]

Hi,

I am practically a newbie to SQL, but have been playing with DTS to try and resolve a problem.

Problem
-------
I need to a establish a procedure that will open two .CSV text files, (both files contain identical data in the first field and each file will always contain 1 row of data only).

I then need to merge the two files together and output the results to a new file, which will contain 1 row of data but all the fields from both files.

Is this possible to do in DTS? Will I need to create a temporary database containing the rows from both files and then run a T-SQL query to get the results.

The reason I am using DTS, is because I want to monitor a folder for files, which will trigger the DTS package to run.

Any help or guidance would be very much appreciated.

Regards,

Scott S.

 

[NoCoolHandle]

Well I started to answer this earlier, (and then had some work to do

It will take a few steps.. (at least if you do it my way)

Create a SQLTask Object (pointing to your local server - tempdb would probably be ok) - the SQLTask builds a table for the purpose of having a workspace (consolidation)

Create 2 file source type objects pointing to each of your files.

Select the first file source and then the sqlconnection and then specify that you are going to merge (insert) the data into the staging table you built with the sqltask

Do the same with the second.

Kick things off with the sqltask (it should check to see it the "staging" table exists and if not, create it.)

The staging table can be as simple as you want as all you are going to do is stuff the nonheadder rows into it before creating the new textfile.

YOu then add a workflow component from the SQLTask to each of the Tasks that insert the data into the sqltable. Do this by clicking on the sqltask and then the task to do the merge - then click the "Workflow" button on the task bar. Specify the action as "ON COMPLETON". (do this for each of the file in tasks)

Then create an output file (destination) compontent.

Now build a task to take the data from the staging table in insert it into your destination file.
(Add a workflow component from each of the tasks that stuff the data into the staging table (2 arrows into 1 object) and specify again "On COmpletion"


That should do it.


Rob
(ps this is from memory so if you have issues post back)

 

[MartinCroft]

You could use xp_cmdshell & basic dos command
through a stored proc, as below, using parameter will give
you the ability to make it userble for other things

CREATE PROC test
	(
	  @Filename1 varchar(20)
	 ,@Filename2 varchar(20)
	 ,@Outputfile varchar(20)
	 ,@Path varchar(20)
	)

AS

DECLARE @Cmd varchar(120)

SET @CMD=' Type '+@Path+@Filename1+' > '+@Path+@Outputfile+' | Type ' +@Path+@Filename2+ ' >> ' +@Path+@Outputfile

--PRINT @CMD 
EXEC Master..XP_cmdshell @Cmd

To Run

EXEC Test @Path='c:\',@FileName1='file1.csv',@FileName2='file2.csv',@outputfile='file3.csv'

 

[NoCoolHandle]

Mystic,

that is cool!

The only issue i can see is if the first line has column headders, but WOW .. I didn't know you could do a double pipe!.

A star!

Rob

 

[Scott32]

Thank you NoCoolHandle & MysticMart for all your help on this, I will have a play and see if I can get it to work.


Regards,

Scott.