Member Avatar for dougancil

I have an asp.net web page that a user is loading a file to that I want to parse data out of. The following text is some sample data (I'm told that it's supposed to be .csv but as you can tell, it's really not.)

Resource:Wills MD, Robert P; Ward LPC LSOTP, Tracy K; Wages MD, John; von Leonrod FNP C, Sara; Teague PA C, Donna; Prewitt Buchanan MD, Laura K; NO - Medical Assistant; NO - C2 Pickup; Morgan FNP C, Nancy; Minot APRN, S. Reid; Medical Assistants; Martin LCSW, Margaret; Lepore MA LPC, Bruno; Iseminger PA C, Amy C; Intern; Horwath PA C, Jessica; Hart PA C, Mary Jo; Haden LCSW, Marion M; Group Services; Frank MD, Brannon R; DuBois MD, Craig; Chandler LCSW, Michele; C2 Pickup - Main Office; Brothers LCSW, Gary; Bates FNP C, Gina; Anderson MD, Christine
Facility:Austin Pain Associates - North Office; Austin Pain Associates - Kyle; Austin Pain Associates - Georgetown; Austin Pain Associates - Cedar Park; Austin Pain Associates
Type:(all)
From Date: 07/12/2010 - To Date 07/12/2010
Sort by:Time
Include Referring source/physician:No
Footer:Default
Criteria:None
","Appointments","Brothers LCSW, Gary","Austin Pain Associates","Monday, July 12, 2010","Time","Patient Name","Patient ID","Appt. Type","Ref. Source/ Physician","Phone","Type","DOB ","Brothers LCSW, Gary","Austin Pain Associates","7/12/2010 12:00:00AM","Time","Patient Name","Patient ID","Appt. Type","Phone","Type","DOB "," 7:30 AM","Muniz Jr, Reynaldo A","27380","SO - Ind Psych Therapy - 45","","(512) 740-7023","Mobile","03/25/1965"," 7:30 AM","Muniz Jr, Reynaldo A","27380","SO - Ind Psych Therapy - 45","(512) 740-7023","Mobile","03/25/1965","Truong, Kimphuong P","(512) 836-5472","Financial Class:","Medicare","Status:","Auth Complete","","","Age:","45 yrs","Notes:","counseling - conniesiver

What I need to parse out of that data is basically

Date
Time
Drs Name
Patient Name
Appt Time
Appt Date
and then post that to a SQL database. Since it's not really in .csv format I was told that the best way would be to handle this with StreamReader and parse the information out with regex. I've never used StreamReader before so I just want to make sure that I'm approaching this from the right direction. Any help would be appreciated.

Thank you

Doug

  • 3 Contributors
  • 2 Replies
  • 337 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Ramesh S

Dani AI

Generated

posted a classic messy "pseudo-CSV" export: metadata lines, repeated headers and comma-separated tokens. is right that reading the file with StreamReader (or a higher-level reader) is a valid starting point, and is also correct that treating a clean CSV as a table via OleDb can work — but only when the file is well-formed and the OLE DB provider is available. A practical, reliable strategy is: (1) normalize/skip the metadata, (2) use a CSV-aware tokenizer to get fields, (3) identify fields by type (DateTime, time, "Last, First" names) using TryParse/regex, and (4) load into SQL via a staging table and bulk insert.

A compact workflow you can apply immediately:

  1. Inspect the file for encoding, BOM and the row where real data begins (look for the header token like "Appointments" or "Patient Name") and skip preceding metadata lines.
  2. Tokenize with a CSV-aware reader (prefer a library such as CsvHelper or, without extra packages, Microsoft.VisualBasic.FileIO.TextFieldParser) so quoted fields and embedded commas are handled. For each token use DateTime.TryParse (with en-US culture if appropriate) to detect dates/times and simple regex heuristics for name formats.

Example (C#) skeleton:

using Microsoft.VisualBasic.FileIO;

using (var parser = new TextFieldParser(path)) {
  parser.TextFieldType = FieldType.Delimited;
  parser.SetDelimiters(",");
  while (!parser.EndOfData) {
    var fields = parser.ReadFields();
    foreach (var t in fields) {
      if (DateTime.TryParse(t, out var dt)) { /* map date/time */ }
      else if (Regex.IsMatch(t, @"^[^,]+,\s*[^,]+$")) { /* last, first - doctor/patient */ }
    }
  }
}

If you must use regex directly, safe patterns include \b\d{1,2}/\d{1,2}/\d{4}\b for dates and \b\d{1,2}:\d{2}\s*(AM|PM)?\b for times; always canonicalize with TryParse afterward. Use OleDb only for well-formed CSVs (and remember driver availability on the server). When writing to SQL, insert into a staging table, validate rows, log parsing errors and then use parameterized commands or SqlBulkCopy/Table-Valued Parameters for performance.

Recommended Answers

All 2 Replies

Member Avatar for dnanetwork

yes streamreader is a good approach..

Member Avatar for Ramesh S

Hi Doug,

StreamReader is one of the good option to parse the csv file. But you need to write extra code to parse each row in the csv file and then post to sql server.

You can also try to use System.Data.OleDb namespace to read and load the csv file content into DataSet/DataTable easily and then update to SQL Server. This approach will help you to avoid writing your own parsing mechanism.

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.