SolvedPrivate

Append header to data

Posted on 2014-02-12
5
29 Views
Last Modified: 2016-02-10
I have to append a header to data in a text file

I have to make an output file   ouput.txt  by combining  2 sets of Data.  The files are not related.
 
The Header information does not change(static)
Data 1

Header.txt
ABCDEFPASS          2345uuNA          ABCDEFG        ABCGFRT     F          ABC       TESTING    

Data 2 is from table and changes every night

Business 1        MANNTR    4567777              789999                80000
Business 2        FANNTR     6387999               789653               70000
Business 3        Bauctyr       4569877              369887               60000


I am not sure how to combine the header (header.txt) with the details in Table B to get the desired output.txt Text file   ---as below

ABCDEFPASS          2345uuNA          ABCDEFG        ABCGFRT     F          ABC       TESTING      
Business 1        MANNTR    4567777              789999                80000
Business 2        FANNTR     6387999               789653               70000
Business 3        Bauctyr       4569877              369887               60000

I prefer to do this in SSIS.  Any other alternative is good.  I just need the output urgently
0
Comment
Question by:Nana Hemaa
  • 2
  • 2
5 Comments
 
LVL 22

Assisted Solution

by:plusone3055
plusone3055 earned 250 total points
ID: 39853669
just use a union

SELECT      'header1' AS header1
                  ,'header2' AS header2
                  ,'header3' AS header3
                  ,'header4' AS header4
                  ,'header5' AS header5
                 ,'header6' AS header6
UNION ALL
SELECT column1
             ,column2
            ,column3
           ,column4
          ,column5
         ,column6
FROM TABLE
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 39853704
I've done this multiple times in the past, and I've pulled it off with a separate column that identifies the headers-detail-footer (sort_order), then sort on it but don't select it in the source of the data flow that creates your text file...
-- Header row
INSERT INTO YourTable (sort_order, column_type, detail) 
VALUES (1, 'H', 'here it comes...') 

-- Detail row
INSERT INTO YourTable (sort_order, column_type, detail) 
SELECT 2, 'D', big_honking_column
FROM some_table

-- Footer row
INSERT INTO YourTable (sort_order, column_type, detail) 
VALUES (3, 'F', 'there it was') 

-- Now use this as the source of the pump to the text file..
SELECT column_type, detail
FROM YourTable
ORDER BY sort_order

Open in new window

0
 

Author Comment

by:Nana Hemaa
ID: 39853705
Tried union before but got an error because the number of columns in the header does not match the details.  20 columns in the header and the details just about 5 or 6 columns
0
 
LVL 22

Assisted Solution

by:plusone3055
plusone3055 earned 250 total points
ID: 39853761
Abonsu,

then to fix that you create 20 headers..  label the ones that you need and label 'noHeader" for hte ones you don't so they match up :)
0
 

Author Comment

by:Nana Hemaa
ID: 39853807
Jim--did you use SSIS to accomplish your output--my columns do not match as nicely as yours above--

The header information is now in a text file with 1 line --header.txt
ABCDEFPASS          2345uuNA          ABCDEFG        ABCGFRT     F          ABC       TESTING    

Details in table B
Business 1        MANNTR    4567777              789999                80000
Business 2        FANNTR     6387999               789653               70000
Business 3        Bauctyr       4569877              369887               60000


I have created a flat file source to point to header.txt
An Ole Db Source to Table B
A flat file destination to point to Output.txt

Now what I am having issues with is how to get the header information and details information to Output.txt since the coulmns do not match
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is about my first experience with programming Arduino.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now