Issue with importing csv file into SQL - mixed data type

Alla 1 Reputation point
2022-05-26T14:23:05.55+00:00

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
Azure SQL Database
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,542 questions
{count} votes

5 answers

Sort by: Most helpful
  1. Alla 1 Reputation point
    2022-05-26T20:00:53.54+00:00

    I'm sorry - it's banking information, so I can't do that. Here is another example - fake data.

    205967-image.png


  2. Alla 1 Reputation point
    2022-05-26T20:24:32.097+00:00

    Sorry, it auto converted it to image. Thought you just wanted a visual. Hold on. Let me try again

    0 comments No comments

  3. Alla 1 Reputation point
    2022-05-26T20:26:24.243+00:00

    AcctName1 12345678 5/25/2022 Some Name 1 1234 83383
    AcctName2 12345678 8/17/2021 Some Name 2 5678 3500
    AcctName3 12345678 8/20/2021 Some Name 3 Voided - 78906555 -246.16

    0 comments No comments

  4. Karen Payne MVP 35,016 Reputation points
    2022-05-26T20:41:09.717+00:00

    I'm guessing the file is comma delimited than use CsvHelper to parse data to a model/class (I did your file) and FastMember to create a DataTable.

    205968-figure1.png

    I wrote this in C# and converted to VB

    Public Class Account  
    	Public Property Column1() As String  
    	Public Property Column2() As Long  
    	Public Property Column3() As DateTime  
    	Public Property Column4() As String  
    	Public Property Column5() As String  
    End Class  
    Option Infer On  
      
    Imports System  
    Imports System.Data  
    Imports System.Globalization  
    Imports System.IO  
    Imports System.Linq  
    Imports System.Text  
    Imports CsvHelper  
    Imports CsvHelper.Configuration  
    Imports CsvHelperExample.Models  
    Imports FastMember  
      
      
    Friend Class Operations  
      
    	Public Shared Function ReadAccounts() As DataTable  
    		Dim fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Accounts.csv")  
    		Dim configuration = New CsvConfiguration(CultureInfo.InvariantCulture) With {  
    			.Encoding = Encoding.UTF8,  
    			.Delimiter = ",",  
    			.HasHeaderRecord = False  
    		}  
      
    		Using fs = File.Open(fileName, FileMode.Open, FileAccess.Read, FileShare.Read)  
    			Using textReader = New StreamReader(fs, Encoding.UTF8)  
    			Using csv = New CsvReader(textReader, configuration)  
    				Dim table As New DataTable()  
    				Using reader = ObjectReader.Create(csv.GetRecords(Of Account)().ToList())  
      
    					table.Load(reader)  
    					Return table  
    				End Using  
    			End Using  
    			End Using  
    		End Using  
    	End Function  
    End Class  
      
      
    
    0 comments No comments

  5. Alla 1 Reputation point
    2022-05-26T20:44:16.4+00:00

    Thank you. So I would need to load CVSHelper? is it a free add on?