Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract specific text from a text file (Excel 2003)

    Hi all

    I have some text files as attached, I need to extract certain values, namely Name, Account No, total amount
    under Portfolio Value in the text file and copy these values to a worksheet under column: Name, Account and Portfolio Value
    respectively.
    I am not sure if it would be better to import these text files into excel and then find and copy the value to a new worksheet
    or it is possible to read the required text directly to a worksheet.

    Some of the text files may not have any data in it or may not have the Portfolio Value as the monthly transactions
    may occupied 2 or 3 text files, while some others files may consist of 2 Names separately in it and hence there will be 2 separate
    Total amounts under each Portfolio Values.

    I have attached both the Excel file which shows the result and the dummy txt file.

    Thank in advance for your assistance.

    regards, francis
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract specific text from a text file (Excel 2003)

    The text file is a report that has been exported from a database. You should ask the person who creates the export to export data in a table format instead, to a text file or perhaps even as a spreadsheet in exactly the format that you want

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract specific text from a text file (Excel 2003)

    Hi Hans

    We have tried getting the the central team to provide us in a table format but after two weeks of following up with them,
    they have responded its can't be rerun under live production environment.
    Should this be import to Excel first and then find the respectives value and copy to a new sheet or this need to be ready directly into Excel
    Apology for my asking as I have not encounter this type of text file before, the ones that I have experience with are mostly able to import into
    excel with rows and columns aligned nicely.

    Appreciate your help in this

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract specific text from a text file (Excel 2003)

    The response from your central team is nonsense, it arises from laziness or incompetence. You should not let them get away with it.

    The data are stored in a structured table format in the database, and you want to have them in a structured table format in your spreadsheet. It is ridiculous to spend time on reconstructing a usable format from a plain text export in an unusable format.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract specific text from a text file (Excel 2003)

    You could send your spreadsheet to the central team and ask them to complete it and return it to you within 2 working days.
    They could always type in the data manually... <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract specific text from a text file (Excel 2003)

    Further to Hans' response.
    Your Central Team may not have access to the information you require in the live environment, but they should be able to retrieve it from their backup system.
    Regards
    Don

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract specific text from a text file (Excel 2003)

    Hi Hans

    We can't do anything about it for now even though we knew about it was just an excuses because we are
    the party who have fogotten to upload the correct data initially.

    I have managed to find some sipplet of codes and have created a macro that would be doing what I want, but it doesn't seem
    to be working properly. Would you have a look the the macro and advise.

    I have attached somes files and the result, those in red doesn't appeared after running the macro. They are
    suppose to be appearing as these are with the Portfolio Value. I hope it was just a minor bug as I need this to
    produce the report and submit to head office by tomorrow

    TIA

    regards, francis
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract specific text from a text file (Excel 2003)

    Hans wasn't on line last time I looked. Try the attached.
    H.T.H.
    Attached Files Attached Files
    Regards
    Don

  9. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract specific text from a text file (Excel 2003)

    Further to my previous post;
    <UL><LI> Don't forget to revise (myPath = "????")
    <LI>If the order of file processing is important to you, it will be necessary to place the filenames into an array; sort the array; then run the "DoTheWork" procedure for each element of the sorted array.[/list]
    Regards
    Don

  10. #10
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract specific text from a text file (Excel 2003)

    Hi Don

    Thanks for assisting in this. but its still doesn't do what I expected. Not all have been extracted.
    One more issue is that I will need to resave my original file to .txt and I am not sure why. These
    file are open by Notepad but when I run the macro, it doesn't extract any data and shows "no files found"
    Is there another format that is like Notepad?

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  11. #11
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract specific text from a text file (Excel 2003)

    It sounds to me that you as if you have not revised the "MyPath = " line.
    Regards
    Don

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extract specific text from a text file (Excel 2003)

    You have to save as *.txt files since you coded the macro to look for these types of files, by using the line:

    myFile = Dir(myPath & "*.txt")

    The macro could look for any extension if you chose to change the extension...

    Steve

  13. #13
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract specific text from a text file (Excel 2003)

    Hi Don and Steve

    I have change My Path and it seem to be working except that I notice that the spacing of the Name in the text file have different number of spaces.
    Some lines have 11 spacing while some have 13 spaces. If the code is hard code to 11 spaces as Str1 = LCase("Name :"), it does not read
    all Name into Excel and if this is hard code to 13 spaces, column Value shows **error** when it should have shows the value amount.

    Is it possible not to hard code Str1 so that the code can extract the Name regardless any number of spaces.

    How do I test what file format this is? I have look at its properties but it doesn't show me the file type.

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract specific text from a text file (Excel 2003)

    Coming up
    Regards
    Don

  15. #15
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract specific text from a text file (Excel 2003)

    The attached file has the modified code. You will need to ensure that the path identified by the variable myPath agrees with the actual path you are using.
    Attached Files Attached Files
    Regards
    Don

Posting Permissions

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