Search code examples
sqlpowershellforeachimport-csvinvoke-sqlcmd

Missing values passing array variables from csv file to invoke-sqlcmd insert statement?

I'm just learning PowerShell, I've built a script trying to import a .CSV file into my SQL Server database, and found that the following query was just appending the table.

$database = 'DATABASE'
$server = 'SERVER'
$table = 'TABLE'
Import-CSV \\TESTPATH\licenses_v2.csv  | 
ForEach-Object { 
Invoke-Sqlcmd -Database $database -ServerInstance $server -Query "insert into $table VALUES ('$($_.FirstName)','$($_.LastName)','$($_.Department)','$($_.Title)')" 
}

So in an effort to first TRUNCATE before INSERT, I've tested the following two scripts below:

$database = 'DATABASE'
$server = 'SERVER'
$table = 'TABLE'
Import-CSV \\TESTPATH\licenses_v2.csv | 
ForEach-Object { Invoke-Sqlcmd -Database $database -ServerInstance $server -Query "truncate table $table; insert into $table VALUES ('$($_.FirstName)','$($_.LastName)','$($_.Department)','$($_.Title)')" 
}

$database = 'DATABASE'
$server = 'SERVER'
$table = 'TABLE'
Invoke-Sqlcmd -Database $database -ServerInstance $server -Query "truncate table $table"
Import-CSV \\TESTPATH\licenses_v2.csv | 
ForEach-Object { Invoke-Sqlcmd -Database $database -ServerInstance $server -Query "insert into $table VALUES ('$($_.FirstName)','$($_.LastName)','$($_.Department)','$($_.Title)')" 
}

After testing those scripts, then using my original script at the top of the page, my table returns the same number of records from the .CSV insert, but they're all blank now, and this wasn't the case prior to testing those two separate TRUNCATE scripts (the file still has the same values in each record):

enter image description here

Querying the object from PowerShell it looks like the select-object command returns all empty records now:

import-csv \\TestPath\licenses_v2.csv | select-object FirstName,Department,Title

However, the Import-CSV, does return the values present in the file:

Import-CSV \\TestPath\licenses_v2.csv

Testing other SQL statements to insert literal SQL strings it appears that I'm still able to write to the table using invoke-sqlcmd, but (I may be wrong) I've somehow truncated the object values that powershell is reading from the file, even though the values are still in the file.

Do I need to restore PowerShell somehow or did i somehow alter my file system?

Also, I can query the very same file from SQL using the following script, so i'm clearly able to read the values but my PowerShell script for some reason isn't able to read the values using the import-csv command:

CREATE TABLE #TempTable(
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [Department] [varchar](150) NULL,
    [Title] [varchar](150) NULL
) 
   BULK INSERT #TempTable
    FROM '\\TestPath\licenses_v2.csv'
    WITH
(
  FIRSTROW = 2,
  DATAFILETYPE='char', 
  FIELDTERMINATOR = '|',
  ROWTERMINATOR = '\n',
  TABLOCK,
  KEEPNULLS -- Treat empty fields as NULLs.
)
go
select * from #TempTable

enter image description here

Solution

  • The issue is caused by the missing delimiter.

    I'm using a pipe delimited CSV, when the default is comma. Theres in my export script screen #1 because it creates a single object for every column. Which is why the headers empty, because powershell does not match any objects under those literal headers (since there's only a single header, named a combination of all.)

    Note that Select can return headers when matched with literally nothing:

    '' | Select Name,Date

Name Date
---- ----

    To fix, just use Import-Csv -Delimiter '|'

    See https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/import-csv?view=powershell-6#optional-parameters

    If you specify a character other than the actual string delimiter in the file, Import-Csv cannot create objects from the CSV strings. Instead, it returns the strings.