[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Merge two CSV Files Powershell

Posted on 2010-09-17
3
Medium Priority
?
1,229 Views
Last Modified: 2012-05-10
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.


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
}
}

Open in new window

0
Comment
Question by:sophieb1984
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
Learnctx earned 2000 total points
ID: 33706897
This will take the 2 CSV files look for a match between the prods.csv and the stock.csv for the partcode. If a match is found they're put into a merged csv. If there is no match it is discarded. It inserts a header into the CSV files to make them easier to work with but that can be taken away and you can just work with the array as you have been.

Unfortunately I am short of time today so cannot provide a cleaner script.
$prodheader = "partcode,manuf,desc,cost,rrp"
$stockheader = "partcode,qty"
$combinedheader = "$($prodheader),qty"

$arrProd = @()
$arrProd += $prodheader
gc "E\prods.csv" | % { $arrProd+=$_ }
Set-Content "E:\prods.csv" $arrProd
$arrProd = $null

$arrStock = @()
$arrStock += $stockheader
gc "E:\stock.csv" | % { $arrStock+=$_}
Set-Content "E:\stock.csv" $arrStock
$arrStock = $null

$arrProd = Import-Csv "E:\prods.csv"
$arrStock = Import-Csv "E:\stock.csv"

$hashProd = @{}
$arrProd | % {
	$hashProd.Add($_.partcode,$("$($_.manuf),$($_.desc),$($_.cost),$($_.rrp)"))
}

$hashStock = @{}
$arrStock | % {
	$hashStock.Add($_.partcode,$_.qty)
}

$hashStock.Keys | % {
	if ($hashProd.ContainsKey($_)){
		$hashProd.Item($_) += ","+$hashStock.Item($_)
	}
}

New-Item -ItemType File -Path "E:\combined.csv"
Add-content "E:\cojavascript:void(0);mbined.csv" $combinedheader
Add-Content "E:\combined.csv" @($hashProd.Keys | % { "$($_),$($hashprod.item($_))" })

Open in new window

0
 
LVL 1

Author Comment

by:sophieb1984
ID: 33715103
The script works fine, but when one of the products cant find a stock line for it the column isn't created as you will know this is not good for CSV as it cant tell where the column ends / finishes I am getting the following result:

partcode,manuf,desc,cost,rrp
partcode,manuf,desc,cost,rrp,qty
partcode,manuf,desc,cost,rrp,qty
partcode,manuf,desc,cost,rrp
partcode,manuf,desc,cost,rrp,qty
partcode,manuf,desc,cost,rrp


i need it to do this:

partcode,manuf,desc,cost,rrp,
partcode,manuf,desc,cost,rrp,qty
partcode,manuf,desc,cost,rrp,qty
partcode,manuf,desc,cost,rrp,
partcode,manuf,desc,cost,rrp,qty
partcode,manuf,desc,cost,rrp,


simply add a comma to the columns that dont have a qty of stock in order to make it look like the
0
 
LVL 1

Author Comment

by:sophieb1984
ID: 33723575
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($_))" })

Open in new window

0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question