256-byte limit for space delimited text (*.prn) files

[steveshoe]

In my job I routinely need to save Excel spreadsheets in the 'space delimited text' format in order to upload their data to a mainframe for processing. I have to use this format because the mainframe is very picky about the data being lined up. For example, if a field starts in column 30 on the first record, it must start in column 30 in all the records

The problem I have with using the 'space delimited text' format (*.prn file) is that it is limited to a 256-byte record length. If I have a spreadsheet with rows longer than that then it cuts off the data beyond that length and puts it below the records containing the first 256 bytes. In essence this makes the file unusable on the mainframe

I have tried other '.txt' formats but they all shift data around based on how much of each column is actually used. Can you tell me if there is any way to get Excel to save '.prn' files so that one row equals one output record regardless of the 256-byte limit?

 

[Earl Kiosterud]

Steve,

Here's a possibility. The Text Write Program at www.tushar-mehta.com will
write records larger than 256 bytes. You can instruct it to not use field
delimiters, text qualifiers, and it'll write a fixed-length text file just
fine. I think is does have a 256-character limit in each field, but not for
the record.

You'll have to get your fields into fixed-length form. You can put this
nightmare in another sheet, and it will make a mirror of your sheet with the
lengths adjusted:

=IF(ISTEXT('Sheet 1'!A1),REPT(0,MAX(4-LEN(LEFT('Sheet
1'!A1,4)),0))&LEFT('Sheet 1'!A1,4),LEFT('Sheet 1'!A1,4)& REPT("
",MAX(4-LEN(LEFT('Sheet 1'!A1,4)),0)))

Paste it into A1, and copy it down and to the right with the fill handle.
It's set up for a field length of 4; change all the 4s in row 1 of each
column to the appropriate field length and copy down.

It pads spaces to the right of text that's less than the field length, and
truncates text that's larger. It it pads zeroes to the left of numbers to
get to the required field length. If the number has more digits than the
field length, it just lops them off; be careful with that.

I'm not sure if this one is well tested, so you should test it thoroughly.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

In my job I routinely need to save Excel spreadsheets in the 'space

delimited text' format in order to upload their data to a mainframe for
processing. I have to use this format because the mainframe is very picky
about the data being lined up. For example, if a field starts in column 30
on the first record, it must start in column 30 in all the records.

The problem I have with using the 'space delimited text' format (*.prn

file) is that it is limited to a 256-byte record length. If I have a
spreadsheet with rows longer than that then it cuts off the data beyond that
length and puts it below the records containing the first 256 bytes. In
essence this makes the file unusable on the mainframe.

I have tried other '.txt' formats but they all shift data around based on

how much of each column is actually used. Can you tell me if there is any
way to get Excel to save '.prn' files so that one row equals one output
record regardless of the 256-byte limit?

 

[Dave Peterson]

And another option for the 240 character limit:

You have a few choices (try against a copy of your worksheet):

I'd either concatenate the cell values into another column:

=LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00")

(You'll have to modify it to match what you want.)

Then I'd copy and paste to notepad and save from there. Once I figured out that
ugly formula, I kept it and just unhide that column when I wanted to export the
data.

If that doesn't work for you, maybe you could do it with a macro.

Here's a link that provides a macro:
http://google.com/[email protected]