Compare columns between 2 files and delete non common columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1559383%22%20slang%3D%22en-US%22%3ECompare%20columns%20between%202%20files%20and%20delete%20non%20common%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1559383%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20a%20bunch%20of%20files%20in%20folder%20A%20and%20their%20corresponding%20metadata%20files%20in%20folder%20B.%20I%20want%20to%20loop%20though%20the%20data%20files%20and%20check%20if%20the%20columns%20are%20the%20same%20in%20the%20metadata%20file.%20If%20the%20columns%20in%20both%20match%2C%20no%20action%20to%20is%20to%20be%20taken.%20If%20Data%20file%20has%20more%20columns%20than%20metadata%20file%2C%20then%20those%20columns%20should%20be%20deleted%20from%20incoming%20data%20file.%3C%2FP%3E%3CP%3Eex%3C%2FP%3E%3CP%3EData%20file%20is%20ps_job.dat%3C%2FP%3E%3CP%3E%22empid%22%7C%22name%22%7C%22deptid%22%7C%22zipcode%22%7C%22salary%22%7C%22gender%22%3CBR%20%2F%3E%221%22%7C%22Tom%22%7C%2210%22%7C%2211111%22%7C%221000%22%7C%22M%22%3CBR%20%2F%3E%222%22%7C%22Ann%22%7C%2220%22%7C%2222222%22%7C%222000%22%7C%22F%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMeta%20data%20file%20is%20ps_job_metadata.dat%3C%2FP%3E%3CP%3E%22empid%22%7C%22name%22%7C%22zipcode%22%7C%22salary%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20my%20output%20to%20be%26nbsp%3B%3C%2FP%3E%3CP%3E%22empid%22%7C%22name%22%7C%22zipcode%22%7C%22salary%22%3C%2FP%3E%3CP%3E%221%22%7C%22Tom%22%7C%2211111%22%7C%221000%22%3CBR%20%2F%3E%222%22%7C%22Ann%22%7C%2222222%22%7C%222000%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1559383%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EWindows%20PowerShell%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1564740%22%20slang%3D%22en-US%22%3ERe%3A%20Compare%20columns%20between%202%20files%20and%20delete%20non%20common%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1564740%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%3CSPAN%3Eparam%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%5B%3C%2FSPAN%3E%3CSPAN%3EParameter%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EMandatory%3C%2FSPAN%3E%3CSPAN%3E%3D%3C%2FSPAN%3E%3CSPAN%3E%24true%3C%2FSPAN%3E%3CSPAN%3E)%5D%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%5B%3C%2FSPAN%3E%3CSPAN%3Estring%3C%2FSPAN%3E%3CSPAN%3E%5D%3C%2FSPAN%3E%3CSPAN%3E%24DataFile%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%5B%3C%2FSPAN%3E%3CSPAN%3EParameter%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EMandatory%3C%2FSPAN%3E%3CSPAN%3E%3D%3C%2FSPAN%3E%3CSPAN%3E%24true%3C%2FSPAN%3E%3CSPAN%3E)%5D%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%5B%3C%2FSPAN%3E%3CSPAN%3Estring%3C%2FSPAN%3E%3CSPAN%3E%5D%3C%2FSPAN%3E%3CSPAN%3E%24MetaDataFile%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%5B%3C%2FSPAN%3E%3CSPAN%3Estring%3C%2FSPAN%3E%3CSPAN%3E%5D%3C%2FSPAN%3E%3CSPAN%3E%24Separator%3C%2FSPAN%3E%3CSPAN%3E%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22%7C%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Ebegin%3C%2FSPAN%3E%3CSPAN%3E%7B%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%7D%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Eprocess%3C%2FSPAN%3E%3CSPAN%3E%7B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%23Read%20Meta%20Data%20File%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%24metacontent%3C%2FSPAN%3E%3CSPAN%3E%20%3D%20%3C%2FSPAN%3E%3CSPAN%3EGet-Content%3C%2FSPAN%3E%20%3CSPAN%3E%24MetaDataFile%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%23Get%20Columns%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%24metacolumns%3C%2FSPAN%3E%3CSPAN%3E%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E%24metacontent%3C%2FSPAN%3E%3CSPAN%3E.Replace%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E'%22'%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E).Split(%3C%2FSPAN%3E%3CSPAN%3E%24Separator%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%23Parsing%20Data%20File%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%24datacontent%3C%2FSPAN%3E%3CSPAN%3E%20%3D%20%3C%2FSPAN%3E%3CSPAN%3EGet-Content%3C%2FSPAN%3E%20%3CSPAN%3E%24DataFile%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%24datacolumns%3C%2FSPAN%3E%3CSPAN%3E%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E%24datacontent%3C%2FSPAN%3E%3CSPAN%3E%5B%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3CSPAN%3E%5D.Replace(%3C%2FSPAN%3E%3CSPAN%3E'%22'%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E).Split(%3C%2FSPAN%3E%3CSPAN%3E%24Separator%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%24data%3C%2FSPAN%3E%3CSPAN%3E%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E%40%3C%2FSPAN%3E%3CSPAN%3E()%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Efor%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%24i%3C%2FSPAN%3E%3CSPAN%3E%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3E%3B%20%3C%2FSPAN%3E%3CSPAN%3E%24i%3C%2FSPAN%3E%3CSPAN%3E%20-lt%20%3C%2FSPAN%3E%3CSPAN%3E%24datacontent%3C%2FSPAN%3E%3CSPAN%3E.Length%3C%2FSPAN%3E%3CSPAN%3E%3B%20%3C%2FSPAN%3E%3CSPAN%3E%24i%3C%2FSPAN%3E%3CSPAN%3E%2B%2B)%7B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%24values%3C%2FSPAN%3E%3CSPAN%3E%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E%24datacontent%3C%2FSPAN%3E%3CSPAN%3E%5B%3C%2FSPAN%3E%3CSPAN%3E%24i%3C%2FSPAN%3E%3CSPAN%3E%5D.Replace(%3C%2FSPAN%3E%3CSPAN%3E'%22'%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E).Split(%3C%2FSPAN%3E%3CSPAN%3E%24Separator%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%24obj%3C%2FSPAN%3E%3CSPAN%3E%20%3D%20%5B%3C%2FSPAN%3E%3CSPAN%3Eordered%3C%2FSPAN%3E%3CSPAN%3E%5D%3C%2FSPAN%3E%3CSPAN%3E%40%3C%2FSPAN%3E%3CSPAN%3E%7B%7D%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Efor%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%24j%3C%2FSPAN%3E%3CSPAN%3E%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3CSPAN%3E%3B%20%3C%2FSPAN%3E%3CSPAN%3E%24j%3C%2FSPAN%3E%3CSPAN%3E%20-lt%20%3C%2FSPAN%3E%3CSPAN%3E%24datacolumns%3C%2FSPAN%3E%3CSPAN%3E.Length%3C%2FSPAN%3E%3CSPAN%3E%3B%20%3C%2FSPAN%3E%3CSPAN%3E%24j%3C%2FSPAN%3E%3CSPAN%3E%2B%2B)%7B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%23%24obj.%22%24(%24datacolumns%5B%24j%5D)%22%20%3D%20%24values%5B%24j%5D%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%24obj%3C%2FSPAN%3E%3CSPAN%3E.Add%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%24datacolumns%3C%2FSPAN%3E%3CSPAN%3E%5B%3C%2FSPAN%3E%3CSPAN%3E%24j%3C%2FSPAN%3E%3CSPAN%3E%5D%2C%20%3C%2FSPAN%3E%3CSPAN%3E%24values%3C%2FSPAN%3E%3CSPAN%3E%5B%3C%2FSPAN%3E%3CSPAN%3E%24j%3C%2FSPAN%3E%3CSPAN%3E%5D)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7D%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%24data%3C%2FSPAN%3E%3CSPAN%3E%20%2B%3D%20%3C%2FSPAN%3E%3CSPAN%3ENew-Object%3C%2FSPAN%3E%3CSPAN%3E%20-Property%20%3C%2FSPAN%3E%3CSPAN%3E%24obj%3C%2FSPAN%3E%3CSPAN%3E%20-TypeName%20psobject%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7D%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3E%23Selecting%20columns%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%24filtered%3C%2FSPAN%3E%3CSPAN%3E%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E%24data%3C%2FSPAN%3E%3CSPAN%3E%20%7C%20%3C%2FSPAN%3E%3CSPAN%3ESelect-Object%3C%2FSPAN%3E%3CSPAN%3E%20-Property%20%3C%2FSPAN%3E%3CSPAN%3E%24metacolumns%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%23create%20file%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%24output%3C%2FSPAN%3E%3CSPAN%3E%20%3D%20%3C%2FSPAN%3E%3CSPAN%3ENew-Item%3C%2FSPAN%3E%3CSPAN%3E%20-Path%20.%20-Name%20%3C%2FSPAN%3E%3CSPAN%3E%22output.dat%22%3C%2FSPAN%3E%3CSPAN%3E%20-ItemType%20File%20-Force%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%24filtered%3C%2FSPAN%3E%3CSPAN%3E%20%7C%20%3C%2FSPAN%3E%3CSPAN%3EExport-Csv%3C%2FSPAN%3E%3CSPAN%3E%20-Delimiter%20%3C%2FSPAN%3E%3CSPAN%3E%24Separator%3C%2FSPAN%3E%3CSPAN%3E%20-Path%20%3C%2FSPAN%3E%3CSPAN%3E%24output%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7D%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3Eend%3C%2FSPAN%3E%3CSPAN%3E%7B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EWrite-Output%3C%2FSPAN%3E%20%3CSPAN%3E%24filtered%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7D%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746318%22%20target%3D%22_blank%22%3E%40sykashif885264%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello,

I have a bunch of files in folder A and their corresponding metadata files in folder B. I want to loop though the data files and check if the columns are the same in the metadata file. If the columns in both match, no action to is to be taken. If Data file has more columns than metadata file, then those columns should be deleted from incoming data file.

ex

Data file is ps_job.dat

"empid"|"name"|"deptid"|"zipcode"|"salary"|"gender"
"1"|"Tom"|"10"|"11111"|"1000"|"M"
"2"|"Ann"|"20"|"22222"|"2000"|"F"

 

Meta data file is ps_job_metadata.dat

"empid"|"name"|"zipcode"|"salary"

 

I would like my output to be 

"empid"|"name"|"zipcode"|"salary"

"1"|"Tom"|"11111"|"1000"
"2"|"Ann"|"22222"|"2000"

 

Thanks!

1 Reply

 

param(
[Parameter(Mandatory=$true)]
[string]$DataFile,
[Parameter(Mandatory=$true)]
[string]$MetaDataFile,
[string]$Separator = "|"
)
begin{

}
process{
#Read Meta Data File
$metacontent = Get-Content $MetaDataFile
#Get Columns
$metacolumns = $metacontent.Replace('"',"").Split($Separator)

#Parsing Data File
$datacontent = Get-Content $DataFile
$datacolumns = $datacontent[0].Replace('"',"").Split($Separator)
$data = @()
for($i = 1; $i -lt $datacontent.Length; $i++){
$values = $datacontent[$i].Replace('"',"").Split($Separator)
$obj = [ordered]@{}
for($j = 0; $j -lt $datacolumns.Length; $j++){
#$obj."$($datacolumns[$j])" = $values[$j]
$obj.Add($datacolumns[$j], $values[$j])
}
$data += New-Object -Property $obj -TypeName psobject
}

#Selecting columns
$filtered = $data | Select-Object -Property $metacolumns
 
#create file
$output = New-Item -Path . -Name "output.dat" -ItemType File -Force
$filtered | Export-Csv -Delimiter $Separator -Path $output
}
end{
Write-Output $filtered
}

@sykashif885264