The given value of type String from the data source cannot be converted to type

Tagged: ,

This topic contains 4 replies, has 3 voices, and was last updated by  Pramod Singla 1 year, 10 months ago.

  • Author
    Posts
  • #31988

    Pramod Singla
    Participant

    Description:I am getting error while importing CSV file data into SQL table. I am trying to export data from a table into a CSV file and then import that CSV file into another table.The error is due to null values in non string data types columns in a table.

    Error:System.InvalidOperationException: The given value of type String from the data source cannot be converted to type int of the specified target column. —> System.FormatException: Failed to convert parameter value from a String to a Int32.

    SW stack:Poweshell 4.0,sql server 2012,table schema is same for both source and destination table.

    Code: following is stripped code that i am using to import the CSV file.

      ####### Build the sqlbulkcopy connection, and set the timeout to infinite######### 
        $BulkCopy = New-Object Data.SqlClient.SqlBulkCopy ($ConnectionString,[System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
        $BulkCopy.DestinationTableName = $TableName
        $BulkCopy.bulkcopyTimeout = 0
        $BulkCopy.batchsize = $BatchSize
    
        # Create the datatable, and autogenerate the columns. 
        $DataTable = New-Object System.Data.DataTable
    
        # Open the text file from disk 
        $Reader = New-Object System.IO.StreamReader ($CSVFileFullName)
        $Columns = (Get-Content $CSVFileFullName -First 1).Split($CSVDelimiter)
    
        ##get Column Names from first line
        if ($FirstRowColumnNames -eq $true) {
          $null = $Reader.readLine()
        }
    
        #create data table with cloumn names read from first line  
        foreach ($Column in $Columns) {
          $null = $DataTable.Columns.Add()
        }
    
        # Read in the data, line by line 
        while (($Line = $Reader.readLine()) -ne $null) {
    
          $null = $DataTable.Rows.Add($Line.Split($CSVDelimiter))
          $RowCounter++;
    
          if (($RowCounter % $BatchSize) -eq 0) {
            $BulkCopy.WriteToServer($DataTable)
            Write-Output "$RowCounter rows have been inserted in $($elapsed.Elapsed.ToString())."
            $DataTable.Clear()
          }
        }
    
        # Add in all the remaining rows since the last clear 
        if ($DataTable.Rows.Count -gt 0) {
          $BulkCopy.WriteToServer($DataTable)
          $DataTable.Clear()
        }
    
    
        Write-Output "Script complete. $RowCounter rows have been inserted into the database."
        Write-Output "Total Elapsed Time: $($elapsed.Elapsed.ToString())"
    
      }
    
  • #32008

    Don Jones
    Keymaster

    That's really a SQL Server error, not a PowerShell thing. You're trying to put string data into a SQL Server integer column. Without seeing your CSV data and the SQL table, I can't tell you specifically what the problem is (and no need to post those things; again, this is really a SQL Server problem).

    As a test, consider using SQL Server to manually import the CSV (e.g., via SSIS maybe). You'll get a much more specific error message.

  • #32016

    Pramod Singla
    Participant

    I tried importing below csv content using SSIS and it worked fine but when I try to import the same csv file using powershell then I am get above specified error.
    a,b,c
    1,2,4
    2,,5
    3,,

    SSIS converts the empty string to 0 (in this case).

  • #32084

    Dan Potter
    Participant

    why? sql does this.

    SELECT *
    INTO newtable
    FROM table

  • #32110

    Pramod Singla
    Participant

    Sorry Dan but I didn't get your point.

You must be logged in to reply to this topic.