locked
SqlBulkCopy.WriteToServer not handling null values in Datetime type column RRS feed

  • Question

  • Hi,

    Background:

    I am trying to insert csv data into sql table using Powershell script(Import-CSVtoSQL.ps1) given on following web page:

    https://gallery.technet.microsoft.com/scriptcenter/Import-Large-CSVs-into-SQL-216223d9

    Issue:

    This script is failing when I try to import null values (The null value is represented as blank in CSV file) from CSV file to datetime type column in a sql table. 

    it's throwing following error:

    Failure:
     System.InvalidOperationException: The given value of type String from the data source cannot be converted to type datetime of the specified target column. ---> System.FormatException: Failed to convert parameter value from a String to a DateTime. ---> 
    System.FormatException: String was not recognized as a valid DateTime.
       at System.DateTime.Parse(String s, IFormatProvider provider)
       at System.Convert.ToDateTime(String value, IFormatProvider provider)
       at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
       at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
       --- End of inner exception stack trace ---
       at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
       at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)
       --- End of inner exception stack trace ---
       at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata, Boolean isNull, Boolean& isSqlType, Boolean& coercedToDataFeed)
       at System.Data.SqlClient.SqlBulkCopy.ReadWriteColumnValueAsync(Int32 col)
       at System.Data.SqlClient.SqlBulkCopy.CopyColumnsAsync(Int32 col, TaskCompletionSource`1 source)
       at System.Data.SqlClient.SqlBulkCopy.CopyRowsAsync(Int32 rowsSoFar, Int32 totalRows, CancellationToken cts, TaskCompletionSource`1 source)
       at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinued(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
       at System.Data.SqlClient.SqlBulkCopy.CopyBatchesAsync(BulkCopySimpleResultSet internalResults, String updateBulkCommandText, CancellationToken cts, TaskCompletionSource`1 source)
       at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource`1 source)
       at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource`1 source)
       at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
       at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
       at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
       at CallSite.Target(Closure , CallSite , Object , Object )
    At C:\Users\adminpramod\Desktop\pram\Import-CSV2SQLTable.psm1:148 char:5
    +     throw $output
    +     ~~~~~~~~~~~~~
        + CategoryInfo          : OperationStopped: (Failure:

    ~DBA and BI Developer~ MCSA 2012 Please vote for this Post if found useful


    • Edited by psingla Tuesday, October 27, 2015 10:36 AM correction
    • Moved by Bill_Stewart Monday, November 30, 2015 4:09 PM This is not "custom design and consulting" forum
    Tuesday, October 27, 2015 9:57 AM

All replies

  • Your data is incompatible with the script.  You need to fix the data or change the schema of the table.

    Example:

    PS C:\scripts> [datetime]'2-2-20'
    
    Sunday, February 2, 2020 12:00:00 AM
    
    PS C:\scripts> [datetime]''
    Cannot convert value "" to type "System.DateTime". Error: "String was not recognized as a valid DateTime."
    At line:1 char:1
    + [datetime]''
    + ~~~~~~~~~~~~
        + CategoryInfo          : InvalidArgument: (:) [], RuntimeException
        + FullyQualifiedErrorId : InvalidCastParseTargetInvocationWithFormatProvider
    
    
    PS C:\scripts>

    Notice that the empty string causes the error.


    \_(ツ)_/

    Tuesday, October 27, 2015 12:03 PM
  • Thanks JRV for the reply.

    I understand your point but there should be some workaround to handle empty string without changing the schema because that is not the option in my case.


    ~DBA and BI Developer~ MCSA 2012 Please vote for this Post if found useful

    Tuesday, October 27, 2015 1:48 PM
  • If the database requires a value and will not accept a null then you cannot post a null to the database.  This has nothing to do with PowerShell it is the way the database schema is designed. 


    \_(ツ)_/

    Tuesday, October 27, 2015 2:03 PM
  • I will also say that the script is very badly written.  It does not understand how to use BulkCopy.  The author does not understand the data objects or what a CSV file is. 

    A CSV fil;e can be directly loaded into a database using ADO.

    Here is an example.

    # Copy-SQLBulk.ps1
    # This shows how to use BulkCopy in PowerShell by uploading a spreadsheet to an MSSQLServer data table.
    
    $filepath = 'C:\scripts\All.xlsx'
    $excelConnection="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$filepath;Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"
    $sqlConnection='Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=issue;Data Source=OMEGA\SQLEXPRESS;'
    $excelQuery='select * from [SearchResult$]'
    $tablename='SearchResult'
    
    Try{
        $conn = New-Object System.Data.OleDb.OleDbConnection($excelConnection) 
        $conn.open()
        $cmd=$conn.CreateCommand()
        $cmd.CommandText=$excelQuery
        $rdr=$cmd.ExecuteReader()
        
        # create the BC object
        $sqlbc=[System.Data.SqlClient.SqlBulkCopy]$sqlConnection
        $sqlbc.DestinationTableName=$tableName
        
        # add all columns - you can add as few  as you like.
        for($i=0; $i -lt $rdr.FieldCount;$i++){
            $fname=$rdr.GetName($i)
            Write-Host $fname -ForegroundColor green
            [void]$sqlbc.ColumnMappings.Add($fname, $fname)
        }
        
        # write all of the data to the table
        $sqlbc.WriteToServer($rdr)
    }
    Catch{
        Write-Host "$_" -ForegroundColor red
    }
    
    $sqlbc.Close()
    $conn.Close()
    
    
    

    The connection string is set for Excel. Just change it to a CSV connections string and this should work as long as the data passes the DB data integrity checks.


    \_(ツ)_/

    Tuesday, October 27, 2015 2:12 PM
  • The column in the database allows "null" but the PS script is not able to convert empty string to null which is causing the issue

    ~DBA and BI Developer~ MCSA 2012 Please vote for this Post if found useful

    Tuesday, October 27, 2015 2:14 PM
  • The following will work directly with a CSV:

    # ==============================================================================================
    #
    # Microsoft PowerShell Source File -- Created with SAPIEN Technologies PrimalScript 2012
    #
    # NAME: Copy-CsvToSQLServer.ps1
    #
    # AUTHOR: jvierra , Designed Systems & Services
    # DATE  : 8/16/2013
    #
    # COMMENT: This is a template only
    #           2015-05-22 Fixed some issues
    #
    # ==============================================================================================
    Param (
    	[parameter(Mandatory = $true)]
    	[string]$CsvPath,
    	[parameter(Mandatory = $true)]
    	[string]$CsvFile,
    	[parameter(Mandatory = $true)]
    	[string]$TargetServer,
    	[parameter(Mandatory = $true)]
    	[string]$TargetDatabase,
    	[parameter(Mandatory = $true)]
    	[string]$TargetTable,
    	[switch]$Truncate
    )
    
    Begin {
    	
    	Function Get-CsvReader {
    		Param (
    			$csvPath,
    			$csvFile
    		)
    		
    		$csvFile = $csvFile.Replace('.', '#')
    		$connStr = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Text;HDR=Yes;FORMAT=Delimited"' -f $csvPath
    		$csvConnection = New-Object System.Data.OleDb.OleDbConnection($connStr)
    		$csvConnection.Open()
    		$csvCommand = $csvConnection.CreateCommand()
    		$csvCommand.CommandText = "select * from [$csvFile]"
    		$csvCommand.ExecuteReader()
    	}
    }
    
    Process {
    	Try {
    		$csvReader = Get-CsvReader -csvPath $CsvPAth -csvFile $csvFile
    		$targetConnStr = "Data Source=$ServerName;Initial Catalog=$DbName;Integrated Security=True;"
    		$bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($targetConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
    		$bulkCopy.DestinationTableName = $TargetTable
    		$bulkCopy.WriteToServer($csvReader)
    		Write-Host "Table $SrcTable in $SrcDatabase database on $SrcServer has been copied to table $DestTable in $DestDatabase database on $DestServer"
    	} Catch {
    		throw $_
    	} Finally {
    		$csvReader.Close()
    		$bulkCopy.Close()
    	}
    }

    I have used both of these scripts many times.


    \_(ツ)_/



    • Edited by jrv Tuesday, October 27, 2015 2:20 PM
    Tuesday, October 27, 2015 2:18 PM
  • Thanks JRV for the scripts.

    I will try them and will update you accordingly.


    ~DBA and BI Developer~ MCSA 2012 Please vote for this Post if found useful

    Tuesday, October 27, 2015 2:30 PM
  • The following will work directly with a CSV:

    # ==============================================================================================
    #
    # Microsoft PowerShell Source File -- Created with SAPIEN Technologies PrimalScript 2012
    #
    # NAME: Copy-CsvToSQLServer.ps1
    #
    # AUTHOR: jvierra , Designed Systems & Services
    # DATE  : 8/16/2013
    #
    # COMMENT: This is a template only
    #           2015-05-22 Fixed some issues
    #
    # ==============================================================================================
    Param (
    	[parameter(Mandatory = $true)]
    	[string]$CsvPath,
    	[parameter(Mandatory = $true)]
    	[string]$CsvFile,
    	[parameter(Mandatory = $true)]
    	[string]$TargetServer,
    	[parameter(Mandatory = $true)]
    	[string]$TargetDatabase,
    	[parameter(Mandatory = $true)]
    	[string]$TargetTable,
    	[switch]$Truncate
    )
    
    Begin {
    	
    	Function Get-CsvReader {
    		Param (
    			$csvPath,
    			$csvFile
    		)
    		
    		$csvFile = $csvFile.Replace('.', '#')
    		$connStr = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Text;HDR=Yes;FORMAT=Delimited"' -f $csvPath
    		$csvConnection = New-Object System.Data.OleDb.OleDbConnection($connStr)
    		$csvConnection.Open()
    		$csvCommand = $csvConnection.CreateCommand()
    		$csvCommand.CommandText = "select * from [$csvFile]"
    		$csvCommand.ExecuteReader()
    	}
    }
    
    Process {
    	Try {
    		$csvReader = Get-CsvReader -csvPath $CsvPAth -csvFile $csvFile
    		$targetConnStr = "Data Source=$ServerName;Initial Catalog=$DbName;Integrated Security=True;"
    		$bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($targetConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
    		$bulkCopy.DestinationTableName = $TargetTable
    		$bulkCopy.WriteToServer($csvReader)
    		Write-Host "Table $SrcTable in $SrcDatabase database on $SrcServer has been copied to table $DestTable in $DestDatabase database on $DestServer"
    	} Catch {
    		throw $_
    	} Finally {
    		$csvReader.Close()
    		$bulkCopy.Close()
    	}
    }

    I have used both of these scripts many times.


    \_(ツ)_/



    I got following error:

    Exception calling "Open" with "0" argument(s): "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine." I tried to install the provider but with no luck.


    ~DBA and BI Developer~ MCSA 2012 Please vote for this Post if found useful

    Thursday, October 29, 2015 5:26 AM