Several text files consolidate in one

Question

Several text files consolidate in one

Fernando-235287

SSCrazy

Points: 2485
More actions
December 6, 2005 at 2:13 am

#88828

I need some assistance if possible. I have to run some flat files with data coming from differnt tables and different columns (header and detail kind of thing, but this one has 8 sections. can not be created using any reporting tool (reporting services or crystal, etc) It has to be flat text files. The processing destination does not accept any other kind of files other than flat text.
How can insert the results to the one text file?
Let's say the first file reads:
H 20051206    4     Development      00001245      00004859
the next one reads:
P 20051206091524    Memphis      TN     38118
thir one is the detail and have more that one line as followo:
T Jack Frost         345 19670528
T Howard Huges    926 19351231

As you can see the columsn are very disimilar and do not have the same length or data type. Any help is greatly appreciated. Thank you By the way I use SQL Server 2000 sp 3(a) and the creation of the files is not a big of a deal is how can I insert all my files into one! Thank you for you help!!!

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

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

Vinoo Say Hey Kid Points: 670 More actions · Answer 1

I would take these files and doctor them to make them suitable for my needs in a language like VB (by inserting NULL or empty strings etc.) to make it compliant with the table and then import it into the tables.

Using t-SQL for this is very difficult for these kind of jobs when it can be done easily in VB ( or any other scripting language like Perl etc..) as we are talking about files here and not databases or tables.

David Burrows SSC Guru Points: 65142 More actions · Answer 2

1.

Create a table with single varchar column, concatenate (and cast where necessary) columns from each table into the varchar column. Extract the table into text file via DTS or OSQL (in DOS Prompt)

2.

Use OSQL (in DOS Prompt) to output and append each table to a single file, eg

osql -S servername -E -h-1 -Q "SET NOCOUNT ON SELECT col1,col2,col3,col4,col5 FROM [datebase].dbo.[table1]" | FINDSTR /c:" " > outputfile.txt

osql -S servername -E -h-1 -Q "SET NOCOUNT ON SELECT col1,col2,col3 FROM [datebase].dbo.[table2]" | FINDSTR /c:" " >> outputfile.txt

osql -S servername -E -h-1 -Q "SET NOCOUNT ON SELECT col1,col2,col3,col4 FROM [datebase].dbo.[table3]" | FINDSTR /c:" " >> outputfile.txt

Far away is close at hand in the images of elsewhere.
Anon.

Fernando-235287 SSCrazy Points: 2485 More actions · Answer 3

Thank you guys. I will give it a try using OSQL. Cool, I think that sove my problem!! If not, I will come back a ask a little bit more. Thanks anyway

RoelofK68 SSC-Addicted Points: 456 More actions · Answer 4

There is also a way of storing data about the different formats ("versions") of the files in a table.

All you have to do then is to ensure that your DTS can identify which file is in which format. We tag a V1 or V2 etc. to the filename for e.g. FileDataV1.txt, TextDatav2.txt.

You can then use Active-X and gv's to check this from the dts and then to use the specific import spec as set out in the mentioned table.

This DTS can then be looped to import multiple files with different layouts from a folder and move them to a "Processed" folder.

<hr noshade size='1' width='250' color='#BBC8E5'>Kindest Regards,

Roelof
<a href='http://' class='authorlink' target='_blank'></a>

--There are only 10 types of people in the world. Those who understand binary, and those who don't.