SolvedPrivate

Append header to data

Posted on 2014-02-12
5
28 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

758 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

22 Experts available now in Live!

Get 1:1 Help Now