Writing two tables to one text file in one job: can it be done?
-
(I hate it when the freakin’ message editor eats a long post! This one I’m doing in Word.)
I have two tables to write to a CSV text file. One is a header record of seven fields, the other is a detail record of generally a dozen or more fields. One header record, one to many detail records.
My first attempt was to have two SQL data sources pointing to the two different tables with two Transform Data tasks pointing to one Text File (Destination). By creating the detail record transform first I was able to map all the header record fields to the output. When I ran the task, the detail step ran before the header step and the result file had only the header record.
My second attempt was three steps.
1. Output the detail records with a SELECT TOP 0 * to populate the field list.
2. Output the header record.
3. Output the detail records.
“On Completions” led from one step to the next.
The result was a file containing only detail records. So obviously the output file is being overwritten. BOL under DTS/Write File Transformation says "If a file with the same name already exists, the transformation does one of the following: Replaces the existing file, Appends the contents of the data column to the existing file, ...." I have a feeling that they are talking about an area of DTS that I'm not in.
My thought for another attempt was to create a table with a couple dozen varchar(255) fields, insert the header, insert the details, output the file. But that won’t work as I can’t have superfluous commas at the end of records. Thus, a union query won’t work as a solution.
Here’s the biggie: I have 170 table pairs that I need to output. So I’m looking for a reasonably elegant solution. Every header table is seven fields, the number of fields in the detail record is totally variable.
I'm considering writing two text files then doing a CMD batch to merge them, not very elegant.
Suggestions?
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
My thought for another attempt was to create a table with a couple dozen varchar(255) fields, This is only a test, you can adapt it as necessary
CREATE TABLE [Table] (SortKey char(1),
col1 varchar(255),
col2 varchar(255),
col3 varchar(255),
col4 varchar(255),
col5 varchar(255),
col6 varchar(255))
INSERT INTO [Table] VALUES ('0','col1','col2','col3','col4','col5','col6')
INSERT INTO [Table] VALUES (1,'A','B','C',null,null,null)
INSERT INTO [Table] VALUES (1,'D','E','F','G','H',null)
INSERT INTO [Table] VALUES (1,'I','J',null,null,null,null)
SELECT col1+
COALESCE(','+col2,'')+
COALESCE(','+col3,'')+
COALESCE(','+col4,'')+
COALESCE(','+col5,'')+
COALESCE(','+col6,'')
FROM [Table]
ORDER BY SortKey
You can then export the single column of concatenated data.
Far away is close at hand in the images of elsewhere.
Anon. -
Coalesce, in its raw form, won't do it. I neglected to mention that I have null fields (fillers) in the data. They're placeholders but must be represented by commas in the data file, also, if they decide to switch from CSV to column oriented output, it's covered. Or if the Feds define one of the fillers for holding something not in the current definition. Oh, the joys of abiding to someone else's spec! 🙂
That being said, your suggestion does give me some ideas if no one else comes up with a DTS solution.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Your final kludgy solution was the one I ended up doing when faced with essentially the same problem. In my case I was appending a trailer record instead of a header record.
After I created the two text files, I used the Execute Process Task to kick off a batch file similiar to the one below.
C:
cd C:\someplace
type TrailerRecord.dat>>C:\someplace\xxxxx.dat
del TrailerRecord.dat
Teague
-
Teague, FYI, you can use the copy command to concatenate files, you don't have to use type. I believe the syntax would be along the lines of "copy trailerrecord.dat+xxx.dat", you'd need to do some experimenting. But your using type to append it is probably the easiest way to do it. 🙂 A lot of people don't know about concatenating files via copy.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Thanks,
I got the type command from the resident DOS guy....does everybody have one of those?
-
Dos guys? Sometimes. I go back to the original PC with 4 screws on the back (5 screws came later) and 384K of ram on the motherboard, some 20 years ago. I also speak some *nix, so I can definitely see the 'type' approach to what you did.
(I also worked with the original 128k Mac with a single floppy drive, the only spreadsheet was Microsoft Multiplan if I recall correctly.)
Since Windows has been around for over a decade now, I can see this sort of trivia/minutia fading away. Still, it's a useful thing to know. What's amazing is the number of unix-like utilities stashed away within Windows that people don't know about.
I also have a sweet little batch file called 2.bat that lets you change directories with ease: type '2 e dir1 dir2 dir3 dir4' and it will go to e:\dir1\dir2\dir3\dir4, and if dir4 doesn't exist, it will stop at dir3 rather than abort the whole command.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
This is interesting, would anyone know how to append one text file to the top on the second text file. In other words I have a text file with one line only which I need to append to the very top of the second text file. This second file has many lines.
Thank you everyone
M.
-
This'll do it.
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
H:\>copy con: file2.txt
line1
line2
line3
line4
^Z
1 file(s) copied.
H:\>copy con: file1.txt
LINE-A
^Z
1 file(s) copied.
H:\>copy file1.txt+file2.txt file3.txt
file1.txt
file2.txt
1 file(s) copied.
H:\>type file3.txt
LINE-A
line1
line2
line3
line4
H:\>
To do it without a third file you would do:
H:\>copy file1.txt+file2.txt
file1.txt
file2.txt
1 file(s) copied.
H:\>type file1.txt
LINE-A
line1
line2
line3
line4
H:\>
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Or you could use the redirection operators:
type header.txt > combined.txt
type detail.txt >> combined.txt
-
Thank you very much.
M.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply