How to clean up data from multiple files

[psycoperl]

I am not sure what the best course of action would be and if Excel and/or Access would be the best way to handle these issues.

I have two text files each contain a string of ID values separated by commas one file is the "MASTER" list and the other is the "Exception" List

I am looking for a way to get a string that only has the values of the MASTER list except for the EXCEPTION LIST values.

For example the MASTER file has the following contents
12345678,23456789,98765432,87654321,76543210,23723496,23748031,23757919,23854243,23855007,23855155,23856110,23860835,23872391

The EXCEPTION FILE has the following contents
76543210,23723496,23855007

What I would like to return is a string that would contain
12345678,23456789,98765432,87654321,23748031,23757919,23854243,23855155,23856110,23860835,23872391

The Master list could have up to 2000 IDs, each ID is 8 characters long, and it would be presented in a single string in the file.

I am looking for the easiest and best way to process these issues.

Your guidance and suggestions is greatly appreciated.

 

[kennypete]

Easy/easiest may be Powershell for this.

Sub out your input master and exception files on lines 1 and 2 and specify where you want the outfile.txt on line 13 and this code should work fine:

$master = Get-Content "D:\_Share\2021\20210424\master.txt"
$exception = Get-Content "D:\_Share\2021\20210424\exception.txt"

[System.Collections.ArrayList]$arrMaster = $master -split (",")
[System.Collections.ArrayList]$arrException = $exception -split (",")

$arrException | ForEach-Object {
    if ($arrMaster -contains $_) {
        $arrMaster.Remove($_)
    }
}

$arrMaster[0] | Out-File -FilePath "D:\_Share\2021\20210424\outfile.txt" -NoNewline
for ($num = 1 ; $num -lt $arrMaster.Count ; $num++) {
    ',' + $arrMaster[$num] | Out-File -FilePath "D:\_Share\2021\20210424\outfile.txt" -Append -NoNewline
}

Using your input files, this was my output:

 

[Marc L]

A VBA demonstration as a beginner starter :​

Sub Demo1()
  Const D = ",", E = "76543210,23723496,23855007", _
        M = "12345678,23456789,98765432,87654321,76543210,23723496,23748031,23757919,23854243,23855007,23855155,23856110,23860835,23872391"
        V = Split(M, D)
        For Each W In Split(E, D):  V = Filter(V, W, False):  Next
        V = Join(V, D)
        Debug.Print V
End Sub