I'm sorry - it's banking information, so I can't do that. Here is another example - fake data.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
I'm sorry - it's banking information, so I can't do that. Here is another example - fake data.
Sorry, it auto converted it to image. Thought you just wanted a visual. Hold on. Let me try again
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
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.
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
Thank you. So I would need to load CVSHelper? is it a free add on?