469,317 Members | 1,966 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,317 developers. It's quick & easy.

First time use of a datatable

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

Expand|Select|Wrap|Line Numbers
  1. Protected Function Dataparse(ByVal Save As String) As String
  2.         Dim DataSet ds = new DataSet(); 
  3.         Dim Datatable dt = new Datatable();
  4.          System.Data.Odbc.OdbcConnection conn;   
  5.             System.Data.Odbc.OdbcDataAdapter da;   
  6.             string connString;   
  7.             string importFolder = @"\\MSBWEB3\wwwroot\Webfile1\Reminders\Doug_Ancil\";   
  8.             string fileName = "doug.csv";   
  9.             connString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + importFolder + ";";   
  10.             conn = new System.Data.Odbc.OdbcConnection(connString);   
  11.             da = new System.Data.Odbc.OdbcDataAdapter("select * from [" + fileName + "]", conn);    
  12.             da.Fill(ds,"Sample"); 
  13.  
  14.     End Function
  15.  
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
Jul 15 '10 #1
1 1452
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.
Jul 15 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Steve Bishop | last post: by
1 post views Thread by BK Kim | last post: by
11 posts views Thread by Jason | last post: by
3 posts views Thread by goldie11 | last post: by
3 posts views Thread by Torben Laursen | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.