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("\\path\Data\xxx.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")
        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
 

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 (###) ###-####
Patients Name Doe, John
Doctors Name xxxxxxxx
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?

And Christopher if you look at this and respond, please know that this code has been tested and it does work, with the sample data from above. It is achievable, just a bit of work and as you can see, still a work in progress.

Thank you,

Doug

[Frinavale]

What control are you using to display the table?
In that control you can specify which columns should be displayed and which shouldn't.

-Frinny