saveas fixed text for unix

  • Thread starter Thread starter jeffP
  • Start date Start date
J

jeffP

Hi all,
I've got a project that takes a *dbf file into Excel, does some clean up
etc, then I need to save it as a fixed length text file that's able to be
imported into a Unix system. I've got most of it done but now I can't get it
exported or saved into a fixed field length text file.I've worked w/ comma
delimited files but this has me lost.
I have 8 columns that need to be set w/ fixed character lengths even though
they hold varying length data. Then I need to export/save the sheet as a
text file for the Unix people.
As I mentioned , I'm really lost / stuck and any help is , as always,
greatly appreciated.
 
select all you cells and set the font as courier new (a mono spaced font).
the autofit your columns or manually adjust the width.

Then do file=>Save as and select Formatted Text (space delimited) (.prn)

to be more exact, you might go to a second sheet and put in formula in A1
like

=Sheet1!A1 & rept(" ",20-len(Sheet1!A1))



then dragfill down and across to capture your data.

Adjust the formula if you want to left pad the value.

then select all the cells and do Edit=>copy, then Edit=>Paste Special and
select values. This replaces the formulas.

then you can copy the sheet to a new workbook and save as above after
autofitting the columns.

An alternative is to use low level file io to write out the file a cell at a
time/a line at a time using space padding as above. Unix uses vblf as a
line terminator I believe, while windows uses vbcrlf. I assume Unix will be
able to handle the file, but if not, you will have to use code to terminate
with vblf.
 
Tom,
This is terrific help. It will take me a while before I can get it done but
thanks so much for taking the time to help on this oddball thing.
 
Tom,
With your help I'm getting this project to work. Thanks.I need to adjust the formula, but don't know how. As is it adds spaces to
the right of the existing number and I need it to fill on the left of the
number.
All your help is greatly appreciated.
 
=REPT(" ",4) & TEXT(A1,"000000.00")

will put 4 spaces, then the number in A1 (formatted with 2 decimals).
 
Tom,
Great help from you and also from Dave. I still can't exactly get what I
want , though. This gives me the perfect fixed length field that I need for
all my fields except the $ amount field. For that field I adjusted Dave's
suggestion =REPT(" ",4) & TEXT(A1,"000000.00")
to =REPT(" ",20-len(sheet1!a1)) & TEXT(sheet1!a1,"000000.00") and it formats
the
way I need but does not provide the fixed length.
I need the two decimal format but also the field filled left to 20
characters.
If you don't mind continuing to help, I always appreciate it.
 
=REPT(" ",20-len(TEXT(sheet1!a1,"000000.00"))) & TEXT(sheet1!a1,"000000.00")

or, since we know the format will be 9 characters wide

=REPT(" ",11) & TEXT(sheet1!a1,"000000.00")

if you don't want leading zeros

with $

=REPT(" ",20-len(TEXT(sheet1!a1,"$#,###.00"))) & TEXT(sheet1!a1,"$#,###.00")

Without $

=REPT(" ",20-len(TEXT(sheet1!a1,"#,###.00"))) & TEXT(sheet1!a1,"#,###.00")

or without $ and commas

=REPT(" ",20-len(TEXT(sheet1!a1,"#.00"))) & TEXT(sheet1!a1,"#.00")
 
And one more option--just to muddy the waters!

=RIGHT(REPT(" ",20)&TEXT(Sheet1!A1,"$#,###.00"),20)

Format the contents, add a bunch of spaces on the left and take the 20 rightmost
characters.
 
Tom and Dave,
You are unbelievable in talent and help. I can't thank you enough.
I had one thing more. I need to take a cell that's 2 place numeric w/
decimal (1234.56) and move it to a cell formatted text, 9 characters (left
fill) without the decimal point (it would be assumed 2 decimal places). I
couldn't get it formatted without loosing the last two digits so this is
what I did. It works fine but was there a simpler way?
=text(sheet1!A1*100,""000000000"")

Thanks again for your time and help
 
Back
Top