Consulting

Results 1 to 12 of 12

Thread: Filter a txt file based on an Excel template

  1. #1
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location

    Filter a txt file based on an Excel template

    I have a csv file composed of n records each of which consists of six numbers, I need to filter this file to generate a new file that contains only the records that satisfy a specific condition set through a template made in excel .

    Each line of the file to be filtered must comply with a condition that is :
    the total of numbers present in each group as set in row 44 (I44, L44, O 44, R44 .... BE44) must satisfy a minimum and a maximum of presence as well as specified in BB7 / BC7 for the specific record analyzed to be validated.

    For example, the record reported as test (3 30 47 67 68 89) highlights a string of attendances equal to 0 0 0 1 0 4 3 3 3 3 1 0 0 (groups in which there are no numbers are not considered) which to the conditional string of line 44 it confirms the presence of a min of 4 and a max of 4 conditions met (see the 4 green stripes placed in correspondence with the occurrences that represent compliance with the condition).

    While for example the record 3 8 10 47 48 84 will be discarded and therefore not present in the Outputfile.csv file as it satisfies only 3 conditions instead of the minimum 4 provided.

    The file provided as an example 'ToPurgeFile.csv' consists of 6545 records and theoretically should be reduced by more than 50% with the excel template attached.

    as I can't write the code, my knowledge is too poor, I ask if someone can write it for me

    thanks in advance

    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    I sent you a PM.

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    In the attached, new formulae at range H47:AB48.
    Macro called PurgeFile2.

    Update manually:
    1. rows 10:31 grid values
    2. row 44 (which will transfer data to row 47)
    3. run the macro


    You don't need formulae in row 33, removing them might speed things up.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    Hello p45cal


    thank you for your support, I'm going to do some tests to check if the result of the process returns the expected values and I inform you, ... really impressive for me as you have solved in a few lines a process that I thought required a lot more code ... thanks again

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    It can be done mucht faster in RAM without any worksheet interaction.

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by snb View Post
    It can be done mucht faster in RAM without any worksheet interaction.
    Yes, it could be almost instant, whereas the time to write and test such a routine (macro or whatever) wouldn't be.
    RIC63, I merely tweaked your worksheet formulae and let them do the donkey work…
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    Hello p45cal


    that's what I wanted, I sincerely thank you for me it's okay


    thanks again for your support

  8. #8
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    Hello p45cal


    Based on the routine you provided me I tried to modify it to evaluate further parameters but the output file does not seem to have a consistent content.
    Through the debug window I tried to check the values assumed from time to time by the variables but for AA48 AA53 AA58 BE6 AA48 AA53 AA58 and BF6 no value is displayed.
    Can you please tell me if what i intend to do is really feasible and if you can correct where am i wrong?


    Thanks

    ...I hope it is correct to write here, I looked in the FAQ if there was something to reopen a thread but I could not find anything
    Attached Files Attached Files

  9. #9
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by RIC63 View Post
    Through the debug window I tried to check the values assumed from time to time by the variables but for AA48 AA53 AA58 BE6 AA48 AA53 AA58 and BF6 no value is displayed.
    You remember I said:
    Quote Originally Posted by p45cal View Post
    RIC63, I merely tweaked your worksheet formulae and let them do the donkey work…
    What the macro does is simple:
    1. it updates cells A2:F2
    2. lets the formulae do their calculations resulting in a single cell (AB48) being TRUE/FALSE
    3. only if it's TRUE, write the data to the output file

    So what you need to do is change the formulae on the sheet finishing with a TRUE/FALSE in cell AB48.

    Now how you change/add formulae on the sheet depends on:
    Quote Originally Posted by RIC63 View Post
    what i intend to do is really feasible
    which I don't really know! -despite this:
    Quote Originally Posted by RIC63 View Post
    to evaluate further parameters
    which isn't very explanatory…



    Quote Originally Posted by RIC63 View Post
    ...I hope it is correct to write here, I looked in the FAQ if there was something to reopen a thread but I could not find anything
    Yes it's correct to write here.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  10. #10
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    good morning p45cal


    in the file that I have attached I tried to modify the routine that you have developed for me so that it can do other conditional checks that determine if a string written in A2:F2 goes into the output file or not, sorry I assumed it was clear.


    What I would like to do contextually to verify the true / false condition of AB48 is:


    AA48 is between the two values min BB5 and max BC5
    AA53 is between the two values min BB6 and max BC6
    AA58 is between the two values min BB7 and max BC7


    and finally that the sum of AA48 + AA53 + AA58 is included between the two values min BE6 and max BF6


    only if all conditions are met then writes / adds to the output file


    thank you for what you can let me know

  11. #11
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    You seem to be getting it all fairly correct.
    In your file, I think:
    cell I48 has the same formula as J48.
    • the 3 ranges H48:S48,H53:S53,H58:S58 are identical
    • the formulae on row 33 carry out the same function as the previous point


    So we have 48 cells with formulae when we only need 12


    In the attached:
    • I've moved the more compact formulae to row 33 and created links to them in row 48
    • Brought the 3 rows Dist 1,2 & 3 next to each other
    • Adjusted the formulae in the 3 rows below row 48
    • Added formulae to AD49:51; check this logic is correct!
    • Added a final TRUE/FALSE formula in cell AI48 to ensure all conditions are met. You must check that this formula and its precedent formulae are all correct because this is the cell that the (adjusted) macro will look at to decide whether to include the data in the output file.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  12. #12
    VBAX Regular
    Joined
    Jan 2015
    Posts
    92
    Location
    hello p45cal


    I agree with all of your observations and also with the logic of the formulas and the simplifications you have introduced
    it is impressive for me to see how you solve and connect concepts with simplicity
    thanks for your help and support sincerely

Posting Permissions

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