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 it's 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 it's a string, not a number data type?

 

Just a thought.

[mike55]

Are you saying it's 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 it's 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.