Trying to build a fixed width file for importing

  • Thread starter Thread starter WF
  • Start date Start date
W

WF

I am trying to build a fixed width file In Excel to import into another program. The records are 252 (not sure what to call it) bits? of information long, in several fields. When I build a couple of records for testing according to the file specs from the program, I am saving the file as an Excel "space delimited" .PRN file using Courier font. So far, so good. However, when I open the file to check it using Notebook I see that the record has "wrapped" into two lines. I open a similar file that was downloaded and created from an AS/400 (using Notebook) and the file does not wrap. The test file that I am loading into the program is not being successfully applied. My guess is that the "wrapping" of the record is the cause.

Would anyone know what I might do to save this record in an Excel spreadsheet without wrappping?

I sincerely appreciate anyone's help on this problem.

Thank you,

WF
 
First, this is pretty much a plain text newsgroup. Most would appreciate if you
turned off the HTML stuff.

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]
 
Thanks to you both for your input. I'm afraid both of your answers are a
bit above my skill level in Excel. I was hoping for a more simple solution.
I guess I'll have to buy a little help on this one. I was hoping to be able
to do this myself.

Many thanks for your help.

WF
 
WF,

I can send you a workbook with formulas that will make a mirror of your sheet, properly padded to the field sizes you specify for each column. If the cell contains text, it'll right-pad it with spaces; if a number, it left-pads it with zeroes. If the cell has more characters than the specified field size, it lops it off, unless it's a number, in which case it says "Error."

Then you save it using the Text Write Program at www.smokeylake.com/excel, specifying no field delimiter.

The formula looks like this

=IF(ISTEXT(Data!A1), LEFT(Data!A1,FldLen!A$1) & REPT(" ", MAX(0, FldLen!A$1 - LEN(Data!A1))), REPT("0", MAX(0, FldLen!A$1 - LEN(Data!A1))) & IF(LEN(Data!A1)<=FldLen!A$1, Data!A1, "~~~Err~~~"))

Your sheet is named "Data" and there's a sheet called FldLen that has the field lengths you want for each column in the first row.

I had a better one, but it was on the F drive of my old machine, I think. That drive quit working.

My "encoded" (wink wink) address is below. Don't mention it in any posts -- the spammers would pick it up, and then I'd have to send my boys to your place.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

I am trying to build a fixed width file In Excel to import into another program. The records are 252 (not sure what to call it) bits? of information long, in several fields. When I build a couple of records for testing according to the file specs from the program, I am saving the file as an Excel "space delimited" .PRN file using Courier font. So far, so good. However, when I open the file to check it using Notebook I see that the record has "wrapped" into two lines. I open a similar file that was downloaded and created from an AS/400 (using Notebook) and the file does not wrap. The test file that I am loading into the program is not being successfully applied. My guess is that the "wrapping" of the record is the cause.

Would anyone know what I might do to save this record in an Excel spreadsheet without wrappping?

I sincerely appreciate anyone's help on this problem.

Thank you,

WF
 
Earl,

Thank you for your generoursity. I'll be in touch.

WF
WF,

I can send you a workbook with formulas that will make a mirror of your
sheet, properly padded to the field sizes you specify for each column. If
the cell contains text, it'll right-pad it with spaces; if a number, it
left-pads it with zeroes. If the cell has more characters than the
specified field size, it lops it off, unless it's a number, in which case it
says "Error."

Then you save it using the Text Write Program at www.smokeylake.com/excel,
specifying no field delimiter.

The formula looks like this

=IF(ISTEXT(Data!A1), LEFT(Data!A1,FldLen!A$1) & REPT(" ", MAX(0,
FldLen!A$1 - LEN(Data!A1))), REPT("0", MAX(0, FldLen!A$1 - LEN(Data!A1))) &
IF(LEN(Data!A1)<=FldLen!A$1, Data!A1, "~~~Err~~~"))

Your sheet is named "Data" and there's a sheet called FldLen that has the
field lengths you want for each column in the first row.

I had a better one, but it was on the F drive of my old machine, I think.
That drive quit working.

My "encoded" (wink wink) address is below. Don't mention it in any posts --
the spammers would pick it up, and then I'd have to send my boys to your
place.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

I am trying to build a fixed width file In Excel to import into another
program. The records are 252 (not sure what to call it) bits? of
information long, in several fields. When I build a couple of records for
testing according to the file specs from the program, I am saving the file
as an Excel "space delimited" .PRN file using Courier font. So far, so good.
However, when I open the file to check it using Notebook I see that the
record has "wrapped" into two lines. I open a similar file that was
downloaded and created from an AS/400 (using Notebook) and the file does not
wrap. The test file that I am loading into the program is not being
successfully applied. My guess is that the "wrapping" of the record is the
cause.

Would anyone know what I might do to save this record in an Excel
spreadsheet without wrappping?

I sincerely appreciate anyone's help on this problem.

Thank you,

WF
 
You might want to make sure that wordwrap is not being set when viewing your
file.

I am trying to build a fixed width file In Excel to import into another
program. The records are 252 (not sure what to call it) bits? of
information long, in several fields. When I build a couple of records for
testing according to the file specs from the program, I am saving the file
as an Excel "space delimited" .PRN file using Courier font. So far, so good.
However, when I open the file to check it using Notebook I see that the
record has "wrapped" into two lines. I open a similar file that was
downloaded and created from an AS/400 (using Notebook) and the file does not
wrap. The test file that I am loading into the program is not being
successfully applied. My guess is that the "wrapping" of the record is the
cause.

Would anyone know what I might do to save this record in an Excel
spreadsheet without wrappping?

I sincerely appreciate anyone's help on this problem.

Thank you,

WF
 
Back
Top