none
Importing CSV file to Datatable - problem with converting data type

    Soru

  • 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.

    06 Mart 2012 Salı 20:39

Yanıtlar

  • 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.


    • Düzenleyen mak102 07 Mart 2012 Çarşamba 19:57
    • Yanıt Olarak İşaretleyen Bob Wu-MTModerator 08 Mart 2012 Perşembe 08:48
    07 Mart 2012 Çarşamba 17:31

Tüm Yanıtlar

  • 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

    06 Mart 2012 Salı 21:37
  • 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.

    06 Mart 2012 Salı 22:45
  • 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

    07 Mart 2012 Çarşamba 01:20
  • 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.

    07 Mart 2012 Çarşamba 16:22
  • 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.


    • Düzenleyen mak102 07 Mart 2012 Çarşamba 19:57
    • Yanıt Olarak İşaretleyen Bob Wu-MTModerator 08 Mart 2012 Perşembe 08:48
    07 Mart 2012 Çarşamba 17:31
  • Hi mak102,
    Thank you for share your idea with us.
    Have a nice day.

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

    08 Mart 2012 Perşembe 08:48
    Moderatör
  • 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



    • Düzenleyen ryguy72 22 Şubat 2013 Cuma 03:03
    22 Şubat 2013 Cuma 02:45