Importing CSV file to Datatable - problem with converting data type

Question

I have to import lots of csv files into my table and I do some cleanup before storing to sql server. I define datatable and pass it to generic function along with filename. here is my code...

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim retVal As DialogResult
        'OpenFileDialogBankTrx.Filter = "CSV File(*.csv)|*.csv)"
        retVal = OpenFileDialogBankTrx.ShowDialog()
        If retVal = Windows.Forms.DialogResult.OK Then
            If OpenFileDialogBankTrx.CheckFileExists = True And OpenFileDialogBankTrx.CheckPathExists = True Then
                TextBox1.Text = OpenFileDialogBankTrx.FileName.ToString()
            End If
        End If
        Stop
        Dim dt As New DataTable
        dt.Columns.Add("TrxDate", GetType(Date))
        dt.Columns.Add("CheckNo", GetType(String))
        dt.Columns.Add("DbAmount", System.Type.GetType("System.Decimal"))
        dt.Columns.Add("CrAmount", System.Type.GetType("System.Decimal"))
        dt.Columns.Add("Description", GetType(String))
        dt.Columns.Add("Comment", GetType(String))
        If CExcel.GetCsvData(TextBox1.Text, dt) Then
            DataGridView1.DataSource = dt
       End If
    End Sub
    Public Shared Function GetCsvData(ByVal CSVFileName As String, ByRef CSVTable As DataTable) As Boolean
        Dim reader As Microsoft.VisualBasic.FileIO.TextFieldParser
        Dim currentRow As String()
        Dim dr As DataRow = CSVTable.NewRow()
        Dim index As Integer = 0
        Dim currColumnDataType As Object
        reader = My.Computer.FileSystem.OpenTextFieldParser(CSVFileName)
        reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
        reader.Delimiters = New String() {","}
        While Not reader.EndOfData
            Try
                currentRow = reader.ReadFields()
                For currColumn = 0 To CSVTable.Columns.Count - 1
                    dr.Item(currColumn) = currentRow(currColumn)                
		Next
                CSVTable.Rows.Add(dr)
                index += 1
            Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                MsgBox("Line " & ex.Message & "is not valid." & vbCrLf & "Terminating Read Operation.")
                reader.Close()
                reader.Dispose()
                Return False
            End Try
        End While
        reader.Close()
        reader.Dispose()
    End Function

I get error when it hits

on line: dr.Item(currColumn) = currentRow(currColumn)               

When it reads from csv file all fields are in string, but I want to convert it to appropriate format of each column in passed table.

How can I do this? Can anypone please help?

Thanks.

Answer 1

Hello,

The best thought here is to use TryParse. There are overloads for these so take time to review them and try the examples out first.

Integer.TryParse DateTime.TryParse Decimal.TryParse

---

KSG

Answer 2

Thanks for the reply.

But I would not know which datatype I should parse, since I will be passing different datatable and each table will have different data type. So based on what I pass I would like to convert approproate column type.

e.g.  dr.Item(currColumn) = CTYPE(currentRow(currColumn) , CSVTable(currColumn).DataType())            

or dr.Item(currColumn) = currentRow(currColumn) (CSVTable(currColumn).DataType.Name)

I think I am close but still missing something. Can anyone please help?

Thanks.

Answer 3

Thanks for the reply.

But I would not know which datatype I should parse, since I will be passing different datatable and each table will have different data type. So based on what I pass I would like to convert approproate column type.

e.g.  dr.Item(currColumn) = CTYPE(currentRow(currColumn) , CSVTable(currColumn).DataType())            

or dr.Item(currColumn) = currentRow(currColumn) (CSVTable(currColumn).DataType.Name)

I think I am close but still missing something. Can anyone please help?

Thanks.

So you are changing the initial question in that "I have to import lots of csv files into my table" from "table" to "tables" ? If this is what you mean then a generic function would seem to be the wrong direction.

I would like to suggest a different approach, examine the input CSV files for patterns in regards to column configuration. Either create a class or function for each different configuration thinking file structure. There may be more code but in the end, my guess is this strategy would be easier to maintain than putting everything into a generic function. I do a very similar operation importing data from PDF files into a backend database. As new unknown structures come in a template can be made quicker in a separate class then attempting to integrate the new structure into a generic function.

There are advantages and disadvantages to both methods, a generic function more likely than not will be harder to maintain if a file or database table structure changes while a template based approach although easier to maintain can become an issue if the overall design is not well thought out before implementing.

---

KSG

Answer 4

I started with different classes for my two of csv files and then I thought it would be easy to maintain if I make generic function and convert csv column data from generic string to passed table column data type. And I thought it would be easy to convert but not finding any solution.

Thanks for your suggestion.

Answer 5

OK finally I came up with following solution which can take DataTable variable and import generic data from CSV.

    Public Shared Function GetCsvData(ByVal CSVFileName As String, ByRef CSVTable As DataTable) As Boolean
        Dim reader As Microsoft.VisualBasic.FileIO.TextFieldParser
        Dim currentRow As String()
        Dim dr As DataRow
        Dim sqlColumnDataType As String
        reader = My.Computer.FileSystem.OpenTextFieldParser(CSVFileName)
        reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
        reader.Delimiters = New String() {","}
        While Not reader.EndOfData
            Try
                currentRow = reader.ReadFields()
                dr = CSVTable.NewRow()
                For currColumn = 0 To CSVTable.Columns.Count - 1
                    sqlColumnDataType = CSVTable.Columns(currColumn).DataType.Name
                    Select Case sqlColumnDataType
                        Case "String"
                            If String.IsNullOrEmpty(currentRow(currColumn)) Then
                                dr.Item(currColumn) = ""
                            Else
                                dr.Item(currColumn) = Convert.ToString(currentRow(currColumn))
                            End If
                        Case "Decimal"
                            If String.IsNullOrEmpty(currentRow(currColumn)) Then
                                dr.Item(currColumn) = 0
                            Else
                                dr.Item(currColumn) = Convert.ToDecimal(currentRow(currColumn))
                            End If
                        Case "DateTime"
                            If String.IsNullOrEmpty(currentRow(currColumn)) Then
                                dr.Item(currColumn) = ""
                            Else
                                dr.Item(currColumn) = Convert.ToDateTime(currentRow(currColumn))
                            End If
                    End Select
                Next
                CSVTable.Rows.Add(dr)
            Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                MsgBox("Line " & ex.Message & "is not valid." & vbCrLf & "Terminating Read Operation.")
                reader.Close()
                reader.Dispose()
                Return False
            Finally
                dr = Nothing
            End Try
        End While
        reader.Close()
        reader.Dispose()
        Return True
    End Function

Hope it will be helpful to someone.

Thanks.

Answer 6

Hi mak102,
Thank you for share your idea with us.
Have a nice day.

---

Bob Wu [MSFT]
MSDN Community Support | Feedback to us

Answer 7

This is very interesting to me.  I am trying to do something very similar.  How is this function called from a Button_Click event?

I tried this:

    Private Sub Button7_Click(sender As System.Object, e As System.EventArgs) Handles Button7.Click
        Dim cnn As SqlConnection
        Dim connectionString As String
        Dim sql As String
        connectionString = "data source=Excel-PC\SQLEXPRESS;" & _
        "initial catalog=NORTHWIND;Trusted_Connection=True"
        cnn = New SqlConnection(connectionString)
        cnn.Open()

        GetCsvData("C:\Users\Excel\Desktop\OrdersTest.csv", dbo.Orders)

    End Sub

I'm getting an error message that says "'dbo' is not declared. It may be inaccessible due to its protection level"

This is my ConnectionString:

SqlConnection("Server=Excel-PC\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;")

This is my Table:  'Orders' or [dbo].[Orders]

This is my file:  StreamReader = File.OpenText("C:\Users\Excel\Desktop\OrdersTest.csv")

---

Ryan Shuell