How to import data from .csv in SQL Server using PowerShell? - problem

Question

I'm trying to import a CSV into a SQL Server database using PowerShell. This is what I'm doing:

$database="myTestDB"
$server="TEST_SERVER"
$table="LOG_TEST"
Import-CSV C:\test\OUTPUT3.csv | ForEach-Object {Invoke-Sqlcmd -Database $database -ServerInstance $server -Query "INSERT INTO $table VALUES ('$($_.col1)',$($_.col2),'$($_.col3)')"}

But I keep getting this error:

Invoke-Sqlcmd : Incorrect syntax near ','.
At line:1 char:57
+ ... ach-Object {Invoke-Sqlcmd -Database $database -ServerInstance $server ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Col2 is an int. I've tried different methods and notations of getting the variables in from the CSV but always similar errors.

Any help would be great. I was following the answer from this question...

How to import data from .csv in SQL Server using PowerShell?

Thanks!

More Information:

I have changed things up to simplify the scenario and so I could give more details here. The table consists of 3 varchar columns. I believe the original errors were due to some issues with the data. The data file I am working with is this saved as test.csv:

col1,col2,col3
a,b,c
1,2,3
d,e,f
4,5,6
g,h,i
7,8,9

My script is:

$database="myTestDB"
$server="Test_Server"
$table="csvLoadTest"
$Query="INSERT INTO $table VALUES ('$($_.col1)','$($_.col2)','$($_.col3)')"
Import-CSV C:\Test_Server\test.csv | ForEach-Object {Invoke-Sqlcmd -Database $database -ServerInstance $server -Query $Query}

The problem I am having now is that the fields are coming in blank. It runs and inserts records but there is no data in the fields.

As a side note... I am aware of the SQL injection risks with this but that is not a real concern for me, in this set up, for a number of reasons. I'm just trying to get it to work at this point. Thanks!

You are going to get SQL injection issues like this. You could pass through `-Variables`. Consider using `bcp` or [`Write-SqlTableData`](https://learn.microsoft.com/en-us/powershell/module/sqlserver/write-sqltabledata?view=sqlserver-ps) — Charlieface, Jun 22 '21 at 22:04
Run this and see what it gives: `Import-CSV C:\test\OUTPUT3.csv | ForEach-Object {"INSERT INTO $table VALUES ('$($_.col1)',$($_.col2),'$($_.col3)')"}` — Rob Farley, Jun 23 '21 at 00:12
Use your original script. You can't use $_ outside of ForEach-Object. You can also move definition of $Query inside of the loop. — Piotr Palka, Jun 23 '21 at 14:26

score 3 · Answer 1 · answered Jun 22 '21 at 22:30

3

This is almost certainly due to a comma in one of your fields.

So if your data looks like:

HM The Queen, Buckingham Palace London, 4

, then you have three columns, right?

But if you have;

HM The Queen, "Buckingham Palace, London", 4

, then do you have three or four? My guess is that col2 is now set to London" because of a comma in col1, and you’re getting an error because of this.

Try using something like Import-DbaCSV from http://dbatools.io, which is very aware of this type of thing. And push it straight into the database instead of constructing an insert statement, for the sake of avoiding sql injection.

answered Jun 22 '21 at 22:30

Rob Farley

15,625
5
44
58

Nope... I reduced what I was doing... $database="myTestDB" $server="TestServer" $table="TEST_TABLE" $Query="INSERT INTO $table (Body,NumSegments,direction) VALUES ('$_.body',$numSegments,'$_.direction')" Import-CSV C:\test\OUTPUT3_reform.csv | ForEach-Object {Invoke-Sqlcmd -Database $database -ServerInstance $server -Query $Query} and the contents of my CSV is quite literally: body,numSegments,direction a,3,c when viewed in notepad Error is: Invoke-Sqlcmd : Incorrect syntax near ','. At line:1 char:64 – Jim Jun 22 '21 at 23:13
Ok. I’ve put a comment against the question. If you can provide that, it might show you where the problem is. – Rob Farley Jun 23 '21 at 00:14
1

_"I believe the original errors were due to some issues with the data"_ – Nick.Mc Jun 24 '21 at 05:01

dougp · Answer 2 · 2021-06-23T17:27:38.150

The pipe (|) is a good way to push things around on the command line. In a file, it's better to not use it, but structure the code in a way that is clearer to read.

I created some sample data and a table on SQL server and tested your script. I saw the same error.

This script doesn't produce the same error. After running the script, all records appear in the database.

$database="myTestDB"
$server="TEST_SERVER"
$table="LOG_TEST"
$csv="C:\test\OUTPUT3.csv"
$data=Import-CSV -LiteralPath $csv
ForEach ($rec in $data) {
    $qry="INSERT INTO $table VALUES('$($rec.col1)',$($rec.col2),'$($rec.col3)')"
    Invoke-Sqlcmd -Database $database -ServerInstance $server -Query $qry
}

Piotr Palka · Answer 3 · 2021-06-23T14:19:52.420

Your script works and I was able to load data using it.
Without posting sample file we will not be able to find error, it will be only guesses. I agree with other comments that it is not safe (susceptible for injection) and slow.
Sample file I loaded:

col1,col2,col3
aaaa,123,asdad
bbbb,3,asdad

So guessing why it can break:

you have different column names in your file than col1, col2, col3
some of the values are empty

In both cases you will get exactly the same error described in your post

How to import data from .csv in SQL Server using PowerShell? - problem

3 Answers3