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
************************************************************************************************************************************

 

[Joe Was]

The problem is your file is using a SPACE as the field Delimiter and your Column titles contain spaces with in a title!

You need to make sure that each columns Title and Row values for that column do not contain spaces!
Like: FREQ_Revenue
or
FreqRevenue

Another solution is to use another character as a column Delimiter for your fields Like the "Pipe" character "|" or any other character that will not be part of the data. Change:

.TextFileOtherDelimiter = " "

To your Delimiter in the code below!

I have set this code to work with your current file, that is Column on each SPACE.

Note you will need to change the Drive:\Folder\FileName.txt in the code below as will as the:

.Name = "UserData"
To the name of your file as well:


Sub myImport()
'Standard Module code, like: Module1.

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;U:\Excel\Test\UserData.txt", Destination:=Range("A1"))
.Name = "UserData"
.AdjustColumnWidth = True
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileOtherDelimiter = " "
.Refresh BackgroundQuery:=False
End With

On Error Resume Next
ActiveCell.CurrentRegion.Select

For Each Cell In Selection
myVal = Trim(Cell.Value)
Cell.Value = myVal
Next Cell

Selection.Columns.AutoFit
Range("A1").Select
End Sub


This is the same code set up to use a PIPE Delimiter:

Sub myImport()
'Standard Module code, like: Module1.

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;U:\Excel\Test\TestPipe.txt", Destination:=Range("A1"))
.Name = "TestPipe"
.AdjustColumnWidth = True
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileOtherDelimiter = "|"
.Refresh BackgroundQuery:=False
End With

On Error Resume Next
ActiveCell.CurrentRegion.Select

For Each Cell In Selection
myVal = Trim(Cell.Value)
Cell.Value = myVal
Next Cell

Selection.Columns.AutoFit
Range("A1").Select
End Sub

 

[maic15]

So, do I import the TXT into Excel and then execute the code?

 

[Joe Was]

The program that is being used to build the TEXT file needs to be modified to work with a GOOD Delimiter the you Run the code from the Excel Workbook code Module. It is set up to do all the work!

Try the first one out on your existing file to see how this works, but the format of you text file now will not give you what you want but it will show you how the code works and how to fix it!

 

[maic15]

But if I import the file and set the delimeter to space, the record amounts exceed Excel

So to clarify,

Step 1:
Import UserData.txt file in to Excel.
Step 2:
Set Delimiters equal to space.
Step 3:
Execute Code

Are these steps correct?

 

[Joe Was]

No

Use the text file as is, do not import, just save it!

Save your Text file to a folder, make the posted code's file statement match your location and file name [Conection in the code above gets your file location and name]!

Then just run the code, do not import, the code does this for you!

 

[maic15]

Ok..
I saved the file to
C:\Documents and Settings\pbuonopane\Desktop\UserData.txt

How do I execute the code? I'm not sure I understand the code part. Do I need to create a commnad button in Excel?

 

[Joe Was]

To do what you need you are going to need a higher skill level than you have right now.

You are going to need to make changes to both your text file and to the posted code, that will need to be based upon your corrections and knowledge of the data. I don't think I will be able to help you given you current abilities.

Some one else who uses the Import Utility in Excel, may know a way around your import problem that I am missing?

 

[tusharm]

I don't see how you expect to get this data set into XL without writing a parser. It looks like you've converted a printed report into a text file. The information, scattered across multiple rows with each row containing different information, may be reader friendly but it is analysis hostile. The only solution I can see is to have each row parsed independent of the others.

From what I can tell, the first row is an account header containing the date of the report, the account name, etc. The next non-blank rows are column header rows. The next line is some kind of a procedure identifier (Catheter OR?). Then, the data are shown in groups with each group containing two rows of information. My guess is that each group refers to one patient type. In the segment I've quoted below there are 2 groups. The first is CUR (whatever that is) and the second is IP, which I presume is InPatient. For each of those the first row contains, in addition to the type of patient, frequency counts. The second line contains the revenue numbers and the 'point values,' whatever those are.

Trying to convert a printed report into an analysis-friendly format is always a PITA. Maybe, someone will write you a program that will parse the text file. It won't be me. And, I suspect that if someone does write one you will have to have them make several changes since the report will have all kinds of exceptions that are not immediately apparent.

You will be a lot better off connecting to the original data source and getting the data directly from it. Whether that is possible in your environment is a question only you can answer.

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
{snip}

 

[nbrcrunch]

Another quick-n-dirty way to approach this is to

1. make a copy of the text file.
2. Edit the copy and delete the header section seeing as that is what is causing the most discussion here. (You have other problems (as mentioned by tusharm) with this file but hopefully you can figure that out afterwards)
3. Save the file.
4. Import to Excel.
5. Manually type the headers.

I have a feeling that the file is most probably fixed length and not delimited. In that case, you can set your own parsing during import.