Splitting multiple DD/MM/YY in a string

[woldie]

Hi,

I've got an interesting problem I'm trying to resolve. What I have is a CSV file which has the company names, and then notes next to each company in the CSV file.

Now what I'm trying to do is that I need to get the bwid according for each company name in the CSV file, and then insert the notes into a separate table with the bwid. This bit of code is not a problem, but what I have in the notes section is something like this...

Company Name,22/06/04 - some blurb 13/1/04 - Some more blurb goes here. 23/2/04 - blurb 16/02/03 - blurb

So what I want to is to split the second bit of this line, so it reads as follows:-

22/06/04 - some blurb
13/1/04 - Some more blurb goes here.
23/2/04 - blurb
16/02/03 - blurb

So therefore I insert into the DB, bwid, date, and the text for each date we have split up.

This is the code:

<?
if (isset($userfile))
{
// reads from CSV file
$allfiles = file ($userfile);

for ($n=0;$n<sizeof($allfiles);$n++)
{
$line = explode(",",$allfiles[$n]);
echo "<b>$line[0]</b>" . '<br><br>';

$result=mysql_query("select bwid
from booking_webassess
where compname='$line[0]'");
list ($DBbwid)=mysql_fetch_row($result);
echo "$DBbwid" . '<br><br>';

// this is where I should split up $line[1], the notes bit.



}
}
else
echo 'No file uploaded';

?>

I don't know if I need some reg exp to do this?

Hope this makes any sense.

Woldie :o)

[Salsa]

Someone else may have a better idea, but it seems like you could do it with preg_match_all [us2.php.net]. You might capture all the substrings with something like:

'@(\d{1,2}/\d{1,2}/\d{1,2} - (?!\d{1,2}/\d{1,2}/\d{1,2} - ).*?)@'

The above will almost certainly not work out of the box as I just typed it out without any reference and it is completely untested. The scheme though is to match patterns beginning with "nn/nn/nn - " (or "n/n/n - " or combinations of the two) followed by anything except the next "nn/nn/nn - ".

To separate the dates from the related text, you could do it all within the regex, but I think it would be simpler to use strpos to find the first "-" in each element of your matches, then split apart the data there--rather than explode(' - ',... because what if there's a " - " in the text?

I hope this helps.

[woldie]

Apologies in responding back, but thanks Salsa for the feedback, regular expression is not my forte, but I will give that a go, I do see what you are trying to do. :o)

Many Thanks

:o)

[woldie]

Hi Guys,

I'm having a spot of bother, I'm getting this error:

Fatal error: Only variables can be passed by reference in /home/sites/domain.com/web/admin/booking-sys/insert_source_db_JJ_list_22-08-04_add_notes.php on line 21

Here's the code:

<?
if (isset($userfile))
{
// connect to database
mysql_connect("localhost","username","password") or die ("could not find file");
mysql_select_db ("tracking") or exit();

$allfiles = file ($userfile);

for ($n=0;$n<sizeof($allfiles);$n++)
{
$line = explode(",",$allfiles[$n]);
echo "<b>$line[0]</b>" . '<br><br>';

$result=mysql_query("select bwid
from booking_webassess
where compname='$line[0]'");
list ($DBbwid)=mysql_fetch_row($result);
echo "$DBbwid" . '<br><br>';

// this is the error
preg_match_all("@(\d{1,2}/\d{1,2}/\d{1,2} - (?!\d{1,2}/\d{1,2}/\d{1,2} - ).*?)@",$line[1],PREG_PATTERN_ORDER);

echo $out[0][0] . ", " . $out[0][1] . "\n";
echo $out[1][0] . ", " . $out[1][1] . "\n";



}
}
else
echo 'No file uploaded';

?>

Thanks.

[woldie]

Hi,

I've got that problem to work, but it prints out the dates twice.

The regex does kinda work, so well done!

Code:

<?
if (isset($userfile))
{
// connect to database
mysql_connect("localhost","username","password") or die ("could not find file");
mysql_select_db ("tracking") or exit();

$allfiles = file ($userfile);

for ($n=0;$n<sizeof($allfiles);$n++)
{
$line = explode(",",$allfiles[$n]);
echo "<b>$line[0]</b>" . '<br><br>';

$result=mysql_query("select bwid
from booking_webassess
where compname='$line[0]'");
list ($DBbwid)=mysql_fetch_row($result);
echo "$DBbwid" . '<br><br>';

preg_match_all("@(\d{1,2}/\d{1,2}/\d{1,2} - (?!\d{1,2}/\d{1,2}/\d{1,2} - ).*?)@", $line[1], $matches);

print_r($matches);


}
}
else
echo 'No file uploaded';
?>

This is the output I get...

Array ( [0] => Array ( [0] => 22/06/04 - [1] => 13/1/4 - [2] => 23/2/04 - [3] => 16/02/03 - ) [1] => Array ( [0] => 22/06/04 - [1] => 13/1/4 - [2] => 23/2/04 - [3] => 16/02/03 - ) )

This is the first line of the CSV file...

"Company Name,22/06/04 - spoke to name who loved the idea and wants a proposal… 13/1/4 - Spoke to name and seeing her on 23/01/04…. 23/2/04 - Still in the picture and will call back in a few days. 16/02/03 - they've got approval"

Any ideas?

Much appreciated....

:o)

[Salsa]

Woldie, see how this works for you. This should put the dates in a subarray of $matches[1] and the text in a subarray of $matches[2]. (Entire matches are in $matches[0].) Because I used the date format as the end assertion for the pattern, the last match in $allfiles[0] wouldn't be made, so I used the rather ugly workaround of appending a dummy date to $line[1]. Also, I traded out your explode of $allfiles[0] for split() because explode would mess everything else up if there were additional commas anywhere in the string.

I hope this helps.

<?php
$allfiles[0] = "Company Name,22/06/04 - spoke to name who loved the idea, and wants a proposal… 13/1/4 - Spoke to name - seeing her on 23/01/04. 23/2/04 - Still in the picture and will call back in a few days. 16/02/03 - they've got approval";

$allfiles[0] = $allfiles[0]."0/0/0 - ";
echo "\$allfiles[0] = '$allfiles[0]'<br>&nbsp;<br>\n\n";

//$line = explode(",",$allfiles[0]);
$line = split(',',$allfiles[0],2);
echo "Company name = '$line[0]'<br>&nbsp;<br>\n\n";
echo "\$line[1] = '$line[1]'<br>&nbsp;<br>\n\n";

//preg_match_all("@(\d{1,2}/\d{1,2}/\d{1,2} - (?!\d{1,2}/\d{1,2}/\d{1,2} - ).*?)@", $line[1], $matches);
preg_match_all("@(\d{1,2}/\d{1,2}/\d{1,4}) - (.*?(?=\d{1,2}/\d{1,2}/\d{1,4} - ))@", $line[1], $matches);

echo "<pre>";
print_r($matches);
echo "<pre>";
?>

[woldie]

Thanks for responding back Salsa, I've run that bit of code and it works well, so thanks again for the taking the time to work on this. Just need to incorporate the database queries and that should be it.

I will be implementing this, this will save me alot of time, because the csv file is quite big so I suppose it is worth creating a script like yours and running it, and place all the data in the database....

I know who to call for my regex queries!

Woldie :o)