Menu
Community /Pin to ProfileBookmark
@NogDogJun 20.2013 — #So what would the expected response be for:
?@NogDogJun 21.2013 — #
Ahh...but how did you know "#" was the delimiter and not "."? ("#" is the correct answer in this case if you want valid CSV format based on the quoting, but what if you remove all the quotes?)
So what I'm ultimately pushing toward here in order to nail down the actual requirement, is how do you determine which is the correct separator character in any given line of text, especially if that text contains two or more candidate special characters?
Once we have a precise requirement, the code may then become self-evident. For instance, if the requirement is simply to break the text into separate fields using any of ".,;|#" as separators, you might use preg_split() with a simple character class as the separator. If the result must always be 3 fields and using only one separator chosen from a set of possible separators, you might have to loop through a set of possible separators (perhaps using foreach() on an array of separator characters) until you find one that gives you 3 fields (or returns an error if you exhaust the possible separators with no result with 3 fields).@NogDogJun 21.2013 — #This is looking promising, but you should add some more test cases, including negative tests to see if it really works. ?
@NogDogJun 21.2013 — #I'm starting to think the only viable solution -- outside of requiring the data source use one specific delimiter, preferably with proper CSV formatting -- is to create a "white list" of allowed delimiters and test against each one until you get the correct number of fields.
@NogDogJun 23.2013 — #@NogDogJun 23.2013 — #You want tabs, too? You said to skip white-space, which normally includes tabs. ? If you tell me you also want underscores as delimiters, I may have to send somebody to rough you up a bit. :p@NogDogJun 23.2013 — #Just to be pedantic, CSV files use commas as delimiters (that's what the "C" stands for), with pretty specific rules for things like using quotes.
I stole parts of what I used to identify the email address from here:http://www.ex-parrot.com/pdw/Mail-RFC822-Address.html
The only parts you should have to mess around with are this at the beginning:
[B]s[/B] is any white-space (space, tab, newline, carriage return, vertical tab)
[B]S[/B] is any character that is NOT a white-space character
To include tabs as delimiters, I believe you could change those parenthesized bits to include the tab character via [B]t[/B]:
@NogDogJun 23.2013 — #
However, this probably won't work as desired if you have an odd case where the separator is some character like ";" but you also have a tab character before/after it.
Getting delimiter from a line
Hi
Can someone please tell me the best possible way to find the delimiter from a given line (not including the spaces)?
For our convenience we can assume use the email address to split if necessary. So the very next char (except space) after the email can be our delimiter. But there may be cases where the email address is at last and no delimiter are there.
Some examples are:
Ex 1:
[CODE]
jon, doe, [email protected], 996655
Ex 2:
[CODE]
[email protected]; doe; ;996655
Ex 3:
[CODE]
jon# doe# 996655# [email protected]
Ex 4:
[CODE]
jon doe 96655
Ex 5:
[CODE]
jon doe 996655 [email protected]
Ex 6:
[CODE]
jon;doe;[email protected];996655;
In ex 4 and 5 above, it should return as no delimiter found.
Any help is appreciated.
Thanks
Sign in
to post a comment20 Comments(s) ↴
0
@NogDogJun 20.2013 — #So what would the expected response be for:<i>
</i>foo.bar#example.com#"I like using ""#"" or ""."" as a CSV delimiter."
?
0
@NogDogJun 21.2013 — #Hi Thanks for ur reply.
When I saved that line of yours and opened in my spreadsheet program specifying the delimiter as #, it spitted up the line very nicely. Chk screenshot.
http://i.imgur.com/K8uEBlC.png
How are they doing it?[/QUOTE]
Ahh...but how did you know "#" was the delimiter and not "."? ("#" is the correct answer in this case if you want valid CSV format based on the quoting, but what if you remove all the quotes?)
So what I'm ultimately pushing toward here in order to nail down the actual requirement, is how do you determine which is the correct separator character in any given line of text, especially if that text contains two or more candidate special characters?
Once we have a precise requirement, the code may then become self-evident. For instance, if the requirement is simply to break the text into separate fields using any of ".,;|#" as separators, you might use preg_split() with a simple character class as the separator. If the result must always be 3 fields and using only one separator chosen from a set of possible separators, you might have to loop through a set of possible separators (perhaps using foreach() on an array of separator characters) until you find one that gives you 3 fields (or returns an error if you exhaust the possible separators with no result with 3 fields).
0
@NogDogJun 21.2013 — #This is looking promising, but you should add some more test cases, including negative tests to see if it really works. ?[code=php]
<?php
function getDelimiter($str, $debug = false)
{
$email = 'w[^@s]*@[^@s]+w';
$regex = '/(^|S)s*'.$email.'s*($|S)/';
if(preg_match($regex, $str, $matches)) {
if($debug) {
echo "<pre>".var_export($matches,1)."</pre>n";
}
foreach($matches as $match) {
if(strlen($match) == 1) {
return $match;
}
}
return false;
}
else {
if($debug) {
echo "<p>Nope</p>n";
}
return false;
}
}
$data = array(
'[email protected]# foo# bar',
'foo #[email protected] #bar',
'foo # bar # [email protected]'
);
foreach($data as $test) {
$delimiter = getDelimiter($test, true);
echo "<p>Delimiter for '$test' is '$delimiter'.</p>n";
}
[/code]0
@NogDogJun 21.2013 — #I'm starting to think the only viable solution -- outside of requiring the data source use one specific delimiter, preferably with proper CSV formatting -- is to create a "white list" of allowed delimiters and test against each one until you get the correct number of fields.[code=php]
$delims = array(',', ';', '|', '#');
$delimiter = null;
foreach($delims as $delim) {
$parts = explode($delim, $text);
if(count($parts == 3) { // or whatever correct value is
$delimiter = $delim;
break;
}
}
[/code]0
@NogDogJun 23.2013 — #[code=php]
<?php
/**
* Try to figure out what the delimiter is by looking for email address
* @return string (false if not found)
* @param string $str string to search
* @param bool $debug whether to output debug info (default to false)
*/
function getDelimiter($str, $debug=false)
{
static $regex = '/(^|S)s*[^()<>@,;:\".[] 00- 31][^()<>@,;:\"[] 00- 31]*@[^()<>@,;:\"[] 00- 31]*[^()<>@,;:\".[] 00- 31]+s*(S|$)/';
if(preg_match($regex, $str, $matches)) {
if($debug) {
echo "<pre>Degbug: found email:n".var_export($matches,1)."</pre>n";
}
for($ix=1; $ix<=2; $ix++) {
if(!empty($matches[$ix]) and preg_match('/W/', $matches[$ix])) {
return $matches[$ix];
}
}
}
return false;
}
// test it:
$test = array(
'000020;ACTIVE;AU VIEUX CAMPEUR;;48 RUE DES ECOLES; ;75005;PARIS;president JACQUES YVES DE RORTHAYS;AF14;M.;Jean-Jacques DENUAU;06 08 16 65 62;[email protected];F004;Melle;Magali SUREDA;06 86 48 23 30;[email protected];Melle;Anne-Charlotte MICHELET;[email protected]',
'[email protected];CHARIOT Tichel',
'jon# doe# 996655# [email protected]',
'jon doe 996655 [email protected]'
);
foreach($test as $str) {
echo "<pre>$str:n";
$result = getDelimiter($str, true);
echo var_export($result,1)."</pre>n";
}
[/code]0
@NogDogJun 23.2013 — #You want tabs, too? You said to skip white-space, which normally includes tabs. ? If you tell me you also want underscores as delimiters, I may have to send somebody to rough you up a bit. :p0
@NogDogJun 23.2013 — #Just to be pedantic, CSV files use commas as delimiters (that's what the "C" stands for), with pretty specific rules for things like using quotes.I stole parts of what I used to identify the email address from here:
The only parts you should have to mess around with are this at the beginning:
<i>
</i>(^|S)s*
s*(S|$)[B]s[/B] is any white-space (space, tab, newline, carriage return, vertical tab)
[B]S[/B] is any character that is NOT a white-space character
To include tabs as delimiters, I believe you could change those parenthesized bits to include the tab character via [B]t[/B]:
<i>
</i>(^|[St])s*
<i>
</i>([St]|$)
0
@NogDogJun 23.2013 — #[code=php]
static $regex = '/(^|[St]) *?[^()<>@,;:\".[] 00- 31][^()<>@,;:\"[] 00- 31]*@[^()<>@,;:\"[] 00- 31]*[^()<>@,;:\".[] 00- 31]+ *([St]|$)/';
[/code]However, this probably won't work as desired if you have an odd case where the separator is some character like ";" but you also have a tab character before/after it.