SqlBulkCopy Change output column types

Hi,

I am trying to import a large excel file 200+ columns into a sql table, when the excel file is created sometimes several of columns will be float but the main table was built with these columns being string, which in most cases it is.

I am trying to create a generic method of importing the data from the excel file into the table but I need to convert the output data to match the sqltable datatype, which I dont know how to do.

also I have noticed some columns in main sql table are nvarchar(max) but if import the excel file into new table the ssame column may show as nvarchar(255).

I have got the following code so far but it fails due to column type mismatch.

Code:

---

Dim excelConnectionString As String = String.Empty
        Dim filePath As String = "Y:\=Capita CEX=\LivePerson\WebHistory\Week 41 Import.xlsx"
        Dim fileExt As String = Path.GetExtension(filePath)
        Dim strConnection As [String] = "Data Source=sbe02\build;Initial Catalog=LivePerson;Integrated Security=True"

        If fileExt = ".xls" OrElse fileExt = "XLS" Then
            excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & filePath & "'" & "; Extended Properties ='Excel 8.0;HDR=Yes'"
        ElseIf fileExt = ".xlsx" OrElse fileExt = "XLSX" Then
            excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties=Excel 12.0;Persist Security Info=False"
        End If




        Dim excelConnection As New OleDbConnection(excelConnectionString)
        Dim cmd As New OleDbCommand("Select * from [sheet1$]", excelConnection)
        excelConnection.Open()
        Dim dReader As OleDbDataReader
        dReader = cmd.ExecuteReader()

        ' Set up the bulk copy object.
        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(strConnection)
            bulkCopy.BulkCopyTimeout = 30
            bulkCopy.BatchSize = 1000
            bulkCopy.DestinationTableName = "tbl_Raw_TestImport"


            ' Set up the column mappings source and destination. (recommended to do this)
            For col = 0 To dReader.FieldCount - 1
                bulkCopy.ColumnMappings.Add(ColName(col), ColName(col))
            Next

            ' Write from the source to the destination.
            Try
                bulkCopy.WriteToServer(dReader)

            Catch ex As Exception
                MsgBox(ex.Message)

            Finally
           
                dReader.Close()
            End Try
        End Using


        MsgBox("Congratulations! Successfully Imported.")
        excelConnection.Close()

---

+++++++++++++++++++++++++++++++++++++++
As an experiment I change the column type in the excel file and did a manually import for comparison and apart from the new file having a couple of columns where the nvarchar length did not match I still received an error

'The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.'

Create a DataTable that matches the datatabase table structure, use your readbulk copyer to fill it, but then send the datatable to the bulk copy instead of the reader.

-tg

Hi,

I have just tried create the table as you said then fill the data adapter but got exact same error 'the given value of type String from the data source cannot be converted to type nvarchar of the specified target column.'

Is there anyway to find out exactly which column is causing this error, as said there is around 200 columns.

could it be an issue with NULLs