locked
get values from second csv RRS feed

  • Question

  •  

     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

  • 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

  • Wednesday, September 20, 2017 6:47 PM
  • I saw that example, but can't use the ACE driver on this solution.

     Thanks.

    Wednesday, September 20, 2017 6:52 PM
  • Then you will have to manually join the two.


    \_(ツ)_/

    Wednesday, September 20, 2017 7:07 PM
  •  

     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
  • PowerShell does not have a command called "Join-Object".


    \_(ツ)_/

    Wednesday, September 20, 2017 11:49 PM
  •  Function..

    error:

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

    Thursday, September 21, 2017 12:22 AM
  •  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
  • 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
  •  Thanks!!

    Monday, September 25, 2017 4:27 AM