Importing file with fixed width, multi-line records
 

I'm looking for a way in Excel (even if I need to purchase an add-on) to
import .txt files that have fixed record lengths (generally 101 bytes) but
have multi-line records. The field lengths are variable and can sometimes
carry into the next record. Something like this:

1THIS IS THE FIRST RECORD SEGMENT
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXX
2THIS IS THE SECOND RECORD SEGMENT
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXX
3THIS IS THE THIRD RECORD SEGMENT
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXX
4THIS IS THE FOURTH RECORD SEGMENT
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXX
5ROBERT A SMITH
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
6THIS IS THE SIXTH RECORD SEGMENT
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXROB
7ERT A SMITH
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Importing file with fixed width, multi-line records
 

"danmcgov" wrote in message
...
I'm looking for a way in Excel (even if I need to purchase an add-on) to
import .txt files that have fixed record lengths (generally 101 bytes) but
have multi-line records. The field lengths are variable and can sometimes
carry into the next record. Something like this:

Because you said they "sometimes carry into the next record", I wonder why
ALL of them don't. You didn't mention where the files originated (what
software made them, in other words). Database report generators are pretty
orderly things, and generally do what they're told to. If they're told to
put each record on two lines, they'll do it all the time, not just some of
the time. If told to put them on one line, then that's what they do, unless
something fishy's going on.

So.....some questions:

1) Have you opened one of these text files in a straight text editor, and
checked to be absolutely sure that word wrap isn't turned on? By text
editor, I mean Notepad, or if the file's too large for Notepad, then
something like Wordpad or NoteTab (available for free at www.download.com)?

2) Do you have any control over how the original text files are created? If
not, can you communicate with the person who's creating them and find out
what software they originate with?

Importing file with fixed width, multi-line records
 

Ultimately, they are sourced from an IBM Mainframe. Below is another example
(Notepad). Generally, the first byte of each record identifies that segment
(4 segments here). I'm looking for something that will allow me to plug in
the parameters (For example; 'Last Name' found in record 2, position 12-35,
'Filler' in record 2 36-37..etc) and easily see the data in columns. That's
easy enough when the records are strung out in one long string but not so
easy with mult-line records.

1010130QXY0000071
JNLXXXXXX05060705060720000000000000+00001602000+0D ESCRIPTION LINE1
2DESCRIPTION LINE2 DESCRIPTION LINE3 DESCRIPTION LINE4 DESCRIPTION
LINE5 DESCRIPTION LINE6
3DESCRIPTION LINE7 DESCRIPTION LINE8 DESCRIPTION LINE9 000000
000000000+
40000000+ XXXXXROE AGENCY
9QXY00
"Doug Kanter" wrote:

"danmcgov" wrote in message
...
I'm looking for a way in Excel (even if I need to purchase an add-on) to
import .txt files that have fixed record lengths (generally 101 bytes) but
have multi-line records. The field lengths are variable and can sometimes
carry into the next record. Something like this:

Because you said they "sometimes carry into the next record", I wonder why
ALL of them don't. You didn't mention where the files originated (what
software made them, in other words). Database report generators are pretty
orderly things, and generally do what they're told to. If they're told to
put each record on two lines, they'll do it all the time, not just some of
the time. If told to put them on one line, then that's what they do, unless
something fishy's going on.

So.....some questions:

1) Have you opened one of these text files in a straight text editor, and
checked to be absolutely sure that word wrap isn't turned on? By text
editor, I mean Notepad, or if the file's too large for Notepad, then
something like Wordpad or NoteTab (available for free at www.download.com)?

2) Do you have any control over how the original text files are created? If
not, can you communicate with the person who's creating them and find out
what software they originate with?

Importing file with fixed width, multi-line records
 

OK - I receive files like this all day long from various IBM mainframes. I'm
not doubting that what you see is some records which seem to occupy two
lines. My question is this: What are you viewing the text file with when you
see two lines in some records? Which text editor/viewer? I'm asking because
I've ONLY seen this happen when my text editor made things look this way. As
soon as I fixed what the editor was doing wrong, everything reverted to one
line per record.

So, how are you viewing them?


"danmcgov" wrote in message
...
Ultimately, they are sourced from an IBM Mainframe. Below is another
example
(Notepad). Generally, the first byte of each record identifies that
segment
(4 segments here). I'm looking for something that will allow me to plug
in
the parameters (For example; 'Last Name' found in record 2, position
12-35,
'Filler' in record 2 36-37..etc) and easily see the data in columns.
That's
easy enough when the records are strung out in one long string but not so
easy with mult-line records.

1010130QXY0000071
JNLXXXXXX05060705060720000000000000+00001602000+0D ESCRIPTION LINE1
2DESCRIPTION LINE2 DESCRIPTION LINE3 DESCRIPTION LINE4 DESCRIPTION
LINE5 DESCRIPTION LINE6
3DESCRIPTION LINE7 DESCRIPTION LINE8 DESCRIPTION LINE9 000000
000000000+
40000000+ XXXXXROE
AGENCY
9QXY00
"Doug Kanter" wrote:

"danmcgov" wrote in message
...
I'm looking for a way in Excel (even if I need to purchase an add-on)
to
import .txt files that have fixed record lengths (generally 101 bytes)
but
have multi-line records. The field lengths are variable and can
sometimes
carry into the next record. Something like this:

Because you said they "sometimes carry into the next record", I wonder
why
ALL of them don't. You didn't mention where the files originated (what
software made them, in other words). Database report generators are
pretty
orderly things, and generally do what they're told to. If they're told to
put each record on two lines, they'll do it all the time, not just some
of
the time. If told to put them on one line, then that's what they do,
unless
something fishy's going on.

So.....some questions:

1) Have you opened one of these text files in a straight text editor, and
checked to be absolutely sure that word wrap isn't turned on? By text
editor, I mean Notepad, or if the file's too large for Notepad, then
something like Wordpad or NoteTab (available for free at
www.download.com)?

2) Do you have any control over how the original text files are created?
If
not, can you communicate with the person who's creating them and find out
what software they originate with?

Importing file with fixed width, multi-line records
 

It's Notepad. They are sent to me as a .dat or .txt file.
I'm probably not making myself as clear as possible. Each record is 101
bytes long. If there are 4 segments of 101 byte records (of related data),
and the field locations are different for each 101 'chunk', Excel's Wizard
will parse the first line fine. Of course, the fields on the 2nd (+3rd+4th)
line are in different positions so they don't parse correctly. (It just so
happens that a single piece of data will span two lines but that's not my
real problem).

Yes, I could manually cut/paste line 2, 3, and 4 together (after 1) then use
the wizard, but that's time consuming. I want to build a template of
parameters for each file structure, once.

I appreciate your time and input.


"Doug Kanter" wrote:

OK - I receive files like this all day long from various IBM mainframes. I'm
not doubting that what you see is some records which seem to occupy two
lines. My question is this: What are you viewing the text file with when you
see two lines in some records? Which text editor/viewer? I'm asking because
I've ONLY seen this happen when my text editor made things look this way. As
soon as I fixed what the editor was doing wrong, everything reverted to one
line per record.

So, how are you viewing them?


"danmcgov" wrote in message
...
Ultimately, they are sourced from an IBM Mainframe. Below is another
example
(Notepad). Generally, the first byte of each record identifies that
segment
(4 segments here). I'm looking for something that will allow me to plug
in
the parameters (For example; 'Last Name' found in record 2, position
12-35,
'Filler' in record 2 36-37..etc) and easily see the data in columns.
That's
easy enough when the records are strung out in one long string but not so
easy with mult-line records.

1010130QXY0000071
JNLXXXXXX05060705060720000000000000+00001602000+0D ESCRIPTION LINE1
2DESCRIPTION LINE2 DESCRIPTION LINE3 DESCRIPTION LINE4 DESCRIPTION
LINE5 DESCRIPTION LINE6
3DESCRIPTION LINE7 DESCRIPTION LINE8 DESCRIPTION LINE9 000000
000000000+
40000000+ XXXXXROE
AGENCY
9QXY00
"Doug Kanter" wrote:

"danmcgov" wrote in message
...
I'm looking for a way in Excel (even if I need to purchase an add-on)
to
import .txt files that have fixed record lengths (generally 101 bytes)
but
have multi-line records. The field lengths are variable and can
sometimes
carry into the next record. Something like this:

Because you said they "sometimes carry into the next record", I wonder
why
ALL of them don't. You didn't mention where the files originated (what
software made them, in other words). Database report generators are
pretty
orderly things, and generally do what they're told to. If they're told to
put each record on two lines, they'll do it all the time, not just some
of
the time. If told to put them on one line, then that's what they do,
unless
something fishy's going on.

So.....some questions:

1) Have you opened one of these text files in a straight text editor, and
checked to be absolutely sure that word wrap isn't turned on? By text
editor, I mean Notepad, or if the file's too large for Notepad, then
something like Wordpad or NoteTab (available for free at
www.download.com)?

2) Do you have any control over how the original text files are created?
If
not, can you communicate with the person who's creating them and find out
what software they originate with?

Importing file with fixed width, multi-line records
 

OK....humor me for a moment. Open the file in Wordpad, which comes with
Windows. Not Word, and not Notepad. After opening in Wordpad, if you still
see more than one line per record, click View, Options, and be sure "No
Wrap" is selected.

If you don't have Wordpad for some reason, go to www.download.com, search
for NoteTab, grab the free version, and open your file with that software.
It is safe to download, and won't mess up anything on your computer.

Let me know what happens. I might be wrong, but there's only one way to find
out.


"danmcgov" wrote in message
...
It's Notepad. They are sent to me as a .dat or .txt file.
I'm probably not making myself as clear as possible. Each record is 101
bytes long. If there are 4 segments of 101 byte records (of related data),
and the field locations are different for each 101 'chunk', Excel's Wizard
will parse the first line fine. Of course, the fields on the 2nd
(+3rd+4th)
line are in different positions so they don't parse correctly. (It just
so
happens that a single piece of data will span two lines but that's not my
real problem).

Yes, I could manually cut/paste line 2, 3, and 4 together (after 1) then
use
the wizard, but that's time consuming. I want to build a template of
parameters for each file structure, once.

I appreciate your time and input.


"Doug Kanter" wrote:

OK - I receive files like this all day long from various IBM mainframes.
I'm
not doubting that what you see is some records which seem to occupy two
lines. My question is this: What are you viewing the text file with when
you
see two lines in some records? Which text editor/viewer? I'm asking
because
I've ONLY seen this happen when my text editor made things look this way.
As
soon as I fixed what the editor was doing wrong, everything reverted to
one
line per record.

So, how are you viewing them?


"danmcgov" wrote in message
...
Ultimately, they are sourced from an IBM Mainframe. Below is another
example
(Notepad). Generally, the first byte of each record identifies that
segment
(4 segments here). I'm looking for something that will allow me to
plug
in
the parameters (For example; 'Last Name' found in record 2, position
12-35,
'Filler' in record 2 36-37..etc) and easily see the data in columns.
That's
easy enough when the records are strung out in one long string but not
so
easy with mult-line records.

1010130QXY0000071
JNLXXXXXX05060705060720000000000000+00001602000+0D ESCRIPTION LINE1
2DESCRIPTION LINE2 DESCRIPTION LINE3 DESCRIPTION LINE4
DESCRIPTION
LINE5 DESCRIPTION LINE6
3DESCRIPTION LINE7 DESCRIPTION LINE8 DESCRIPTION LINE9 000000
000000000+
40000000+ XXXXXROE
AGENCY
9QXY00
"Doug Kanter" wrote:

"danmcgov" wrote in message
...
I'm looking for a way in Excel (even if I need to purchase an
add-on)
to
import .txt files that have fixed record lengths (generally 101
bytes)
but
have multi-line records. The field lengths are variable and can
sometimes
carry into the next record. Something like this:

Because you said they "sometimes carry into the next record", I wonder
why
ALL of them don't. You didn't mention where the files originated (what
software made them, in other words). Database report generators are
pretty
orderly things, and generally do what they're told to. If they're told
to
put each record on two lines, they'll do it all the time, not just
some
of
the time. If told to put them on one line, then that's what they do,
unless
something fishy's going on.

So.....some questions:

1) Have you opened one of these text files in a straight text editor,
and
checked to be absolutely sure that word wrap isn't turned on? By text
editor, I mean Notepad, or if the file's too large for Notepad, then
something like Wordpad or NoteTab (available for free at
www.download.com)?

2) Do you have any control over how the original text files are
created?
If
not, can you communicate with the person who's creating them and find
out
what software they originate with?

Importing file with fixed width, multi-line records
 

The records wrapped at the same points in Wordpad and Notepad. Of course,
the files are forced to start a new record after 101 bytes.
Thanks

"Doug Kanter" wrote:

OK....humor me for a moment. Open the file in Wordpad, which comes with
Windows. Not Word, and not Notepad. After opening in Wordpad, if you still
see more than one line per record, click View, Options, and be sure "No
Wrap" is selected.

If you don't have Wordpad for some reason, go to www.download.com, search
for NoteTab, grab the free version, and open your file with that software.
It is safe to download, and won't mess up anything on your computer.

Let me know what happens. I might be wrong, but there's only one way to find
out.


"danmcgov" wrote in message
...
It's Notepad. They are sent to me as a .dat or .txt file.
I'm probably not making myself as clear as possible. Each record is 101
bytes long. If there are 4 segments of 101 byte records (of related data),
and the field locations are different for each 101 'chunk', Excel's Wizard
will parse the first line fine. Of course, the fields on the 2nd
(+3rd+4th)
line are in different positions so they don't parse correctly. (It just
so
happens that a single piece of data will span two lines but that's not my
real problem).

Yes, I could manually cut/paste line 2, 3, and 4 together (after 1) then
use
the wizard, but that's time consuming. I want to build a template of
parameters for each file structure, once.

I appreciate your time and input.


"Doug Kanter" wrote:

OK - I receive files like this all day long from various IBM mainframes.
I'm
not doubting that what you see is some records which seem to occupy two
lines. My question is this: What are you viewing the text file with when
you
see two lines in some records? Which text editor/viewer? I'm asking
because
I've ONLY seen this happen when my text editor made things look this way.
As
soon as I fixed what the editor was doing wrong, everything reverted to
one
line per record.

So, how are you viewing them?


"danmcgov" wrote in message
...
Ultimately, they are sourced from an IBM Mainframe. Below is another
example
(Notepad). Generally, the first byte of each record identifies that
segment
(4 segments here). I'm looking for something that will allow me to
plug
in
the parameters (For example; 'Last Name' found in record 2, position
12-35,
'Filler' in record 2 36-37..etc) and easily see the data in columns.
That's
easy enough when the records are strung out in one long string but not
so
easy with mult-line records.

1010130QXY0000071
JNLXXXXXX05060705060720000000000000+00001602000+0D ESCRIPTION LINE1
2DESCRIPTION LINE2 DESCRIPTION LINE3 DESCRIPTION LINE4
DESCRIPTION
LINE5 DESCRIPTION LINE6
3DESCRIPTION LINE7 DESCRIPTION LINE8 DESCRIPTION LINE9 000000
000000000+
40000000+ XXXXXROE
AGENCY
9QXY00
"Doug Kanter" wrote:

"danmcgov" wrote in message
...
I'm looking for a way in Excel (even if I need to purchase an
add-on)
to
import .txt files that have fixed record lengths (generally 101
bytes)
but
have multi-line records. The field lengths are variable and can
sometimes
carry into the next record. Something like this:

Because you said they "sometimes carry into the next record", I wonder
why
ALL of them don't. You didn't mention where the files originated (what
software made them, in other words). Database report generators are
pretty
orderly things, and generally do what they're told to. If they're told
to
put each record on two lines, they'll do it all the time, not just
some
of
the time. If told to put them on one line, then that's what they do,
unless
something fishy's going on.

So.....some questions:

1) Have you opened one of these text files in a straight text editor,
and
checked to be absolutely sure that word wrap isn't turned on? By text
editor, I mean Notepad, or if the file's too large for Notepad, then
something like Wordpad or NoteTab (available for free at
www.download.com)?

2) Do you have any control over how the original text files are
created?
If
not, can you communicate with the person who's creating them and find
out
what software they originate with?

Importing file with fixed width, multi-line records
 

OK. It looks like it the file was formatted like a mail merge. I'd ask for a
file with a different format - each record on one line only. If that's not
possible, post another message here, asking "How to step through records
sequentially". In some database software, it's called "scanning". I seem to
recall someone posting some VBA code here recently, which basically did
this:

1) Look at line 1
2) Look at line 2
3) Do something with text picked up from 1 & 2.
4) Step down, repeat 1 through 3.


"danmcgov" wrote in message
...
The records wrapped at the same points in Wordpad and Notepad. Of course,
the files are forced to start a new record after 101 bytes.
Thanks

"Doug Kanter" wrote:

OK....humor me for a moment. Open the file in Wordpad, which comes with
Windows. Not Word, and not Notepad. After opening in Wordpad, if you
still
see more than one line per record, click View, Options, and be sure "No
Wrap" is selected.

If you don't have Wordpad for some reason, go to www.download.com, search
for NoteTab, grab the free version, and open your file with that
software.
It is safe to download, and won't mess up anything on your computer.

Let me know what happens. I might be wrong, but there's only one way to
find
out.


"danmcgov" wrote in message
...
It's Notepad. They are sent to me as a .dat or .txt file.
I'm probably not making myself as clear as possible. Each record is
101
bytes long. If there are 4 segments of 101 byte records (of related
data),
and the field locations are different for each 101 'chunk', Excel's
Wizard
will parse the first line fine. Of course, the fields on the 2nd
(+3rd+4th)
line are in different positions so they don't parse correctly. (It
just
so
happens that a single piece of data will span two lines but that's not
my
real problem).

Yes, I could manually cut/paste line 2, 3, and 4 together (after 1)
then
use
the wizard, but that's time consuming. I want to build a template of
parameters for each file structure, once.

I appreciate your time and input.


"Doug Kanter" wrote:

OK - I receive files like this all day long from various IBM
mainframes.
I'm
not doubting that what you see is some records which seem to occupy
two
lines. My question is this: What are you viewing the text file with
when
you
see two lines in some records? Which text editor/viewer? I'm asking
because
I've ONLY seen this happen when my text editor made things look this
way.
As
soon as I fixed what the editor was doing wrong, everything reverted
to
one
line per record.

So, how are you viewing them?


"danmcgov" wrote in message
...
Ultimately, they are sourced from an IBM Mainframe. Below is another
example
(Notepad). Generally, the first byte of each record identifies that
segment
(4 segments here). I'm looking for something that will allow me to
plug
in
the parameters (For example; 'Last Name' found in record 2, position
12-35,
'Filler' in record 2 36-37..etc) and easily see the data in columns.
That's
easy enough when the records are strung out in one long string but
not
so
easy with mult-line records.

1010130QXY0000071
JNLXXXXXX05060705060720000000000000+00001602000+0D ESCRIPTION LINE1
2DESCRIPTION LINE2 DESCRIPTION LINE3 DESCRIPTION LINE4
DESCRIPTION
LINE5 DESCRIPTION LINE6
3DESCRIPTION LINE7 DESCRIPTION LINE8 DESCRIPTION LINE9 000000
000000000+
40000000+ XXXXXROE
AGENCY
9QXY00
"Doug Kanter" wrote:

"danmcgov" wrote in message
...
I'm looking for a way in Excel (even if I need to purchase an
add-on)
to
import .txt files that have fixed record lengths (generally 101
bytes)
but
have multi-line records. The field lengths are variable and can
sometimes
carry into the next record. Something like this:

Because you said they "sometimes carry into the next record", I
wonder
why
ALL of them don't. You didn't mention where the files originated
(what
software made them, in other words). Database report generators are
pretty
orderly things, and generally do what they're told to. If they're
told
to
put each record on two lines, they'll do it all the time, not just
some
of
the time. If told to put them on one line, then that's what they
do,
unless
something fishy's going on.

So.....some questions:

1) Have you opened one of these text files in a straight text
editor,
and
checked to be absolutely sure that word wrap isn't turned on? By
text
editor, I mean Notepad, or if the file's too large for Notepad,
then
something like Wordpad or NoteTab (available for free at
www.download.com)?

2) Do you have any control over how the original text files are
created?
If
not, can you communicate with the person who's creating them and
find
out
what software they originate with?