Link to home
Create AccountLog in
Avatar of dchid
dchid

asked on

Regular Expression to collect data from html page and place in an array

Hi
My regex is not the best and I am looking to collect some data from this page to add to a database: http://www.iso.org/iso/support/currency_codes_list-1.htm

I am basically hoping to get hold of the entity - currency - alphabetic code - numeric code and place them in an array

Looking at the source code the code for the table begins around line 200 and is formatted like so:
                  <tr >       
                  <td valign="top">
                        AFGHANISTAN
                  </td>
                  <td valign="top">
                        Afghani
                  </td>
                  <td valign="top">
                        AFN
                  </td>
                  <td valign="top">
                        971
                  </td>
                  </tr>
                  <tr class="zebra">                         
                  <td valign="top">
                        ÅLAND ISLANDS
                  </td>
                  <td valign="top">
                        Euro
                  </td>
                  <td valign="top">
                        EUR
                  </td>             
                  <td valign="top">
                        978
                  </td>             
                  </tr>             
                  <tr >                         
                  <td valign="top">
                        ALBANIA
                  </td>             
                  <td valign="top">
                        Lek
                  </td>             
                  <td valign="top">
                        ALL
                  </td>             
                  <td valign="top">
                        008
                  </td>             
                  </tr>             
                  <tr class="zebra">
                  <td valign="top">
                        ALGERIA
                  </td>
                  <td valign="top">
                        Algerian Dinar
                  </td>             
                  <td valign="top">
                        DZD
                  </td>             
                  <td valign="top">
                        012
                  </td>             
                  </tr>  

I have the following code wrote by kambiz for another problem, which works how I would like:

// fetch data  
$exchange_data = file_get_contents('http://www.bloomberg.com/javascripts/currdata.js');
// apply regular expression
preg_match_all("/\['(\w{3}):CUR'\]\s*=\s*(\d+(.\d+)?);/", $exchange_data, $matches);
// create array of CURRENCY => RATE
$exchange_rates = array_combine($matches[1], $matches[2]);

I am hoping someone could help with some regex which helps me collect the data I want from http://www.iso.org/iso/support/currency_codes_list-1.htm

Thanks
Avatar of kaufmed
kaufmed
Flag of United States of America image

How about the following? The values will be stored in capture group 1 (Entity), 2 (Currency), 3 (Alphabetic code), and 4 (Numeric code).
<td valign="top">\s*([\S ]*)\s*</td>\s*<td valign="top">\s*([\S ]*)(?:<a[^>]*>[^<]*</a>)?\s*</td>\s*<td valign="top">\s*([A-Z]{3})\s*</td>\s*<td valign="top">\s*(\d{3}|Nil)\s*

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of dchid
dchid

ASKER

Thank you both for the replies.
Kaufmed, what you supplied seems to pull what I need from the html but I am having trouble looping through it to add into database fields (entity - currency - alphabetic code - numeric code) and have not yet managed to get it to work as required.

Ray I will look at what you have supplied there and see if I can get that to work and post back with how it goes.

Thanks again
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Here is a screenshot of what the results of the above look like.
untitled.PNG
It seems I missed the detail about the data that kambiz mentions when I originally looked through it. The following accounts for this, but the lines that have multiple data would have to be dealt with as a special case--the data is captured in the submatch, but you would have to separate the submatch into separate lines. You may be better off using an approach like Ray_Paseur's.
$exchange_data = file_get_contents('test.html');
preg_match_all('#<td valign="top">\s*([\S ]*)\s*</td>\s*<td valign="top">\s*([\S ]*?(?:<br /><br />[\S ]*?)*)\s*(?:<a[^>]*>[^<]*</a>)?\s*</td>\s*<td valign="top">\s*([A-Z]{3}(?:<br /><br />[A-Z]{3})*)\s*</td>\s*<td valign="top">\s*((?:\d{3}|Nil)(?:<br /><br />(?:\d{3}|Nil))*)#', $exchange_data, $matches, PREG_SET_ORDER);

print count($matches) . "<br /><br />";

for ($i = 0; $i < count($matches); $i++)
{
	print "<b>Entity:</b> " . $matches[$i][1] . " | <b>Currency:</b> " . $matches[$i][2] . " | <b>Alpha Code:</b> " . $matches[$i][3] . " | <b>Num. Code:</b> " . $matches[$i][4] . "<br />\n";
}

Open in new window

Avatar of dchid

ASKER

Thank you, you have all been most helpful, I have got all your suggestions partially working how I want, I am going to look into inserting them into the database now and I will post back how this goes.

Kaufmed the for loop you gave has helped alot and with minor alterations I have it working with the other code above.
Avatar of dchid

ASKER

Thanks to you all, really appreciate all your help.