Export to TEXT file - Eliminate Blank spaces 1

[chamilz]

I have two tables which contain Header and Detail records. Header table has 12 columns with primary key and Detail table has 8 columns with secondary key. I need to join these tow tables in order to create TAB delimited TEXT file to feed another system.

Final TEXT files always should contain header record following the relevant detail records as bellow,

Col col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12
H des1 123 aa bb cc dd ee ff gg rr fgh
D 123 dd ff tt gg hh rr
D 123 tt thg hh gg ws kk
D 123 oo pp hh tt rr fd
H des2 889 aa bb cc dd ee ff gg rr fgh
D 889 gg tt gg hh rr
D 889 tt thg hh gg ws kk

H- Header record
D- Detail
123/889 – Keys

I created another Table and inserted HEADER and DETAIL records and sorted according to the required order. And I used DTS process to create TAB delimited file.

My problem is that final TEXT file has blank spaces for DETAIL records, columns 9, 10, 11 and 12. These blank spaces are driving me crazy. Third party system doesn’t accept blank spaces.

Could any one help me to find a solution to these blank spaces? Can I use some other techniques to eliminate the blank spaces?

Thanks in advance!

 

[SajidAttar]

Hi Chamilz,

It is happening because there are 4 columns empty in your Last Table.

try to use the following query to populate your final table

you have to make changes in the query with your actual column names and field names.

select KeyField, 0, convert( varchar,Col1) + char(9) +  convert( varchar,Col2) + char(9) + convert( varchar,Col3) + char(9) +  . . .convert( varchar,Col12)  from Header

union all 

select KeyField, 1, convert( varchar,Col1) + char(9) +  convert( varchar,Col2) + char(9) + convert( varchar,Col3) + char(9) +  . . .convert( varchar,Col8)  from details

order by 1,2

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[SajidAttar]

and possible give the code you are using to populate the FinalTable.

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[chamilz]

Thanks for your reply..

I will give a try and let you know the result
/Chamil

 

[chamilz]

This work like a charm...star for you
Thank you