none
Read a CSV file and dynamically generate the insert RRS feed

  • Question

  • I have a requirement where there are multiple csv's which needs to be exported to a sql table. So far, I am able to read the csv file and generate the insert statement dynamically for selected columns however, the insert statement when passed as a parameter to the $cmd.CommandText does not evaluate the values

    How to evaluate the string in powershell

    Import-Csv -Path $FileName.FullName |  % {	  
    		
    # Insert statement.
    $insert = "INSERT INTO $Tablename  ($ReqColumns) Values ('"
    
    	$valCols='';
    	$DataCols='';
    	$lists = $ReqColumns.split(",");
    	foreach($l in $lists)
    	{
    		$valCols= $valCols + '$($_.'+$l+')'','''
    	}
    		  
    	#Generate the values statement
    	$DataCols=($DataCols+$valCols+')').replace(",')",""); 
    	
    	$insertStr =@("INSERT INTO $Tablename  ($ReqColumns) Values ('$($DataCols))")
    	#The above statement generate the following insert statement
    	#INSERT INTO TMP_APPLE_EXPORT  (PRODUCT_ID,QTY_SOLD,QTY_AVAILABLE) Values (' $($_.PRODUCT_ID)','$($_.QTY_SOLD)','$($_.QTY_AVAILABLE)' )
    	
    	$cmd.CommandText = $insertStr #does not evaluate the values
    	
    	#If the same statement is passed as below then it execute successfully
    	#$cmd.CommandText = "INSERT INTO TMP_APL_EXPORT  (PRODUCT_ID,QTY_SOLD,QTY_AVAILABLE) Values (' $($_.PRODUCT_ID)','$($_.QTY_SOLD)','$($_.QTY_AVAILABLE)' )"
    		  
    	#Execute Query
    	$cmd.ExecuteNonQuery() | Out-Null 
    
    	}






    jyeragi

    Friday, April 25, 2014 4:32 AM

Answers

All replies