Retrieving data from a .CSV file

[mike55]

Hi all,

I am retrieving data from a .CSV file into a dataset, the problem that I am encountering is that the code/dataset seems to not pick up certain characters such as the 0 (Zero) characters at the start of the .CSV data field.

Here is the code that I am using, all/any suggestions welcomed...

  Retrieve the data from the .csv file.
    Private Function RetrieveOrganisationUploadedMembership(ByRef dstCSVFile As DataSet, ByVal filePath As String, Optional ByRef errorMessage As String = Nothing) As Boolean

        RetrieveOrganisationUploadedMembership = True

        Dim cnnCSVConnection As OleDb.OleDbConnection

        Try
            Dim fileName As String
            Dim path As String
            Dim dcUpload(0) As DataColumn

            fileName = System.IO.Path.GetFileName(filePath)
            path = filePath.Replace(fileName, Nothing)

            cnnCSVConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Text;")
            Dim cmdCSVCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM " & fileName, cnnCSVConnection)
            Dim adpCSV As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmdCSVCommand)

             Open a connection to the .csv file and retrieve the data to a dataset.
            cnnCSVConnection.Open()
            adpCSV.Fill(dstCSVFile, "MemberData")

             Set the primary key.
            dcUpload(0) = dstCSVFile.Tables(0).Columns("MobileNumb")
            dstCSVFile.Tables(0).PrimaryKey = dcUpload

            Return RetrieveOrganisationUploadedMembership
        Catch ex As Exception
            errorMessage = "Problem Encountered, " & ex.Message.ToString.TrimEnd
            RetrieveOrganisationUploadedMembership = False
        Finally
             Close the database connection.
            cnnCSVConnection.Close()
        End Try
    End Function

Mike55.

 

[bri189a]

Are you saying its treating a 0 as a number instead of a string? Perhaps if the CSV data had quotes around it: 000073 instead of 000073 may work to tell whatever is determining the column type that its a string, not a number data type?

Just a thought.

 

[mike55]

Are you saying its treating a 0 as a number instead of a string? Perhaps if the CSV data had quotes around it: 000073 instead of 000073 may work to tell whatever is determining the column type that its a string, not a number data type?

Just a thought.

Thats how I have got around the problem at the moment, but it does not seem to be the most appropriate solution. What I was doing before was to read the .csv file using a reader, and then loop though each line looking for ",". But this solution is not suitable for the approach that I am taking.

Mike55.