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.

--
jeff

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.

--
Regards,
Tom Ogilvy

"jeffP" wrote in message
...
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.

--
jeff

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.

--
jeffP



"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"jeffP" wrote in message
...
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.

--
jeff

Tom,
With your help I'm getting this project to work. Thanks.
Adjust the formula if you want to left pad the value.<<<<<
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.

--
jeffP


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"jeffP" wrote in message
...
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.

--
jeff

=REPT(" ",4) & TEXT(A1,"000000.00")

will put 4 spaces, then the number in A1 (formatted with 2 decimals).



jeffP wrote:

Tom,
With your help I'm getting this project to work. Thanks.
Adjust the formula if you want to left pad the value.<<<<<
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.

--
jeffP


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"jeffP" wrote in message
...
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.

--
jeff






--

Dave Peterson

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

--
Regards,
Tom Ogilvy


"jeffP" wrote in message
...
Tom,
With your help I'm getting this project to work. Thanks.
Adjust the formula if you want to left pad the value.<<<<<
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.

--
jeffP


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"jeffP" wrote in message
...
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.

--
jeff

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.

--
jeffP


"Tom Ogilvy" wrote in message
...
=rept(" ",20-len(Sheet1!A1)) & Sheet1!A1

--
Regards,
Tom Ogilvy


"jeffP" wrote in message
...
Tom,
With your help I'm getting this project to work. Thanks.
Adjust the formula if you want to left pad the value.<<<<<
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.

--
jeffP


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"jeffP" wrote in message
...
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.

--
jeff

=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")

--
Regards,
Tom Ogilvy


"jeffP" wrote in message
...
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.

--
jeffP


"Tom Ogilvy" wrote in message
...
=rept(" ",20-len(Sheet1!A1)) & Sheet1!A1

--
Regards,
Tom Ogilvy


"jeffP" wrote in message
...
Tom,
With your help I'm getting this project to work. Thanks.
Adjust the formula if you want to left pad the value.<<<<<
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.

--
jeffP


"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"jeffP" wrote in message
...
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.

--
jeff