create flat file export with dts, different column lengths

create flat file export with dts, different column lengths

Post by victoria richard » Thu, 30 Aug 2001 00:12:09



Hi

I need to create a fixed length text file with no delimeters. certain rows of the export have different specifications ie:

each row will always be 320 char

row 1 will be have the first column will be 10 char, the second 30 char etc.

row 2 will have the first column of 5 char and the second row 50 char etc

how can i create  an output file with different fixed length columns for each row?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

create flat file export with dts, different column lengths

Post by HSali » Thu, 30 Aug 2001 00:51:35


Victoria,
I am assuming you have two recordsets that you want to interlace, typically
these are in header/detail fashion.

use an activescript task using the Filesystemobject and two recordsets and
follow this pseudocode
Flesh out and amend the code as needed.

do while not HdrRs.EOF
    textfile.writeline HdrRs. Columns ( use a padding subroutine if needed)
    fetch DtlRs where Dtl.keys = HdrRs.Keys
    do while not DtlRs.EOF
        textfile.writeline DtlRs. Columns ( use a padding subroutine if
needed)
        dtlRs.MoveNext
    loop
    HdrRs.MoveNext
loop

Does That make sense?

Another approach is kinda tricky:
create a third recordset in such a way that you could append both header and
detail recordsets to it,
then retrieve header and detail recordsets
append to third recordset and
write it all out in one pass.

Hope that helps

Regards
Habib


Quote:> Hi

> I need to create a fixed length text file with no delimeters. certain rows

of the export have different specifications ie:
Quote:

> each row will always be 320 char

> row 1 will be have the first column will be 10 char, the second 30 char
etc.

> row 2 will have the first column of 5 char and the second row 50 char etc

> how can i create  an output file with different fixed length columns for
each row?

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!


 
 
 

1. DTS export to a flat file with float columns causes crash (SQL2000 - SP3)

Hiya!

I am trying to export a table from a SQL 2000 Server (SP3) (with 3
integer and 250 float columns) to a flat CSV file.

I am using the graphical DTS package designer in the Enterprise
Manager. I insert the SQLDB connection, the flat file, and the Data
Transformation (DataPump).

I choose the source Table, want to set the destination options, get a
list with the column names, klick on EXECUTE to add them, and NADA.

Wenn I click on "define columns" the Enterprise manager crashes.

This means that I can't define transformations!!!

The same thing on a table with varchar, etc. columns works perfectly.
I got it even running on a SQL 2000 server with SP2.

Hope you know what my prob is... HELP

2. SQL Statement Help

3. Exporting Different Tables to Same Flat File

4. Need help capturing ingres queries - Clarification

5. Accessing Flat files through DTS Flat File OLE DB Provider

6. table-level lock or row-level lock

7. Flat File -- Different Column Counts

8. Use of quotename()

9. Problem with DTS export to flat file

10. Urgent : DTS exporting to a Flat file

11. Creating a Flat File Export

12. DTS Fixed Length File Export Issue

13. Best way to query a flat fixed length text file