Powershell: error report while Import CSV file to SQL server

Question

How can I skip or say "error" on SQL server when the data does not match with data type? such as ID has nchar(2) on SQL server and I have csv file that one of data is '1234' for ID. How can I have error sign or error report for that data in SQL server. It is ok that I can have separate excel file about all the errors. I just want to know which data got error.

And make the script without stopping even there is an error.

Here is my script

$today = (get-date).Date
$DataImport = Import-Csv -Path (Get-ChildItem filename | where { $_.CreationTime.Date -eq $today } )    
$DataTable = Out-DataTable -InputObject $DataImport
write-DataTable -serverInstance servername -Database DBname -TableName Table name -Data $DataTable

Functions Out-DataTable and Write-DataTable have been defined.

Did your see this - stackoverflow.com/questions/4766250/… ? — Leptonator
– Leptonator, Commented Sep 18, 2013 at 14:45

sqlint · Accepted Answer · 2013-09-18 17:33:42Z

1

You can use TRY and CATCH.

$today = (get-date).Date
TRY
{
$DataImport = Import-Csv -Path (Get-ChildItem filename | where { $_.CreationTime.Date -eq $today } )    
$DataTable = Out-DataTable -InputObject $DataImport
write-DataTable -serverInstance servername -Database DBname -TableName Table name    -Data $DataTable
}
CATCH 
{     
$_.Exception.Message
"PS code here that writes error to txt or to excel."
}

answered Sep 18, 2013 at 17:33

sqlint

1,1199 silver badges8 bronze badges

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

Comments

Chad Miller · Accepted Answer · 2013-09-18 19:51:19Z

Write-DataTable function is using the the .NET SqlBulkCopy class http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx, unlike the T-SQL BULK INSERT command or some data loading utilities like SSIS, there's isn't an option to commit some rows and log error rows. You could play with changing the -BatchSize parameter to 1 instead of 50,000, this way it will commit each row and instead of rolling back entire batch and you can catch the exception. Note: This will be slower for large datasets.

Another solution is to use the T-SQL BULK INSERT command specifying an error file:

$query = "BULK INSERT dbname..table FROM 'C:\temp\test.csv' WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', ERRORFILE = 'C:\temp\test.err')"

Then call the query using invoke-sqlcmd or sqlcmd.exe.

Collectives™ on Stack Overflow

Powershell: error report while Import CSV file to SQL server

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related