Converting Text files to Excel sheets with specific formatting

G

Guest

Converting Text files to Excel sheets with specific formatting

Hello, I have an issue with .txt files and excel. I have a text file that
is outputted from our mainframe that needs to be converted to an exact excel
format. The text output has tons of information that is not needed and hides
pertinent information within long strings of useless data. The text appears
something like this:

199230989814890987477912309809830972004090409703877891023478700011000234

The information that I would need out of the same string would be the
following parts:

19923098981489098747791230980983097\\\\\20040904\\\\\0970387789102347870001\\\\\\\1000234\\\\\
the 20040904 which is the date and 1000234 which is a test account number.

The text file consists of about 20 to 25 strings like this per line. Each
value needed would have to automatically fill a cell in a named column.

I’m not big on excel or data automation so I’m here for some guidance please.

Thanks
 
T

Tom Ogilvy

This is fixed width data. Open the file in Excel and the text import wizard
should appear. You can choose fixed width, then on the next dialog,
designate where the columns should be separated.

On the third dialog, you can instruct excel how to handle specific columns
(such as specify date format or skip the column).

If you want a macro, turn on the macro recorder while you do it manually.
 
G

Guest

Personally I would be fine with the wizard, but I’m trying to make this
automated for the end user. I tried showing the users how to use the wizard,
but it is to time consuming. I’ll try and clarify what I need. I need
something weather it’s a script, and third party program, conversion utility,
etc…. to take the text data and convert the fixed width information to a
usable sheet. My absolute wish would be for a user to click something and
boom it’s done. It needs to be a quick conversion that requires the least
amount of user input and manipulation.
 
T

Tom Ogilvy

Let me clarify it one more time for you:
I told you, if you want a macro, turn on the macro recorder while you do it
manually. Then you will have your script. There aren't any clairvoyant
programs. You will have to set up the specifications at least once.
 
G

Guest

We do this quite often. We have an automated mainframe process that sends an
email to our users with a link that opens a mainframe data set in Excel. The
link is:
HTTP://SDCVM01.CGS.COM:82/~ITSWEB.1...PREAD(0)&LENGTHS=3,24,24,4,2,12,18,13,11,8,60
Where the first part (HTTP thru 102/) is the server ID, FLC is a mainframe
command to view a data set (we use CMS here), and &LENGTHS= gives the Excel
column widths (that is, in the example above the first column data is 3
characters, 2nd col is 24, 3rd col is 24, etc.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top