Delete Selected Numbers

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of foncesa john foncesa john 2 years, 1 month ago.

  • Author
    Posts
  • #20068
    Profile photo of foncesa john
    foncesa john
    Participant

    Hi,

    I have a text file wherein there are 6 digits numbers in descending order, & I have an new database csv file wherein this numbers of text file exists, now i want to delete those numbers in my csv file. Pick the text file 6 digits number and search in csv file and if found delete it.

    Text file order.
    123456
    654321
    456789

    CSV file order.
    9872636410,765246254261,7166354627,[u]123456[/u],87654254321
    9876453524,[u]654321[/u],84756565263,8765365436,3668293746
    8576463514,2436459682,6151125643,[u]456789[/u],5896702826

    Any Helps.

  • #20070
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    What do you want to put into the CSV file in place of those numbers? Should it just be an empty field? for example:

    9872636410,765246254261,7166354627,,87654254321
    9876453524,,84756565263,8765365436,3668293746
    8576463514,2436459682,6151125643,,5896702826
    

    Also, should those numbers only be deleted from the corresponding lines of the CSV and text file? In your example, what if "123456" existed farther down in the CSV file; should it be deleted, or left alone because "123456" was only specified in the first line of the text file?

    This is fairly straightforward. Let's say that you wanted to remove 123456 from the entire CSV file, you could do something like this:

    (Import-Csv test.csv) |
    ForEach-Object {
        foreach ($property in $_.PSObject.Properties)
        {
            if ($property.Value -eq 123456)
            {
                $property.Value = $null
            }
        }
    
        $_
    } |
    Export-Csv test.csv -NoTypeInformation
    
  • #20087
    Profile photo of foncesa john
    foncesa john
    Participant

    Hi,

    Sorry for imparting improper information,

    The text file has two fields one key number and second Search number. Pick 2nd field (6 digits) from text file and search the CSV file once that 6 digits is found we will have the key number from CSV file (2 feild) and confirm that with Text file key number in text file (1st field) & if both conditions are matched, delete it from CSV database.

    Text file structure.
    22149|123456
    2413|654321
    26118|123456

    CSV file structure.
    1,22149,9872636410,765246254261,7166354627,[u]123456[/u],87654254321 (both the conditions are matched then delete it)
    2,2413,9876453524,[u]654321[/u],84756565263,8765365436,3668293746 (conditions matched)
    3,26118,8576463514,2436459682,6151125643,456789,5896702826

    Result of CSV file.
    1,22149,9872636410,765246254261,7166354627,87654254321
    2,2413,9876453524,84756565263,8765365436,3668293746
    3,26118,8576463514,2436459682,6151125643,456789,5896702826

    Thanking you in advance.

You must be logged in to reply to this topic.