I need to import a list of medication names & directions from a website & exclude all other info (which is between the list of medication names). Then I need Access to generate a list of the medication names separated by commas.
What is the best way to go about this? Is Access even the best tool for this?
This is an example of what the data looks like if pasted into notepad from the webpage:
<-----------START PASTE----------->
Medication
Amiodarone 200 mg Tab
SIG: give 2 tablets (400 mg) by oral route once daily
10/26/2010
Standing
Medication
Ecotrin Low Strength 81 mg Tab
SIG: give 1 tablet (81 mg) by oral route once daily
10/26/2010
Standing
Medication
Enalapril Maleate 5 mg Tab
SIG: give 1 tablet (5 mg) by oral route once daily
10/26/2010
Standing
Medication
Ferrous Sulfate 325 mg (65 mg Iron) Tab
SIG: give 1 tablet (325 mg) by oral route 2 times a day
10/26/2010
Standing
Medication
Lantus 100 unit/mL Sub-Q
SIG: inject 5 units subcutaneously at bedtime
10/26/2010
Standing
<-----------END PASTE----------->
So basically I need to extract the line of data after the word "MEDICATION" & before the date. Then I need to export them (without all the blank lines) into a comma seperated variable format so the above data would look more like:
Amiodarone 200 mg Tab SIG: give 2 tablets (400 mg) by oral route once daily,Ecotrin Low Strength 81 mg Tab SIG: give 1 tablet (81 mg) by oral route once daily,Enalapril Maleate 5 mg Tab SIG: give 1 tablet (5 mg) by oral route once daily,Ferrous Sulfate 325 mg (65 mg Iron) Tab SIG: give 1 tablet (325 mg) by oral route 2 times a day,Lantus 100 unit/mL Sub-Q SIG: inject 5 units subcutaneously at bedtime
*****************UPDATE:
In the past I always pasted into notepad & word. I just tried pasting into excel after reading some other posts in this forum & I think the problem is half solved!
When I paste the above data into Excel it lines up the names of the medications into the H column followed by the directions of the medications in the next H row. Then the second medication in H3 & its directions are in H4.
So now I need Access to extract the data from H1 & H2 & separate them with a comma from H3 & H4 & separate them with a comma from H5 & H6, etc.
I think this is a much more feasible task.
How do I do it?