Converting an Excel file to fixed length ASCII (text) file

[Guest]

I would like to convert an Excel table to a fixed length ASCII file for data
transfer. Is this possible with Excel XP or an available download?

 

[Myrna Larson]

File/Save As and select the type Formatted Text (space delimited) (.prn)

Beware of the following caveats (from Help for XL 2002). I have no idea what
they mean by "Columns of data are separated by commas". If these limitations
affect you, you may need to write VBA code to create your text file. That
would not be limited to 240-character lines.

"The Formatted Text (*.prn) format saves only the text and values as they are
displayed in cells of the active worksheet.

"All rows are saved. If a row of cells contains more than 240 characters,
characters beyond 240 wrap to a new line at the end of the converted file. For
example, if the rows 1 through 10 all contain more than 240 characters, the
remaining text in row 1 is placed in row 11, the remaining text in row 2 is
placed in row 12, and so on.

"Columns of data are separated by commas, and each row of data ends in a
carriage return. If cells display formulas instead of formula values, the
formulas are converted as text. All formatting, graphics, objects, and other
worksheet contents are lost. The euro symbol will be converted to a question
mark.

"Before saving a worksheet in this format, make sure all data that you want
converted is visible and that there is adequate spacing between the columns.
Otherwise, data may be lost or not properly separated in the converted file.
You may need to adjust the column widths of the worksheet."

 

[Dave Peterson]

On top of what Myrna posted:

If I'm going to save a file as .prn, I'll change the font for all the cells to
Courier New.

Then I can adjust the columnwidths the way I like (and use my favorite text
editor to check how it worked!).


Saved from a previous post:


There's a limit of 240 characters per line when you save as .prn files.

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]