Convert Notepad to Excel

[maic15]

Hi All,
I have a notepad file that will not properly convert to Excel. Can anyone help?


DATE 10/01/05 611 NORTH SHORE UNIVERSITY HOSPITAL KLF PATIENT REVENUE AND USAGE STATISTICS ACC PER 09 PAGE 1

----TOTAL---- ----OTHER---- ---MEDICARE-- --TITLE XIX-- -BLUE CROSS- ---POINT---- ----POINT---- ----POINT----
FREQ REVENUE FREQ REVENUE FREQ REVENUE FREQ REVENUE FREQ REVENUE VALUE 1 VALUE 2 VALUE 3
************************************************************************************************************************************
611-0007 CVP CATH 14 OR 17 .0000 .0000 .0000
CUR 1 1 0 0 0
7.00 7.00 0.00 0.00 0.00 0.0000 0.0000 0.0000
IP 1 1 0 0 0
7.00 7.00 0.00 0.00 0.00 0.0000 0.0000 0.0000
OP 0 0 0 0 0
0.00 0.00 0.00 0.00 0.00 0.0000 0.0000 0.0000
YTD 4 4 0 0 0
28.00 28.00 0.00 0.00 0.00 0.0000 0.0000 0.0000
IP 4 4 0 0 0
28.00 28.00 0.00 0.00 0.00 0.0000 0.0000 0.0000
OP 0 0 0 0 0
0.00 0.00 0.00 0.00 0.00 0.0000 0.0000 0.0000
************************************************************************************************************************************
611-0008 CVP CATH 16 .0000 .0000 .0000
CUR 1 1 0 0 0
7.00 7.00 0.00 0.00 0.00 0.0000 0.0000 0.0000
IP 1 1 0 0 0
7.00 7.00 0.00 0.00 0.00 0.0000 0.0000 0.0000
OP 0 0 0 0 0
0.00 0.00 0.00 0.00 0.00 0.0000 0.0000 0.0000
YTD 4 4 0 0 0
28.00 28.00 0.00 0.00 0.00 0.0000 0.0000 0.0000
IP 4 4 0 0 0
28.00 28.00 0.00 0.00 0.00 0.0000 0.0000 0.0000
OP 0 0 0 0 0
0.00 0.00 0.00 0.00 0.00 0.0000 0.0000 0.0000
************************************************************************************************************************************
611-0009 CATH TRAY 14-16 .0000 .0000 .0000
CUR 51 39 0 1 11
255.00 195.00 0.00 5.00 55.00 0.0000 0.0000 0.0000
IP 51 39 0 1 11
255.00 195.00 0.00 5.00 55.00 0.0000 0.0000 0.0000
OP 0 0 0 0 0
0.00 0.00 0.00 0.00 0.00 0.0000 0.0000 0.0000
YTD 657 458 0 6 193
3,285.00 2,290.00 0.00 30.00 965.00 0.0000 0.0000 0.0000
IP 657 458 0 6 193
3,285.00 2,290.00 0.00 30.00 965.00 0.0000 0.0000 0.0000
OP 0 0 0 0 0
0.00 0.00 0.00 0.00 0.00 0.0000 0.0000 0.0000
************************************************************************************************************************************
611-0010 CATH TRAY FOLEY .0000 .0000 .0000
CUR 269 175 0 3 91
1,883.00 1,225.00 0.00 21.00 637.00 0.0000 0.0000 0.0000
IP 269 175 0 3 91
1,883.00 1,225.00 0.00 21.00 637.00 0.0000 0.0000 0.0000
OP 0 0 0 0 0
0.00 0.00 0.00 0.00 0.00 0.0000 0.0000 0.0000
YTD 2,506 1,628 3 70 805
17,542.00 11,396.00 21.00 490.00 5,635.00 0.0000 0.0000 0.0000
IP 2,506 1,628 3 70 805
17,542.00 11,396.00 21.00 490.00 5,635.00 0.0000 0.0000 0.0000
OP 0 0 0 0 0
0.00 0.00 0.00 0.00 0.00 0.0000 0.0000 0.0000
DATE 10/01/05 611 NORTH SHORE UNIVERSITY HOSPITAL KLF PATIENT REVENUE AND USAGE STATISTICS ACC PER 09 PAGE 2

----TOTAL---- ----OTHER---- ---MEDICARE-- --TITLE XIX-- -BLUE CROSS- ---POINT---- ----POINT---- ----POINT----
FREQ REVENUE FREQ REVENUE FREQ REVENUE FREQ REVENUE FREQ REVENUE VALUE 1 VALUE 2 VALUE 3
************************************************************************************************************************************
611-0029 PLEURAVAC ADULT .0000 .0000 .0000
CUR 1 0 1 0 0
144.00 0.00 144.00 0.00 0.00 0.0000 0.0000 0.0000
IP 1 0 1 0 0
144.00 0.00 144.00 0.00 0.00 0.0000 0.0000 0.0000
OP 0 0 0 0 0
0.00 0.00 0.00 0.00 0.00 0.0000 0.0000 0.0000
YTD 1 0 1 0 0
144.00 0.00 144.00 0.00 0.00 0.0000 0.0000 0.0000
IP 1 0 1 0 0
144.00 0.00 144.00 0.00 0.00 0.0000 0.0000 0.0000
OP 0 0 0 0 0
0.00 0.00 0.00 0.00 0.00 0.0000 0.0000 0.0000
************************************************************************************************************************************

 

[Greg Truby]

maic,

I've got to agree with Joe and Tushar.

1. This is obviously a case of print-report-output that's been dumped into a text file. As Tushar has already stated, parsing these is a pain. Furthermore it looks like the original source report is a double-row layout, when means that after you get it all parsed, you'll need to shuffle cells over into other columns and shift them up.
2. You state that the # of records is exceeding Excel's capacity, so now you've got another problem -- you're into Access, not Excel. This means that you can't use Access' built-in tools because those assume single-line layouts coming in.
3. So now you're looking at hand-coding a parser in Access. Judging by the questions you asked Joe, Joe is correct - this is plainly going to be quite a handful for you. You really need to go back to your boss and have IT give you a friendlier formatted output.

I hope all of this doesn't sound too harsh; but Joe, Tushar and I have learned that being forthright is kinder in the long run than throwing someone into something that is way over his head and watching him get more and more frustrated when a [simple] alteration to the input format would move you forward by leaps and bounds versus struggling and struggling to compensate for IT sticking you with a poor data design.