Loops, branching, reorganizing data

[Mbrower]

For the last 4 years I have used MS to gather patient demographics from various Practice Management Softwares in use in Dental offices. I use the data to do integration with our Clinical Records software. I have been very pleased with MS and the support forums.

I now find that we have a need to do queries against our database and I have successfully created those queries using the DBCONNECT and DBQUERY tips found in the forum. I write the queries to a csv file for further processing. That is what I have been struggling with for several days. I am very confused at how to go about reorganizing the data.

The csv file looks like:

,new,patient,(510) 222-1245,Flap and Osseous Surgery ,Amoxicillin 875mg ,25,
,new,patient,(510) 222-1245,Flap and Osseous Surgery ,Clindamycin 300mg ,25,
,new,patient,(510) 222-1245,Flap and Osseous Surgery ,Penicillin 250mg ,25,
,new,patient,(510) 222-1245,Gingivectomy-Gingivoplasty ,Amoxicillin 875mg ,25,
,new,patient,(510) 222-1245,Gingivectomy-Gingivoplasty ,Clindamycin 300mg ,25,
,new,patient,(510) 222-1245,Gingivectomy-Gingivoplasty ,Penicillin 250mg ,25,
,old,patient,(234) 123-2345,Flap and Osseous Surgery ,Amoxicillin 875mg ,25,
,old,patient,(234) 123-2345,Flap and Osseous Surgery ,Penicillin 250mg ,25,

I want it to end up like (Combining multiple fields based on phone#)
, new, patient , (510) 222-1245 , Flap and Osseous Surgery - Gingivectomy-Gingivoplasty , Amoxicillin 875mg - Clindamycin 300mg - Penicillin 250mg ,

, old , patient , (234) 123-2345 , Flap and Osseous Surgery , Amoxicillin 875mg - Penicillin 250mg ,

I am currently using CSVtoArray to start the process as shown in the following code

Code: Select all

CSVFileToArray>%Script_Dir%\out.csv,csvData

mdl>csvdata_count

    Let>Rec=1
    let>rec1=csvdata_1_3
    Let>script=
       While>csvdata_%rec%_3=%rec1%
    //mdl>csvdata_%rec%_3
    Let>rec=rec+1
     let>rec2=csvdata_%rec%_5
       //mdl>%script%
    concat>%script%,%rec2%
       EndWhile
      

mdl>%script%

The code is working on combining the 5th field which is prescriptions at this time. I was hoping to learn the technique before I worked on the rest of the reorganization. It works but obviously it is doubling up because of field 4. I admit to being a complete novice in programming and I apologize if this has been covered in the forums before, any help offered will be appreciated.