Importing .CSV data to a dataset

[mike55]

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:

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 doesn't 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.

Sample.txt

Edited September 14, 2007 by mike55

[Mondeo]

I would parse the csv file using a streamreader and use the readline method with string.split(",") to build the datatable manually.

[mike55]

Ok,

 

I have taken Mondeo's 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:

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.