CODEWITHSUNDEEP
sql-serverpowershell
Tim
Tim

Reputation: 55

Import CSV from PowerShell to SQL Server

I was trying to import a CSV file from PowerShell over to my SQL Server database. I've already created the database and tables with columns. I got the data on a CSV file that I need to import.

I tried to research and found a bit of code I modified so it should be working, but when I run the code I get the error:

Import-CsvToSql : exeption calling "writetoserver" with "1" argument(s): "The transaction is iether not associated with the connection or has been completed"

But I hasn't imported the data to the table, so I don't know what's wrong.

Here is the code I've got so far:

Import-Module csvsqlimport
Import-CsvToSql -Csv C:\Users\Tim\Desktop\POWERSHELL\AutoParts.csv `
    -SqlServer DHCP_SERVER -Database FeilAuto4 `
    -Table dbo.Reservedele -FirstRowColumns -Delimiter ";" -Truncate

Upvotes: 3

Views: 21605

Answers (1)

Arleigh Reyna
Arleigh Reyna

Reputation: 369

You may want to try the "SqlServer" module as it is being kept up to date by Microsoft and has multiple SQL cmdlets. The only downside is that you will have to separate the script into multiple commands based on their cmdlets.

## Install module if not installed, this is a one time install.
Install-Module SqlServer

## Input Variables
$csvPath = "C:\Users\Tim\Desktop\POWERSHELL\AutoParts.csv"
$csvDelimiter = ";"
$serverName = "DHCP_SERVER"
$databaseName = "FeilAuto4"
$tableSchema = "dbo"
$tableName = "Reservedele"

## Truncate Table
Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query "TRUNCATE TABLE $tableSchema.$tableName"

## Import CSV into SQL
Import-Csv -Path $csvPath -Delimiter $csvDelimiter | Write-SqlTableData -ServerInstance $serverName -DatabaseName $databaseName -SchemaName $tableSchema -TableName $tableName -Force

Upvotes: 9

Related Questions