+ Reply to Thread
Results 1 to 14 of 14

Excel to find in Notepad and pull data lines

  1. #1
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Thumbs up Excel to find in Notepad and pull data lines

    Hi All,

    I have a Notepad file which has N number (very large) of data. I also have a workbook which has just a few data as of the Notepad data. There may be possibilities ...either if the available ID number in the Excel Workbook is more than one time in the Notepad data or not available at all, so I need to pull all those lines (records) completely from Notepad to the workbook's a sheet (for knowing the repetition of ID in the notepad).

    Can anyone plz help me out with this urgent problem. I wanna run codes for this from MS Excel to Notepad, and just think that applying loops with count & find tricks can work in a faster way (...but don't know how to write codes for this situation).

    P.S. - Below are the attached sample files.

    Thanks in advance!
    Last edited by SunOffice; 11-07-2011 at 05:03 PM.
    Excelforum is Completely Awesome! True learning with Live Examples & Best Techniques!!

  2. #2
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Re: Excel to find in Notepad and pull data lines

    I googled it, but got a little idea to start the code as per my requirements:

    http://groups.google.com/group/micro...b83e0509bfaa00

  3. #3
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Excel to find in Notepad and pull data lines

    hi, SunOffice, I hope I understood right what you are looking for (from my second attempt). Run code "test"
    Attached Files Attached Files
    Last edited by watersev; 11-05-2011 at 07:24 PM. Reason: I think I understood the idea behind :)

  4. #4
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Question Re: Excel to find in Notepad and pull data lines

    Thank you so much for quick & great help!!

    ...and one more thing I require is that If a list of a few ID numbers are available on the worksheet then how can I come to know about each ID number's repetition (frequency) count in the available large data of Notepad file?

    Please see below attached file. I have just added a new column for the above situation or you can apply your ways.

    P;S. »» I need to paste all the found records(lines) from Notepad to another worksheet, so that there is no need to open the notepad again and verify manually.
    Attached Files Attached Files
    Last edited by SunOffice; 11-06-2011 at 07:19 AM.

  5. #5
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Excel to find in Notepad and pull data lines

    please check attachment
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Question Re: Excel to find in Notepad and pull data lines

    WOW...!!

    P;S. »» I need to paste all the found records(lines) from Notepad to another worksheet, so that there is no need to open the notepad again and verify manually.

    ...actually when I was working with you codes for my project; I just came to know these essential requirement. ...hope you/anyone can look into this situation, so tht I can successfully closed this thread from my end.


    ...Your programing tricks open a new channel for my vision ...thanks again for knowledge sharing.

    Please see the attached...
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Excel to find in Notepad and pull data lines

    please check attachment, run code "test"
    Last edited by watersev; 11-06-2011 at 04:37 PM.

  8. #8
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Re: Excel to find in Notepad and pull data lines

    Mr. watersev,

    Thank you sooo much!! It's working flawlessly
    Please Login or Register  to view this content.
    P.S. »» I'd be glad again if you could provide me link(s) to learn more abt the used trick & terminology?

    Thank you to this amazing forum for sharing knowledge!!

  9. #9
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Question Re: Excel to find in Notepad and pull data lines

    Thank you for listening to me again!

    Here is the updated attached, a sample text file. All the requirements are the same as said above in the posts or let me know if anything else is required.

    I tried with below, did a bit modifications, but could not get success - not getting the complete found data lines, line by lines.

    Please Login or Register  to view this content.
    Last edited by SunOffice; 11-30-2011 at 06:29 AM. Reason: Excel file is uploaded!

  10. #10
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Excel to find in Notepad and pull data lines

    Please Login or Register  to view this content.
    Last edited by snb; 11-30-2011 at 06:35 AM.



  11. #11
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Re: Excel to find in Notepad and pull data lines

    Thank you snb for knowledge sharing!

    ...There are more than a million data available in the notepad/text file(s). and I have an excel sheet with a few records around 500, which my team has to manually find/ search in the notepad one by one. If one record is found single/ multiple times then need to copy that whole text line and paste in another sheet as well for data verification & collation purpose.

  12. #12
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Excel to find in Notepad and pull data lines

    try this option (I did not delete your comments):
    Please Login or Register  to view this content.
    Last edited by watersev; 11-30-2011 at 07:11 AM. Reason: updated file with the new code

  13. #13
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Excel to find in Notepad and pull data lines

    So:

    Please Login or Register  to view this content.
    or

    Please Login or Register  to view this content.


    1. the fastest search method in VBA I am familiar with is Instr.
    2. so I put all the existing ID's in column2 into 1 textstring (c01), each ID separated by a pipeline |
    3. I open the 'notepad' file, split all the rows and filter only those lines that contain a colon (only lines with a colon contain an ID in your notepad example): the result is a 1 dimensional array sn
    4. Now we check each line from the notepad file (= each element in the array sn) to the string that contains all existing ID's., using Instr.
    - To get the ID from each array-item we split each array-item by the tabdelimiter and take the first item from the splitting result. The first item is item 0.
    - we look whether the string c01 contains that item; if not
    * mark that item replacing it's value by "~~", so we can filter all "~~" items afterwards
    or (method snb_2)
    * filter the array sn by the not found ID, removing all items from sn that contain the not-found ID
    - method 1: remove from array sn all items that contain "~~")
    5. write the resulting array sn into sheet2
    Last edited by snb; 11-30-2011 at 08:08 AM.

  14. #14
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2013, 2019 & O365
    Posts
    214

    Re: Excel to find in Notepad and pull data lines

    Thank you watersev!

    Yeah it is working fine here; let me try this on the main/ original text file at my work.
    ...and will let you know if anything goes wrong now.

    ...btw for below: Since there are many columns (15-20) with a particular order of data in the notepad, then what should I write instead of 4? or just go with this 4

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1