Importing Text / Raw Data from a Web Site

[shibu8]

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?

[ajetrumpet]

indeed, access is not the program for it. you might be better off (time wise), to use MS query located in excel to query the actual table that the information is in on the website, clean it up in excel then import what you want right into access.

if you've never used query before, when you run it, it automatically indicates HTML tables that are outputted on the page with a yellow right arrow. click the arrow and import the table into the sheet.