Exporting to fixed length file

[Don C]

Our "state of the art" Bull mainframe does not accept uploads of Excel data. To upload files, I currently change each cell to fixed length, then append all the cells (using "&") into one cell. A copy and paste/values of that last worksheet can be saved as a text file and uploaded.

I now have a file with 50 some odd columns (including many blank cells) and 50,000 records. I'd prefer not to use my tried and true method. The resulting file will be HUGE until I do the final saveas, and with all those columns it is real possible to make an error. Also, converting blanks to text has its own set of problems.

Is there a solution to easily defining the field length for each column and then creating a fixed length record for uploading? I presume I could do this by passing the file through ACCESS, but I am not that comfortable with ACCESS and am looking for an Excel solution.

Thanks

 

[Legacy 98055]

Hi Don.
Easy to do though a bit slow perhaps.
For a code example, please list some detailed examples of your end data.
I'm assuming the datatypes for each column is "String"? If not, please indicate this.
What does your file looklike?
For ex.
Cell A1 contains "Don"
B1, "011871"
C1, "1122 Somewhere Ave"

Fixed length of 20 bytes for each record.

Does your file save as:
<pre>
"Don ","011871 ","1122 Somewhere Ave "

or

Don 011871 1122 Somewhere Ave

</pre>
or other???

Tom

 

[Don C]

The original data of one file runs in columns A through AU (47 columns) and includes identification numbers (e.g. Excel stores "135" but the Bull needs a nine character "000000135"), Last Name (25 characters), First Name (15 characters), a series of counts (two or three digits each), and a series of percents(which have to be multipied by 100 before converting to text).

Blanks are different from zeros, so when I convert each field to fixed length I have to test for an entry, putting spaces in for blanks.

Once each cell is converted to fixed length text, I then joint them into one record (=A2&B2&C2...) on a third worksheet. THAT worksheet is saved as a Text file. This process works, but is labor intensive and subject to error.

Then I have the File that goes out to column GB for 30,000 records which needs to be uploaded. It too has everything from numbers to text and just INVITES errors somewhere!

Thanks for the thoughts.

 

[Legacy 98055]

Ok Don.
If you wish, send me an example of the data for each of these files, as is, in Excel and then the end result expected in each of the text files. This is definitely a good candidate for a time saving click and go.
Tom