Lost columns in formated text (space delimited) save

[Jeff Clark]

I convert spreadsheets from Excel 2003 to a formatted text (space delimited)
..prn file for use in another program. I last used this function in October 07
without any problems. I tried to do this yesterday and the save did not
maintain the columnar spacing, instead it ran everything together, which made
the data unusable.

I tried doing a program repair from the original CD without any success.
What would cause this?

Thanks, jeff

 

[Dave Peterson]

I bet that the columns were not the same width as when you tested in October.

I like to use a non-proportional font (Courier New) and then I can see how
things may look--although checking the .prn file using your favorite Text editor
(NotePad???) is still required.

 

[Jeff Clark]

You are exactly right in the column width change. I use a new template each
time that comes to me with even/odd widths, depending on the data in each
column. After looking at many of the templates that I used last year, they
all have exactly .11 deducted from each columns width. This would make them
unusable at this time.

I just downloaded a fresh template and saved it as a workbook (3 tabs in
workbook and this is a normal type save option) and again find the columns
..11 narrower across the sheet.

Something has changed in the program to cause this deduction when saving the
master and has changed all of my old templates.

I am at a loss on this.

 

[Dave Peterson]

If the .columnwidth is off by .11, then I _think_ this could be caused by a
different printer driver. Maybe the current printer allows for more detail--or
maybe the opposite.

You may want to try changing to the old printer to see if that changes anything.

 

[Jeff Clark]

I haven't changed printers. This problem occurs on screen in the Excel
template and then when I save it to the formatted text (space delimited) prn
file the columns/data all runs together. When I open the template, the
columns are in the even/odd format and then when I save the template it
changes the columns by .11 on each column.

I recently downloaded the .net framework (beta version) and SQL programs to
run a different application. Could either of these programs cause this
problem?

 

[Jeff Clark]

I just did some research between my desktop and my laptop. The laptop
downloads and retains the template in its proper form, the desktop does not.

I went to tools/options/transition and found a difference in the setups. The
desktop has "transition formula evaluation" checked the laptop does not.

I unchecked the box and closed the program and tried the download again, but
it came in the same way and the box was checked again.

Both units have office basic 2003 SP3 installed. Could this be the source of
the problem?

I haven't changed printers. This problem occurs on screen in the Excel
template and then when I save it to the formatted text (space delimited) prn
file the columns/data all runs together. When I open the template, the
columns are in the even/odd format and then when I save the template it
changes the columns by .11 on each column.

I recently downloaded the .net framework (beta version) and SQL programs to
run a different application. Could either of these programs cause this
problem?

 

[Dave Peterson]

My next guess (and it's still a guess) is that it's related to the display
settings differences between the desktop and the laptop.

 

[Dave Clark]

My Dear Dave Peterson --

I heard of this thread and since this particular issue has been an
occasional thorn in my side for quite some time, I thought I would take a few
moments and expand on exactly what this problem is.

We have an elderly DOS-era application, which expects to be fed input data
which is constructed in a fixed-columnar layout. Originally this was being
done with simple text editors. But for a great many reasons, today we prefer
to have the users prepare their data in Excel instead, and then use the "Save
As (Formatted Text, space delimited)" process to export the data from excel
into a text file, that the application can pick up.

To support this process, we have prepared a standard Excel template file, to
be used for this purpose. One key point we make in the instructions, is for
the users to NOT change any of the column widths of the data, since of course
that would modify the resultant output layout and cause the destination
application to have fits.

In the main this process seems to work fine. But an occasional user, such
as Jeff Clark who started this thread, finds that the resultant text file
which they export, has had the columns "scrunched together", with each data
column exactly one space less than the original design intent. We have been
unable to identify what the cause or setting is that is resulting in this
behavior. And it seems not to be a function of the excel file itself. Jeff
can take a copy of one of my files and export it to formatted text on his
machine, and what he gets out is scrunched, whereas what I got from that file
on Excel on my machine is not scrunched. And conversely, I can take a file
from Jeff and put it on my machine and it exports properly on mine even
though it does not on his.

The only "symptom" we have been able to identify, is that when examined in
Excel on one of these troublesome Excel installations, that each data column
appears as 0.11 narrower than they appear as on the majority of other
machines, where the export works as expected.

This problem does not appear to be a function of any particular excel
version -- we have encountered this problem occasionally with Office 97
users, ditto for Office 2000, and now here's Jeff Clark using Office 2003 and
experiencing exactly this same phenomenon.

This has been going on long enough that I can hazard a statistical estimate,
which appears to be that something like one out of every 15-20 users runs
into this goofy problem. So it's not a common situation. The majority of
folks find this process works just fine.

But when this does crop up, it's annoying as the dickens, and we have no
clue what to do, other than suggest such users try exporting from somebody
else's machine, which typically does work OK.

If anyone can point us to exactly what is going on with this, and help us
figure out what the root cause is, that would be wonderful.

Thanx in advance for any insights that y'all can give.

Dave Clark.

 

[Dave Peterson]

I don't have any more guesses.

But I wouldn't rely on the columnwidths to define the width of each field. I'd
use some other technique.

Saved from a previous post:

There's a limit of 240 characters per line when you save as .prn files. So if
your data wouldn't create a record that was longer than 240 characters, you can
save the file as .prn.

I like to use a fixed width font (courier new) and adjust the column widths
manually. But this can take a while to get it perfect. (Save it, check the
output in a text editor, back to excel, adjust, save, and recheck in that text
editor. Lather, rinse, and repeat!)

Alternatively, you could 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.)

Drag it down the column to get all that fixed width stuff.

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]

 

[Jeff Clark]

Dave Petersons note about the display settings made me look at those settings
in the display/advanced settings. The desktop was set to 125 dpi (large)
while the laptop setting was normal. Changed the desktop to normal 96 dpi,
rebooted and evreything is back to where it should be on the column widths.

I upgraded to a 20" monitor in November and since I am old and blind I
chanfged the setting so that evrything would be bigger.

Thanks for the help, Jeff

 

[Dave Peterson]

That's one of the reasons I wouldn't trust the layout of my .prn file to excel's
columnwidths.

I'd use one of those other suggestions.