locked
Problem with reading data from CSV file into DataSet. RRS feed

  • Question

  • Hi everybody,

    I want to import CSV file into SQL server.
    Before that, I read all data from CSV file into DataSet to check data before updating them into DB.

    And I get problem with DataType in DataSet.
    For example : CSV file has 2 columns : Name, Password
    If some first rows of column Password is Number only, and the other rows 's  data is  string  type
    => DataTable create column's DataType itself => The other rows which is string can not be read into DataTable.

    Please help me how I can change all data in CSV file to string before reading them into DataTable to avoid the above problem.

    Thanks in advance.
    Thursday, November 8, 2007 1:34 PM

Answers

  • pvphuc,

     

    Based on your post, you want to change the data in CSV file to String before importing into DataTable object. I would like to provide you the following code snippet in a Class:

     

    Code Block

    Public Class DataTableHelper

        Public Shared Sub ProduceCSV(ByVal dt As DataTable, ByVal file As System.IO.StreamWriter, ByVal WriteHeader As Boolean)

            If WriteHeader Then

                Dim arr As String() = New String(dt.Columns.Count - 1) {}

                For i As Integer = 0 To dt.Columns.Count - 1

                    arr(i) = dt.Columns(i).ColumnName

                    arr(i) = GetWriteableValue(arr(i))

                Next

                file.WriteLine(String.Join(",", arr))

            End If

            For j As Integer = 0 To dt.Rows.Count - 1

                Dim dataArr As String() = New String(dt.Columns.Count - 1) {}

                For i As Integer = 0 To dt.Columns.Count - 1

                    Dim o As Object = dt.Rows(j)(i)

                    dataArr(i) = GetWriteableValue(o)

                Next

                file.WriteLine(String.Join(",", dataArr))

            Next

            file.Flush()

            file.Close()

        End Sub

        Public Shared Function GetWriteableValue(ByVal o As Object) As String

            If o Is Nothing Then

                Return ""

            ElseIf o.ToString().IndexOf(",") = -1 Then

                Return o.ToString()

            Else

                Return """" + o.ToString() + """"

            End If

        End Function

    End Class

     

     

    Hope that can help you.

    Tuesday, November 13, 2007 4:06 AM
  • Thanks for your support.

    I think I found the solution for DataType problem in DataTable by reading content of CSV file into a string.
    After that, I split data by "," character and insert data into DataTable.

    Tuesday, November 13, 2007 7:55 AM

All replies

  • pvphuc,

     

    Based on your post, you want to change the data in CSV file to String before importing into DataTable object. I would like to provide you the following code snippet in a Class:

     

    Code Block

    Public Class DataTableHelper

        Public Shared Sub ProduceCSV(ByVal dt As DataTable, ByVal file As System.IO.StreamWriter, ByVal WriteHeader As Boolean)

            If WriteHeader Then

                Dim arr As String() = New String(dt.Columns.Count - 1) {}

                For i As Integer = 0 To dt.Columns.Count - 1

                    arr(i) = dt.Columns(i).ColumnName

                    arr(i) = GetWriteableValue(arr(i))

                Next

                file.WriteLine(String.Join(",", arr))

            End If

            For j As Integer = 0 To dt.Rows.Count - 1

                Dim dataArr As String() = New String(dt.Columns.Count - 1) {}

                For i As Integer = 0 To dt.Columns.Count - 1

                    Dim o As Object = dt.Rows(j)(i)

                    dataArr(i) = GetWriteableValue(o)

                Next

                file.WriteLine(String.Join(",", dataArr))

            Next

            file.Flush()

            file.Close()

        End Sub

        Public Shared Function GetWriteableValue(ByVal o As Object) As String

            If o Is Nothing Then

                Return ""

            ElseIf o.ToString().IndexOf(",") = -1 Then

                Return o.ToString()

            Else

                Return """" + o.ToString() + """"

            End If

        End Function

    End Class

     

     

    Hope that can help you.

    Tuesday, November 13, 2007 4:06 AM
  • Thanks Bruno Yu,

    I understand your solution.
    But how about user can change data in CSV file after exporting.



    Tuesday, November 13, 2007 5:55 AM
  • You can consider to display the DataTable that imports from the CSV file in DataGridView control on the form. Then change the cell in DataGridView, update and save to the DataTable.

     

    In my opinion, this is the most simple way to change data in your CSV file.

     

    Hope that can help you.

    Tuesday, November 13, 2007 6:19 AM
  • Thanks for your support.

    I think I found the solution for DataType problem in DataTable by reading content of CSV file into a string.
    After that, I split data by "," character and insert data into DataTable.

    Tuesday, November 13, 2007 7:55 AM
  •  

    Dear pvphuc,

     

    may i know how u done it? i have a few different csv file. and i face 2 problem when i read them into my datagridview.

     

    1) the text will missing if the column data contain interger(same as ur problem even i set the csv file to all text)

    2) the column dataformat is text but whole column data is number. and some of my data is start from 00123. then it automatic help me to change it into 123 as integer.

     

    may i know how u solve it? thanks

     

    Best Regards

    Terence Chua

    Monday, March 17, 2008 8:21 AM