none
SQLBulkCopy ColumnMappings problem RRS feed

  • Question

  • Hello,

     

    I am importing a CSV file into an SQL table.  I want to limit the number of fields being imported to 170 which is the maximum number of fields in the SQL table.  The ColumnMappings does limit the number of fields, but the data in each field seems to be truncated.  Thanks for any ideas.

     

    Here is the code:

     

    Code Snippet

    Dim outfile As String = Path.GetFileName(csvFileName)

    Dim filepath As String = Path.GetDirectoryName(csvFileName)

    '

    Using myConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filepath & ";" & "Extended Properties=""Text;HDR=No;FMT=Delimited""")

    Using myCommand As New OleDbCommand("SELECT * FROM " & outfile & ";", myConnection)

    Using conn As SqlConnection = New SqlConnection(My.Settings.SISConnection)

    Using bc As System.Data.SqlClient.SqlBulkCopy = New SqlBulkCopy(My.Settings.SISConnection, SqlBulkCopyOptions.KeepIdentity)

    '

    For x = 0 To 169

       bc.ColumnMappings.Add(x, x)

    Next

    '

    Try

       myConnection.Open()

       conn.Open()

    '

       bc.DestinationTableName = SalesTempTable

       bc.WriteToServer(myCommand.ExecuteReader())

    Catch ex As Exception

       ErrorExit(ex)

    Finally

       conn.Close()

       myConnection.Close()

    End Try

    End Using

    End Using

    End Using

    End Using

     

     

    Thursday, April 3, 2008 10:29 PM

All replies

  • If the data is being truncated in the DataReader (and not during the WriteToServer method) you will probably need to create a schema.ini file to define the fields in your text file.

     

    It also might help to know whether the text is being truncated at the same spot. For example, is the text being truncated at 255 characters? Or, is it being truncated because the destination column in SQL Server isn't large enough to accomodate the data? Or, is it random?

     

    Friday, April 4, 2008 1:32 PM
  •  

    I have very similar issue

    I dont limit the column number, but i did notice that bulk insert only puts 255 characters in DB table

    Destination table is well over 1000 char in all the columns and value of that particular column has 800+ chars

    So it looks like everything working fine, table has all the right data, but one column which is usually large always truncated to 255

    Monday, April 14, 2008 6:23 PM