Member Avatar for 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 (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

Edited by Ezzaral because: Edited sensitive data
  • 2 Contributors
  • 2 Replies
  • 136 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by dougancil

Dani AI

Generated

The file described by has a variable preamble and then a CSV-like block. The current approach (reading the first line as headers and using String.Split) is fragile: it will break on quoted commas, extra header lines, and when the true header is not the file's first line. A more reliable flow is: locate the actual header row, parse CSV with a proper parser that respects quotes, convert/validate types (phone, date/time) and then bulk-insert into SQL.

A practical VB.NET pattern uses Microsoft.VisualBasic.FileIO.TextFieldParser to scan rows until you detect the header (look for tokens like "phone" or "patient"), build a typed DataTable from that header, then read subsequent fields into rows. TextFieldParser handles quoted fields and different delimiters. Example sketch:

Imports Microsoft.VisualBasic.FileIO

Using tf As New TextFieldParser(filePath)
  tf.SetDelimiters(","c)
  tf.HasFieldsEnclosedInQuotes = True
  Dim headerFound As Boolean = False
  Dim dt As New DataTable("Appointments")

  While Not tf.EndOfData
    Dim fields = tf.ReadFields()
    If Not headerFound Then
      If fields.Any(Function(f) f.IndexOf("phone", StringComparison.OrdinalIgnoreCase) >= 0) Then
        For Each h In fields : dt.Columns.Add(h.Trim()) : Next
        headerFound = True
      End If
    Else
      Dim row = dt.NewRow()
      For i = 0 To Math.Min(fields.Length - 1, dt.Columns.Count - 1)
        row(i) = fields(i).Trim()
      Next
      dt.Rows.Add(row)
    End If
  End While
End Using

For inserting to SQL Server, use SqlBulkCopy with explicit ColumnMappings for speed and reliability (or parameterized INSERTs if you need per-row checks). Before bulk load, normalize phone numbers with a regex, parse apptdate/apptime with DateTime.TryParseExact (specify culture and expected formats), and log rows that fail validation.

References: TextFieldParser docs and SqlBulkCopy docs. ’s link is a start, but a parser + validation + bulk-insert pipeline is the more robust solution for production.

Recommended Answers

All 2 Replies

Member Avatar for rohand

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

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.

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.