Import CSV from PowerShell to SQL Server

Question

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

Arleigh Reyna · Accepted Answer · 2019-10-17 15:28:41Z

9

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

answered Oct 17, 2019 at 15:28

Arleigh Reyna

3691 silver badge6 bronze badges

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

2 Comments

Tax Max Over a year ago

Thanks. Maybe "truncate table" is not needed. According cmdlet help "Because you specify the Force parameter, if the database, schema, and table do not exist, this cmdlet creates them."

Tax Max Over a year ago

And Depending on SQL version, Invoke-Sqlcmd may require -TrustServerCertificate.

Collectives™ on Stack Overflow

Import CSV from PowerShell to SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related