CODEWITHSUNDEEP
sqlsql-serverpowershell
Jim
Jim

Reputation: 1

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

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!

Upvotes: 0

Views: 1371

Answers (3)

dougp
dougp

Reputation: 3087

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
}

Upvotes: 0

Piotr Palka
Piotr Palka

Reputation: 3159

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:

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

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

Upvotes: 0

Rob Farley
Rob Farley

Reputation: 15849

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.

Upvotes: 3

Related Questions