sophieb1984
asked on
Merge two CSV Files Powershell
Hi All,
I have the below script but am having some issues, i have two csv files
prods.csv:
partcode, manuf, desc, cost, rrp
stock.csv:
partcode, qty
i need to merge the qty column with the corolating partcode, the file stock.csv has more items in it than the prods.csv and neither files have a header in them so i need to match the partcodes and then on a match put the qty in the prods.csv or a new csv.
my current script only checks to make sure that the line number is the same which isnt good enough as the stock.csv has more lines in it.
I have the below script but am having some issues, i have two csv files
prods.csv:
partcode, manuf, desc, cost, rrp
stock.csv:
partcode, qty
i need to merge the qty column with the corolating partcode, the file stock.csv has more items in it than the prods.csv and neither files have a header in them so i need to match the partcodes and then on a match put the qty in the prods.csv or a new csv.
my current script only checks to make sure that the line number is the same which isnt good enough as the stock.csv has more lines in it.
Remove-Item E:\MergedData.txt
$prods = Get-Content "E:\prods.csv"
Write-Host "Ateam Product file read"
$data = Get-Content "E:\Stock.csv"
Write-Host "Both files read"
Clear-Host
[int] $i = 0
foreach($prod in $prods)
{
# split the current line from the genes.txt
# file into an array of 2 items using
# the tab character as the delimiter
$oneLineProds = $prod.Split(",")
# assign each of the 2 pieces of gene info
# to a variable
# $geneNumber would be "gene1" in first line
$prodNumber = $oneLineProds[0]
# $geneName would be "kinse_a" in first line
$prodName = $oneLineProds[1]
$prodDesc = $oneLineProds[2]
$prodCost = $oneLineProds[3]
$prodRRP = $oneLineProds[4]
# $data[$i] retrieves the correct line number from
# the data.txt file.
# split the current line from the data.txt
# file into an array of 2 elements using
# the tab character as the delimiter
$oneLineData = $data[$i].Split(",")
# $geneDataValue would be "+8.6" in first line
$prodStock = $oneLineData[1]
# build a variable that will look like this for the
# first line in both files:
# "gene1,kinse_a,+8.6"
$lineToWrite = "$prodNumber, $prodName, $prodDesc, $prodCost, $prodRRP, $prodStock"
# now write that variable out to a file
$lineToWrite | out-file E:\MergedData.txt -append
# increment $i so that we always retrieve the
# correct line number from the data.txt file
$i++
# for performance testing - comment out for production
if( ($i % 100) -eq 0 )
{
$i
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Sorry to be a pain i have got the script to work now but if the script is run more than once on a file then it will add the header again and again when the script is run, is it possible to add an if statement that will not add the header if it already exists.
Remove-Item "e:\csv\csv_suppliers\ateam\MergedData.csv"
$prodheader = "partcode,manuf,desc,cost,std"
$stockheader = "partcode,qty"
$combinedheader = "$($prodheader),qty"
$arrProd = @()
$arrProd += $prodheader
gc "e:\csv\csv_suppliers\ateam\Pricelist for Fms.csv" | % { $arrProd+=$_ }
Set-Content "e:\csv\csv_suppliers\ateam\Pricelist for Fms.csv" $arrProd
$arrProd = $null
$arrStock = @()
$arrStock += $stockheader
gc "e:\csv\csv_suppliers\ateam\stock feed.csv" | % { $arrStock+=$_}
Set-Content "e:\csv\csv_suppliers\ateam\stock feed.csv" $arrStock
$arrStock = $null
$arrProd = Import-Csv "e:\csv\csv_suppliers\ateam\Pricelist for fms.csv"
$arrStock = Import-Csv "e:\csv\csv_suppliers\ateam\stock feed.csv"
$hashProd = @{}
$arrProd | % {
$hashProd.Add($_.partcode,$("$($_.manuf),$($_.desc),$($_.cost),$($_.std),"))
}
$hashStock = @{}
$arrStock | % {
$hashStock.Add($_.partcode,$("$($_.qty)"))
}
$hashStock.Keys | % {
if ($hashProd.ContainsKey($_)){
$hashProd.Item($_) +=""+$hashStock.Item($_)
}
}
New-Item -ItemType File -Path "e:\csv\csv_suppliers\ateam\MergedData.csv"
Add-content "e:\csv\csv_suppliers\ateam\MergedData.csv" $combinedheader
Add-Content "e:\csv\csv_suppliers\ateam\MergedData.csv" @($hashProd.Keys | % { "$($_),$($hashprod.item($_))" })
ASKER
partcode,manuf,desc,cost,r
partcode,manuf,desc,cost,r
partcode,manuf,desc,cost,r
partcode,manuf,desc,cost,r
partcode,manuf,desc,cost,r
partcode,manuf,desc,cost,r
i need it to do this:
partcode,manuf,desc,cost,r
partcode,manuf,desc,cost,r
partcode,manuf,desc,cost,r
partcode,manuf,desc,cost,r
partcode,manuf,desc,cost,r
partcode,manuf,desc,cost,r
simply add a comma to the columns that dont have a qty of stock in order to make it look like the