Importing CSV columns into SQL table without Sqlcmd

Question

I’m currently importing a csv file and inserting some of the results into a table.

Unfortunately I’m using Sqlcmd, but the module isn’t installed on the relevant server and can’t be installed (out of my control).

Is there a way to manage the exact same as the below, outside of SqlCMD?

Example Code:


$server = 's'
$database = 'd'
$table = 't'

Import-CSV C:\Test\Test.csv | ForEach-Object {Invoke-Sqlcmd `
  -Database $database -ServerInstance $server `
  -Query "INSERT INTO $table VALUES ('$($_."Column One")',
                                     '$($_."Column Two")',
                                      NULL,
                                     '$($_.""Column Three"")')"
    }

Look into Sql Server Integration Services (SSIS). You can also use the ADO.Net objects if you need to. — Joel Coehoorn
– Joel Coehoorn, Commented Dec 4, 2019 at 17:27

Joel Coehoorn · Accepted Answer · 2022-06-08 20:36:23Z

3

You can try the ADO.Net objects:

# Hardcode the table name, unless you *REALLY* trust your users.
$SQL = "INSERT INTO TableName VALUES (@Column1, @Column2, NULL, @Column3)"

$conn = new-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=$server;Database=$database;Integrated Security=True;"
$cmd = new-Object System.Data.SqlClient.SqlCommand($SQL, $conn)

# Use actual types and lengths from the database here
$c1 = $cmd.Parameters.Add("@Column1", [System.Data.SqlDbType]::NVarChar, 20)
$c2 = $cmd.Parameters.Add("@Column2", [System.Data.SqlDbType]::NVarChar, 20)
$c3 = $cmd.Parameters.Add("@Column3", [System.Data.SqlDbType]::NVarChar, 20)

$conn.Open()

Import-CSV C:\Test\Test.csv | ForEach-Object {
   $c1.Value = $_."Column One"
   $c2.Value = $_."Column Two"       
   $c3.Value = $_."Column Three"

   $cmd.ExecuteNonQuery()
}
$conn.Close()

This will also fix any issues with apostrophes in the data, which would have caused HUGE problems in the original.

edited Jun 8, 2022 at 20:36

answered Dec 4, 2019 at 17:35

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fler Over a year ago

Thanks Joel. I am receiving an error of "Failed to convert parameter value from a string to a datetime", which I believe is coming from here: $c8 = $cmd.Parameters.Add("@Column8", [System.Data.SqlDbType]::DateTime) $c9 = $cmd.Parameters.Add("@Column9", [System.Data.SqlDbType]::DateTime)

Joel Coehoorn Over a year ago

No, that exception can only come from the ExecuteNonQuery() call, but is ultimately caused by assigning an invalid or ambiguous date string to the Value property of the parameter object, not from creating the parameter itself. And if you have more than a handful of these, you might also think about using an array, rather than individual variables.

Richard Newman · Accepted Answer · 2019-12-04 17:39:08Z

1

You could try and use the bulk insert command, search for a simple SQL connection and query execution - the exact one that Joel Coehoorn posted, if you have bulk permissions and the server has access to the file itself. you could also add parameters to it and then call it however you see fit. Meaning you could foreach files in folder, call the bulk insert with your connection.

BULK INSERT TableName
FROM 'LinkToSourceFile.csv' WITH (
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
FIRSTROW = 2
    );

edited Dec 4, 2019 at 17:39

answered Dec 4, 2019 at 17:31

Richard Newman

844 bronze badges

Collectives™ on Stack Overflow

Importing CSV columns into SQL table without Sqlcmd

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related