Importing .CSV data to a dataset

mike55

mike55

Well-known member
Joined
Mar 26, 2004
Messages
726
Location
Ireland
Hi all

Firstly apologies if the following posting is in the incorrect location. I have a .CSV file from which I read the data and put it in a dataset, the dataset then undergoes various actions and I end up putting the data into a sql server2005 database. The following is the code that I am using to access to .CSV file and extract the data:
Code: 
Public Function RetrieveCSVData() As Boolean

            RetrieveCSVData = True

            Dim cnnCSVConnection As OleDb.OleDbConnection = Nothing

            Try

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

                If dstCSVFile.Tables.Count > 0 Then
                    dstCSVFile.Tables.Remove("MemberData")
                End If
                fileName = System.IO.Path.GetFileName(mFilePath)
                path = mFilePath.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")
                LogEntry
                logAction.WriteLog(System.Configuration.ConfigurationManager.AppSettings("FilePath"), "Suretxtlog.txt", "RetrieveCSVData Passed!", "UploadDownload-RetrieveCSVData", "SuretxtWebService")

                Return RetrieveCSVData
            Catch ex As Exception

                logAction.WriteLog(System.Configuration.ConfigurationManager.AppSettings("FilePath"), "Suretxtlog.txt", ex.Message, "UploadDownload-RetrieveCSVData", "SuretxtWebService")
                RetrieveCSVData = False
            Finally
                 Close the database connection.
                cnnCSVConnection.Close()
            End Try
        End Function

The problem is that the select command doesnt always return all the data in the .CSV file. The current problem that I am having is that the first column is being returned as blank. I have attached the .csv file for people to have a look at (Note the file had to be uploaded as a .txt file). The problem only seems to occur when there is a limited amount of data in the .csv file. If I submit a file with 5 or 133 records no problems occur.

After some further testing, I can confirm that the problem only exists when I have only one record in the .csv file. Should I go and add in a second record, and then do the import the problem does not occur.

Mike55.
 

Attachments

Last edited by a moderator:
Ok,

I have taken Mondeos advice and I am attempting to parse the file using a streamreader. To actually get the string result into a datatable I came across the following code: http://www.hotblue.com/article0000.aspx?a=0006

The only problem that I am having is translating the following validation statement:
Code: 
 Regex re=new Regex("((?<field>[^\",\\r\\n]+)|\"(?<field>([^\"]|\"\")+)\")(,|(?<rowbreak>\\r\\n|\\n|$))");

Instead of this statement, I need to have a validator that handles 4 columns, the first two are alphanumeric values, the 3rd must be a phone number, and the 4th an email address.

Any suggestions?

Mike55.
 
Back
Top