Hi Brad
I have no idea how many columns you're talking about, but the way I did
these type of things in the past would be to use a seperate sheet to put
the result of this, and the only save that sheet to a CSV file. In order
to have columns with at least a certain number of characters in them, a
simple formula would do the trick.
If the workbook has the sheet called "MyData" where all your data is
stored, then insert another sheet and call that, say, "Export". Now if,
for example the sheet "Mydata" has 10 columns of data, and the diferent
columns need to be of width 10,10,16,13,12..etc, we first put a blank
cell, with the number of blanks equal to the widest width we need in
cell A1 of the sheet "Export". Simply press the space bar the required
number of times. To ensure that it is correct, enter the formula
=len(a1) in cell B1 and make sure that the result is what you needed.
One or two more will not matter. Now in the second row of the sheet
"Export" we enter the following formulae:
Cell A2: =right($A$1&MyData!Ax,10) ... where x is the row where the
data in "MyData" starts
Cell B2: =","&right($A$1&MyData!Bx,10)
Cell C2: =","&right($A$1&MyData!Cx,16)
Cell D2: =","&right($A$1&MyData!Dx,13)
... etc.
Then simply highlight row 2 and copy down for the number of rows needed.
Now, in order to use it, open Notepad. In the sheet "Export", highlight
from row 2 all the data and press <Ctrl>+<C>. In Notepad, press
<Ctrl>+<V> and your data will be there and can be saved. This is a CSV
file, with fixed column widths.
When using a mainframe, I think that the commas between the cells can be
left out, since you're working with fixed width columns, so that the
formulae for cells B2 and after could look just the same as that for
cell A2 (above). Your program on the mainframe, reading this data, just
have to take this into account.
Hope that helps.
At Neethling
Cape Town
-------- Original Message --------
Date: Thu, 27 Mar 2008 18:41:13 -0700
From: Brad <[log in to unmask]>
Subject: Excel question, Tony??
Is there a way to define fixed length cells within excel? For
example, I have a check# field that can be up to 16 bytes long. We
plan to convert the excel document to a .csv file and upload it to the
mainframe for processing. In this example, I need 16 positions
between the commas, padding unentered positions with spaces. Hope
that makes sense.
This is for a friend on another list that I use.
Much thanks!
Brad Loomis
San Miguel, CA
--
___________________________________________________________
QOTW: When I read about the evils of drinking,
I gave up reading.
-- Paul Hornung
Do you want to signoff PCSOFT or just change to
Digest mode - visit our web site:
http://freepctech.com/pcsoft.shtml
|