I'm trying to automate the import of csv data into sql. All files, but one, are imported ok, so there is no issue with the code. This particular file has a mixed data type column. And when importing data, it loads data as Double, instead of String. As a result, the values that have words, are imported as null. I tried to convert that column into String (ds.Tables(0).Columns(4).DataType), but it makes no difference. When I try to check the type right after the conversion attempt, it shows runtime type, not a string. I have spent a few days researching similar issues and tried everything I could find. Hopefully someone here can offer help on converting the column properly, as it doesn't work for me. Please see the code below. Thanks!
Private Sub ImportIntoSqlBulk(SqlConnString As SqlConnection, ConnStringSql As String, TableNameSql As String, Filenmcsv As String) SqlConnString.ConnectionString = ConnStringSql SqlConnString.Open() Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SqlConnString) bulkCopy.DestinationTableName = TableNameSql Try bulkCopy.WriteToServer(GetCsvData(PathImpt, Filenmcsv)) SqlConnString.Close() Catch ex As Exception Email_Subject = "Financial Data Import from Excel Error" successflag = "N" Email_Body = "There was an error importing data into " & TableNameSql & " from excel. Error message: " & ex.Message Send_Email() SqlConnString.Close() Exit Sub End Try End Using End Sub Public Function GetCsvData(ByVal strFolderPath As String, ByVal strFileName As String) As DataTable Dim strConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFolderPath & ";Extended Properties=""Text;HDR=YES;IMEX=1""" Dim conn As New OleDbConnection(strConnString) Try conn.Open() Dim cmd As New OleDbCommand("SELECT * FROM [" & strFileName & "]", conn) Dim da As New OleDbDataAdapter() da.SelectCommand = cmd Dim ds As New DataSet() da.Fill(ds) If strFileName = "CheckRegister.csv" Then Convert.ToString(ds.Tables(0).Columns(4).DataType) ds.Tables(0).Columns(4).DataType.GetType() End If da.Dispose() Return ds.Tables(0) Catch ex As Exception Return Nothing Finally conn.Close() End Try End Function