locked
get values from second csv RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

     

     How can I get a couple of values from my 2nd csv during processing? I need the data values from $csv2 that have the header of prodline and shipto include in the output.

    $CSV1 = Import-CSV "C:\compress_in\hdr.csv"
$CSV2 = Import-CSV "C:\compress_in\dtl.csv"

$Hash = @{ }
foreach ($Record in $CSV1)
{
  
	$Hash[$Record.Invoice_Nbr] = $Record | Select Company,Gl_Division,Acct_Year,Acct_Month,Invoice_Nbr,ship_cust_nbr,order_nbr
}

foreach ($Record in $CSV2)
{
	try
	{
		$Hash[$Record.Invoice_Nbr].Company = $Record.Company
		$Hash[$Record.Invoice_Nbr].Gl_Division = $Record.Gl_Division
		$Hash[$Record.Invoice_Nbr].Acct_Year = $Record.Acct_Year
                $Hash[$Record.Invoice_Nbr].Acct_Month = $Record.Acct_Month
		$Hash[$Record.Invoice_Nbr].Invoice_Nbr = $Record.Invoice_Nbr
	}
	catch
	{
		Write-Warning "[Csv2]Failed to process $($Record.Invoice_Nbr): $($_.Exception.Message)"
	}
}

$Hash.Values | Export-CSV C:\compress_in\Invoiceload.csv -NoTypeInfo

     Thanks.

     

    Wednesday, September 20, 2017 6:31 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    First learn to post readable code. Don't write code based on guesswork or what you think you have seen online.

    Here is your code with a couple of fixes to show you how to load a CSV.  If the CSV has headers you don't use the "header" argument.

    $hdr = import-csv C:\compress_in\hdr.csv | Where{ $va.order_type -ne 'c'}
$dtl = import-csv C:\compress_in\dtl.csv

$hashB = @{ }
foreach ($vb in $dtl) {
	$hashB[$vb.Invoice_Nbr] = $vb
}

foreach ($va in $a) {
	$MyJoin = $hashB.get_Item($va.Invoice_Nbr)
	$myOut = $va.ship_cust_nbr + "," + $va.Invoice_Nbr + "," + $va.invoice_date + "," + $MyJoin.Item_ID + "," + $MyJoin.Prod_Line + "," + $va.order_nbr
	$myOut | Out-File 'c:\compress_in\invload.csv' -Append
}

    The join loops need to be embedded.  Match rows based on the key.  Don't use a hash.  Just match the records detail->master.  Add needed columns from hdr to dtl and output CSV line object.

    Output new collection using Export-Csv.

    \_(ツ)_/

    • Marked as answer by hart60 Monday, September 25, 2017 4:27 AM
    Saturday, September 23, 2017 6:29 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    See this script: https://1drv.ms/u/s!AjiiPtIUqzK_gs0UobI_Zrj9tslXKw

    \_(ツ)_/

    Wednesday, September 20, 2017 6:47 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I saw that example, but can't use the ACE driver on this solution.

     Thanks.

    Wednesday, September 20, 2017 6:52 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Then you will have to manually join the two.

    \_(ツ)_/

    Wednesday, September 20, 2017 7:07 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

     

     Why can't it find join-object?

    $left ='C:\compress_in\hdr.csv'  # you must have already created it.
$right ='C:\compress_in\dtl.csv' # idem.

Join-Object -Left $left -Right $right -Where { $args[0].Invoice_Nbr -eq $args[1].Invoice_Nbr } -Type AllInBoth -LeftProperties Company,Gl_Division,Acct_Year,Acct_Month,Invoice_Nbr,ship_cust_nbr,order_nbr -RightProperties Prodline |
Export-Csv 'c:\compress_in\combined.csv'

Import-Csv 'c:\compress_in\combined.csv' | Format-Table -Autosize
     Thanks.

    Wednesday, September 20, 2017 11:46 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    PowerShell does not have a command called "Join-Object".

    \_(ツ)_/

    Wednesday, September 20, 2017 11:49 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

     Function..

    error:

     Join-Object : A parameter cannot be found that matches parameter name 'where'

    Thursday, September 21, 2017 12:22 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

     went back to manual join can I make the out-file piece any faster or script in general?

     Thanks.

    $a = @(import-csv 'C:\compress_in\hdr.csv' -Header "Invoice_Nbr","ship_cust_nbr","order_type","order_nbr","invoice_date" -Delimiter ",")

$b = @(import-csv 'C:\compress_in\dtl.csv' -Header "Invoice_Nbr","Item_Id","Prod_Line" -Delimiter ",")

$hashA=@{}

foreach ($va in $a){

if ($va.order_type -eq 'c') 
 {
   $hashA[$va.Invoice_Nbr] = $va
 }
 Else
 {
 Write-Host 'skipping' $va.Invoice_Nbr
 }
}

$hashB=@{}

foreach ($vb in $b){

$hashB[$vb.Invoice_Nbr] = $vb

}

foreach ($va in $a) {

$MyJoin = $hashB.get_Item($va.Invoice_Nbr)

$myOut = $va.ship_cust_nbr + "," + $va.Invoice_Nbr + "," + $va.invoice_date + "," + $MyJoin.Item_ID + "," + $MyJoin.Prod_Line + "," + $va.order_nbr 

$myOut | Out-File 'c:\compress_in\invload.csv' -Append
}

    Saturday, September 23, 2017 2:59 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    First learn to post readable code. Don't write code based on guesswork or what you think you have seen online.

    Here is your code with a couple of fixes to show you how to load a CSV.  If the CSV has headers you don't use the "header" argument.

    $hdr = import-csv C:\compress_in\hdr.csv | Where{ $va.order_type -ne 'c'}
$dtl = import-csv C:\compress_in\dtl.csv

$hashB = @{ }
foreach ($vb in $dtl) {
	$hashB[$vb.Invoice_Nbr] = $vb
}

foreach ($va in $a) {
	$MyJoin = $hashB.get_Item($va.Invoice_Nbr)
	$myOut = $va.ship_cust_nbr + "," + $va.Invoice_Nbr + "," + $va.invoice_date + "," + $MyJoin.Item_ID + "," + $MyJoin.Prod_Line + "," + $va.order_nbr
	$myOut | Out-File 'c:\compress_in\invload.csv' -Append
}

    The join loops need to be embedded.  Match rows based on the key.  Don't use a hash.  Just match the records detail->master.  Add needed columns from hdr to dtl and output CSV line object.

    Output new collection using Export-Csv.

    \_(ツ)_/

    • Marked as answer by hart60 Monday, September 25, 2017 4:27 AM
    Saturday, September 23, 2017 6:29 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

     Thanks!!

    Monday, September 25, 2017 4:27 AM