question

Alla-9729 avatar image
0 Votes"
Alla-9729 asked karenpayneoregon commented

Issue with importing csv file into SQL - mixed data type

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-databasedotnet-visual-basic
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Some of the samples use these properties: Dim strConnString As String = ". . .;Extended Properties='Excel 8.0;HDR=YES;IMEX=1' ". Check if it gives the same error.


0 Votes 0 ·

It's csv file though, not excel

0 Votes 0 ·

Can you put together several lines with one line with the issue and one that works fine. If the lines are long shorten them up.

0 Votes 0 ·
Alla-9729 avatar image Alla-9729 karenpayneoregon ·

Not quite sure I understand the request. The issue with importing column 4 - it has numbers in some rows and words in others - within the same column.

For example:

1234
void 1234

  • in the case above 1234 imports as 1234, but 'void 1234' imports as NULL. If I debug code during import - it interprets column 4 values as Double, even though in SQL that column is specified as nvarchar

0 Votes 0 ·

It's super simple, take some lines from your file and post them here.

0 Votes 0 ·
Alla-9729 avatar image
0 Votes"
Alla-9729 answered karenpayneoregon commented

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

205967-image.png



image.png (8.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Well how can I copy that as an image???

0 Votes 0 ·
Alla-9729 avatar image
0 Votes"
Alla-9729 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Alla-9729 avatar image
0 Votes"
Alla-9729 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

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



figure1.png (22.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Alla-9729 avatar image
0 Votes"
Alla-9729 answered karenpayneoregon commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CvsHelper and FastMember are both free.

0 Votes 0 ·