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 Subthat 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 (999) 999-9999
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