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:

19923098981489098747791230980983097200409040970387 7891023478700011000234

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.

Im not big on excel or data automation so Im here for some guidance please.

Thanks

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.

--
Regards,
Tom Ogilvy

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

19923098981489098747791230980983097200409040970387 7891023478700011000234

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

Personally I would be fine with the wizard, but Im 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. Ill try and clarify what I need. I need
something weather its 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 its done. It needs to be a quick conversion that requires the least
amount of user input and manipulation.

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

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

19923098981489098747791230980983097200409040970387 7891023478700011000234

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

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.

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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

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


19923098981489098747791230980983097200409040970387 7891023478700011000234

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

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.10...,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.
--
Edd


"BamaBrad" wrote:

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:

19923098981489098747791230980983097200409040970387 7891023478700011000234

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.

Im not big on excel or data automation so Im here for some guidance please.

Thanks