Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Convert Notepad to Excel

This is a discussion on Convert Notepad to Excel within the Excel Questions forums, part of the Question Forums category; Hi All, I have a notepad file that will not properly convert to Excel. Can anyone help? DATE 10/01/05 611 ...

  1. #1
    Board Regular
    Join Date
    Nov 2004
    Posts
    313

    Default Convert Notepad to Excel

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

  2. #2
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

    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
    JSW: Try and try again: "The way of the Coder!"

  3. #3
    Board Regular
    Join Date
    Nov 2004
    Posts
    313

    Default

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

  4. #4
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

    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!
    JSW: Try and try again: "The way of the Coder!"

  5. #5
    Board Regular
    Join Date
    Nov 2004
    Posts
    313

    Default

    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?

  6. #6
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

    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!
    JSW: Try and try again: "The way of the Coder!"

  7. #7
    Board Regular
    Join Date
    Nov 2004
    Posts
    313

    Default

    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?

  8. #8
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

    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?
    JSW: Try and try again: "The way of the Coder!"

  9. #9
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,922

    Default Re: Convert Notepad to Excel

    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.

    Quote Originally Posted by maic15
    Hi All,
    I have a notepad file that will not properly convert to Excel. Can anyone help?
    Code:
    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}

  10. #10
    Board Regular
    Join Date
    Jan 2003
    Location
    CA, Bay Area
    Posts
    2,062

    Default

    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.
    Give a man a fish & you've fed him once. Teach a man to fish & you feed him for a lifetime. Give a man a fish a day & he becomes a spoiled adult still living with his parents at 35.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com