Reading and posting a .csv file into a SQL database

[dougancil]

I have the following code:

Private Function BuildDataTable(ByVal fileFullPath As String, ByVal seperator As Char) As DataTable   
        Dim myTable As New DataTable("MyTable")   
        Dim i As Integer = 0   
        Dim myRow As DataRow = Nothing   
        Dim fieldValues As String() = Nothing   
        'string FileToRead = Server.MapPath(fileFullPath);      
        Dim myReader As System.IO.StreamReader = Nothing   
        Try   
            'Open file and read first line to determine how many fields there are.      
            myReader = System.IO.File.OpenText(fileFullPath)   
            'string ReadContents = myReader.ReadToEnd();      
            fieldValues = myReader.ReadLine().Split(seperator)   
            'Create data columns accordingly      
            For i = 0 To fieldValues.Length - 1   
                myTable.Columns.Add(New DataColumn(fieldValues(i)))   
            Next   
            'Adding the first line of data to data table      
            myRow = myTable.NewRow()   
            For i = 0 To fieldValues.Length - 1   
                myRow(i) = fieldValues(i).ToString()   
            Next   
            myTable.Rows.Add(myRow)   
            'Now reading the rest of the data to data table      
            While myReader.Peek() <> -1   
                fieldValues = myReader.ReadLine().Split(seperator)   
                myRow = myTable.NewRow()   
                For i = 0 To fieldValues.Length - 1   
                    myRow(i) = fieldValues(i).ToString()   
                Next   
                myTable.Rows.Add(myRow)   
            End While   
        Finally   
            myReader.Close()   
        End Try   
  
        Return myTable   
    End Function   
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)   
        Dim dt As DataTable = BuildDataTable("\\MSBWEB3\wwwroot\Webfile1\Data\doug.csv", ","c)   
        'Creating a new table for storing in database      
        'Extracting phone number, patient name, doctors name, appointment date and appointment time'     
        Dim dtForDB As New DataTable()   
        dtForDB.Columns.Add("PhoneNumber")   
        dtForDB.Columns.Add("PatientName")   
        dtForDB.Columns.Add("DrName")   
        dtforDB.Columns.Add("apptdate")   
        dtforDB.Columns.Add("apptime")   
        DateTime(dt)   
        dt.ToLongDateString()   
        dt.ToShortDateString()   
        For Each dr As DataRow In dt.Rows   
            dtForDB.Rows.Add(dr("Phonenumber").ToString(), dr("PatientName").ToString(), dr("DrName").ToString(), dr("apptdate").ToString(), dr("appttime").ToString())   
        Next   
    End Sub

that is creating a datatable based on the following data:
Resource: (text)
Facility: (text)
Type:(all)
From Date: 07/12/2010 - To Date 07/12/2010
Sort by:Time
Include Referring source/physician:No
Footer:Default
Criteria:None
(csv appointment data)

The only information that I'm needing from this data starts after the bold section and then from that (as you can see from my code) all I will need is

Phone Number [phone]
Patients Name Mr. Example
Doctors Name Dr. Demo
Appointment Time 7:30 AM
Appointment date 7/12/2010

This file is being uploaded by a client through a web page that will immediately post it to a sql server. The file comes to me as a .csv file (though as you can see, it's really not .csv) and I'm wondering the best methodology for sorting through it and getting only the information that I need. Would it be better to sort the data or do a select? Also, since I'm new to this methodology, could someone please include a sample of the code that they would use to achieve something like this?

Thank you

Doug

[rohand]

Visii below link. may be it will helpful to you but not sure as your file is not really a csv file.

http://www.hotblue.com/article0000.aspx?a=0006

If you will fix the problem and get success then can you please share your logic with us, so that it will be helpful to others as well if they will face same type of situation.

[dougancil]

Rohand,

I've looked at that page but that page isn't specific enough to do what I'm asking. Granted it does have some of what I would consider as "key elements" to my code, it doesn't have the real heavy lifting I'm needing this code to do. I may have this figured out in the next few days but it's going to take someone smarter than me to do it. I'll post back what I find out.