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
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
2THIS IS THE SECOND RECORD SEGMENT
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
3THIS IS THE THIRD RECORD SEGMENT
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
4THIS IS THE FOURTH RECORD SEGMENT
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
5ROBERT A SMITH
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
6THIS IS THE SIXTH RECORD SEGMENT
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXROB
7ERT A SMITH
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

"danmcgov" <danmcgov@discussions.microsoft.com> wrote in message
news:9034BD8E-7880-477F-8055-F2EA7E107B94@microsoft.com...
> 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?

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+0DESCRIPTION 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" <danmcgov@discussions.microsoft.com> wrote in message
> news:9034BD8E-7880-477F-8055-F2EA7E107B94@microsoft.com...
> > 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?
>
>
>

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" <danmcgov@discussions.microsoft.com> wrote in message
news:407D520B-8843-4D2A-9D13-5ADD0A87457F@microsoft.com...
> 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+0DESCRIPTION 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" <danmcgov@discussions.microsoft.com> wrote in message
>> news:9034BD8E-7880-477F-8055-F2EA7E107B94@microsoft.com...
>> > 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?
>>
>>
>>

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" <danmcgov@discussions.microsoft.com> wrote in message
> news:407D520B-8843-4D2A-9D13-5ADD0A87457F@microsoft.com...
> > 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+0DESCRIPTION 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" <danmcgov@discussions.microsoft.com> wrote in message
> >> news:9034BD8E-7880-477F-8055-F2EA7E107B94@microsoft.com...
> >> > 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?
> >>
> >>
> >>
>
>
>

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" <danmcgov@discussions.microsoft.com> wrote in message
news:3471656F-2802-4F38-8FF0-E109D72C26F9@microsoft.com...
> 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" <danmcgov@discussions.microsoft.com> wrote in message
>> news:407D520B-8843-4D2A-9D13-5ADD0A87457F@microsoft.com...
>> > 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+0DESCRIPTION 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" <danmcgov@discussions.microsoft.com> wrote in message
>> >> news:9034BD8E-7880-477F-8055-F2EA7E107B94@microsoft.com...
>> >> > 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?
>> >>
>> >>
>> >>
>>
>>
>>