First time use of a datatable

[dougancil]

I'm creating a datatable for the first time and I'm having some difficulties. I have the following code:

Protected Function Dataparse(ByVal Save As String) As String
        Dim DataSet ds = new DataSet(); 
        Dim Datatable dt = new Datatable();
         System.Data.Odbc.OdbcConnection conn;   
            System.Data.Odbc.OdbcDataAdapter da;   
            string connString;   
            string importFolder = @"\\MSBWEB3\wwwroot\Webfile1\Reminders\Doug_Ancil\";   
            string fileName = "doug.csv";   
            connString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + importFolder + ";";   
            conn = new System.Data.Odbc.OdbcConnection(connString);   
            da = new System.Data.Odbc.OdbcDataAdapter("select * from [" + fileName + "]", conn);    
            da.Fill(ds,"Sample"); 
 
    End Function
 

What I'm trying to do is as follows:

I have the following data that basically I only need a few bits of information from:

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) xxx-7023","Mobile","03/25/1965"," 7:30 AM","Muniz Jr, Reynaldo A","27380","SO - Ind Psych Therapy - 45","(512) xxx-7023","Mobile","03/25/1965","Truong, Kimphuong P","(512) xxx-5472","Financial Class:","Medicare","Status:","Auth Complete","","","Age:","45 yrs","Notes:","counseling - conniesiver

The only things I need from this are:

Patients Name

Drs Name

Patients Phone Number

Appt Time

Appt Date

and the rest of the information I can discard. A customer uploads this as a .csv file (even though it really isn't as you can see) and I'd like to parse the needed information and post that to my SQL database and discard the rest. I think I can do this with a dataset but I've never built that before. The fields from the customer will always be the same and the fields I will need will always be the same.

Also, the date time has to be in the format of yyyy/mm/dd:hhmm and the phone number always has to have 512 as a prefix.

This is my first time creating a datatable and I've read how to create them but I'm having some problems. Any help would be appreciated.

Thank you,

Doug

[Christopher Nigro]

Hey Doug,

Like we talked about - you cannot use ADO.NET to work with that file: the file is not tabular. It is not in a format that can yeild columns and rows. Just because there are a bunch of commas in there doesn't mean that it is a proper CSV file. To understand this, try to open that file in Excel. You will not get a structure that is similiar to a database table. When you previously asked "can I use a DataTable", I thought you meant you would define the DataTable, parse the file by hand, assign the parsed values to DataColumns in DataRows, add the DataRows to the DataTable, add the DataTable to a Dataset and update the Dataset with the appropriate data commands. It is a bit much, but it is doable. I would just parse the file row by row and commit the data with a DataCommand that is defined with a SPROC.

In short, there is no single step from that source file to any object that represents a database or data table. You need to code an in-between routine to transform the source.