SqlBulkCopy fails if data is longer than table field

[darkdusky]

I have an import from excel to sql server. this works fine unless excel file data is longer than table field. The table is a temporary table which is created by making a copy of the destination table - so column lengths etc are same as the destination table.

Is it possible to set SqlBulkCopy to ignore errors or to truncate rather than fail. Or is it possible to get System.Data.OleDb.OleDbConnection to trim blanks because the extra length is usually blank spaces at end of data.

Here is relevant part of my code:

'Create connection strings
Dim sExcelConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & sExcelFileName.ToString & ";" & _
"Extended Properties='Excel 12.0 Xml;HDR=YES';"

Dim sSQLConnectionString As String = DBConnect.ConnectionString
Try
'Copy Excel File to Temp Table
Dim OleDbConn As OleDbConnection = New OleDbConnection(sExcelConnectionString)
Dim OleDbCmd As OleDbCommand = ExcelConnection()
OleDbConn.Open()

Dim dr As OleDbDataReader = OleDbCmd.ExecuteReader()
Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(sSQLConnectionString)
bulkCopy.DestinationTableName = sSQLTable
bulkCopy.BatchSize = 500
bulkCopy.WriteToServer(dr)

OleDbConn.Close()


............

Protected Function ExcelConnection() As OleDbCommand
Dim xConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & GetExcelFileName().ToString & ";" & _
"Extended Properties='Excel 12.0 Xml;HDR=YES';"

Try
Dim objXConn As New System.Data.OleDb.OleDbConnection(xConnStr)
objXConn.Open()
Dim myTableName = objXConn.GetSchema("Tables").Rows(0)("TABLE_NAME")
Dim objCommand As New OleDbCommand((String.Format("SELECT * FROM [Sheet1$]", myTableName)), objXConn)

Return objCommand
Catch ex As Exception
MessageBox.Show("Error: " & ex.Message.ToString)
End Try
End Function

 

[cjard]

In the part that says:

SELECT * FROM [Sheet1$]


How about you add some TRIM() and MID() in there?

 

[Tom]

You are first extracting the data from an excel file and storing in a datatable. Before calling your sqlBulkCopy, you can validate & format all the info in that table.

Before the BulkCopy you can either loop thru your DataTable and at that time validate each of your columns data to your specification. I've found this to be the most time consuming out of most of my imports but needs to be done.

Depending on the amount of records in your files, the following may be faster to perform then looping thru all the records if this is the only thing you need to validate. Dynamically add a new column to your table, use DataColumn.Expresssion to format your fields to the len you want, then drop the original column and finally rename the new column if you have too.