SolvedPrivate

Append header to data

Posted on 2014-02-12
5
32 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

739 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