Treat string like it is a piece of code in PowerShell-CodePudding

In PowerShell, I would like to read a CSV file, get all column names and dynamically pass them to a for loop, to select items on each row, for each column name. To do so, I have the following script:

$CSV_Employees = Import-Csv "C:\Users\L_L\Desktop\Employees_v2.csv"

$output_array = @()
foreach($HEADER in $CSV_Employees[0].psobject.properties.name){
    $output_array  = $HEADER
}

$output_array = $output_array | ForEach-Object {'''$($'   "LINE.$_"} 
$output_variable= $output_array-join ")', "
$output_variable= """("   $output_array   ")')"""

foreach($LINE in $CSV_Employees){
   $output_variable
}

Output:

"('$($LINE.Id)', '$($LINE.FirstName)', '$($LINE.LastName)', '$($LINE.Salary)', '$($LINE.Department)', '$($LINE.Country)')"

The problem that I am looking to solve is within the foreach loop at the end. It just prints the content of the variable that I am passing. I know that this is the usual behavior but is it possible to treat this variable like an actual piece of code?

The end goal is to create a SQL INSERT INTO statement out of a CSV file. This exact piece is the one that would create the VALUES section of the INSERT INTO statement with all rows of the data from the CSV file. This needs to work for different CSV files, so the column names can't be fixed.

I am using a sample data, which looks like this:

CodePudding user response：

Don't "prepare" your statements, just expand the values as needed when you actually get to them in the second loop:

$CSV_Employees = Import-Csv "C:\Users\L_L\Desktop\Employees_v2.csv"

# No need for the loop here
$columnNames = @($CSV_Employees[0].psobject.Properties.Name)

$insertStatements = foreach($employee in $CSV_Employees){
    $values = $columnNames.ForEach({
        # fetch value from `$employee.$_`, escape single quotes
        "'{0}'" -f $employee.$_.Replace("'","''")
    })

    # Generate insert command string
    "INSERT INTO dbo.TableName ($($output_array -join ', ')) VALUES ($($values -join ', '))"
}

$insertStatements now contains all the generated SQL insert statements