Fixed Width Records 1

[CNS4us]

I have a flat table that needs to get converted into a fixed width text file for a bank. They want a header record with mostly static info, detail records and a trailing record with totals.

Does anyone have any brilliant ideas on how to create such a beast??

Thanks in advance.

Specifics below.......
RECORD TYPE H (Header Record) Start Position Length Type
RECORD TYPE 1 1 Alphanumeric Always contains 'H'.
COMPANY NAME 2 16 Alphanumeric
STAT COMPANY CODE 18 10 Alphanumeric Always contains '7777777777'
EIN (COMPANY ID) 28 10 Alphanumeric Provided by Employer
EFFECTIVE ENTRY DATE 38 6 Date Format YYMMDD

RECORD TYPE D (Detail Record)
RECORD TYPE 1 1 Alphanumeric Always contains 'D'.
SOCIAL SECURITY NUMBER 2 9 Alphanumeric
AMOUNT 11 10 Numeric
NAME 21 22 Alphanumeric

RECORD TYPE F (Trailer Record)
RECORD TYPE 1 1 Alphanumeric Always contains 'F'.
EIN (COMPANY ID) 2 10 Alphanumeric
TOTAL ITEMS 12 10 Numeric
TOTAL CREDIT AMOUNT 22 12 Numeric

 

[SkipVought]

CNS4us,

Post a sample of each type of record.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[CNS4us]

I guess the records below would be what they want. I wonder if Excel is the best program to use for this. Ideally, the end user should just click a button and the macro should format it all and save it as a text file.

Thanks in advance!!!!!!!

::: SAMPLE :::

12345678901234567890123456789012345678901234567890
HABC Company 77777777771232222223040610
D5455566773355 Joe Lunchbucket
D5456677884388 Sally Shoebox
D54588990024458 Billy Bottomfeeder
F1232222223 32201

 

[SkipVought]

I would recommend Excel as a possibility.

You will have to construct a macro. Use the Macro Recorder.

1) Number each imported record, so that you can put it bck in order

2) Sort the records by type

3) Macro Record the parsing of each TYPE of record using Data/Text to Columns - fixed width.

4) Sort back into original sequence.

If you macro record each of these steps separately, they can be cleaned up and put together to form a procedure.

Post back if you need help.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[CNS4us]

I'm sorry Skip I must have mis-understood you. The records I posted are what the FINAL TEXT file should look like. The only records that I actually have in Excel would be the "D" records and those would be in columns like:

SS# Amt Name
545-55-6677 33.55 Joe Lunchbucket
545-66-7788 43.88 Sally Shoebox

My initial thought would be to create three separate spreadsheets because the different type of records have different column widths and counts. I don't have a problem cleaning the formatting, but would need help adding "spaces" when needed. After the counts and totals were calculated I would have to somehow combine all three spreadsheets into a single final text file.

Hope that clarifies it a bit. Thanks for your help!

 

[SkipVought]

OK - sorry. You want to convert an Excel sheet to fixed-width records.

For instance, your HEADER would be something like this
[tt]
="H"&A1&Left(BLANKS,Wid1-Len(A1))&B1Left(BLANKS,Wid2-Len(B1))&...
[/tt]
Your DETAIL would be something like this
[tt]
="D"&A2&Left(BLANKS,Wid1-Len(A2))&B2&Left(BLANKS,Wid2-Len(B2))&...
[/tt]
where
BLANKS is a Named Range that contains at least the number of spaces needed for the WIDEST column
Wid1 is a Named Range that contains the Width of COLUMN 1
Wid2 is a Named Range that contains the Width of COLUMN 2

Put BLANKS, Wid1, Wid2 etc on a separate sheet of named ranges

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

I failed to suggest that these formulas be formatted on a separate sheet in column A
[tt]
="H"&Sheet1!A1&Left(BLANKS,Wid1-Len(Sheet1!A1))&Sheet1!B1&Left(BLANKS,Wid2-Len(Sheet1!B1))&...
="D"&Sheet1!A2&Left(BLANKS,Wid1-Len(Sheet1!A2))&Sheet1!B2&Left(BLANKS,Wid2-Len(Sheet1!B2))&...
[/tt]
then save as THIS SHEET as a .prn file

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[CNS4us]

Wow!!! I am going to have to disect that a bit to understand it. Thanks so much for the help!!!!

 

[CNS4us]

Skip, thanks again!!!

That works like a charm! No more fear of those damn fixed width formats with headers, detail rows and trailers. I used to grimace and sweat in pure fear having to tame those wild tigers...now I smile and cheer. Bring it on!!!!!!!!

Ok, so I am a little dramatic. In any effect, thanks a bunch!

Cheers!