Replace character in CSV-string that is LIKE the separator, but not THE separator

Question

I have a little problem with working on strings in PHP.

Imagine a CSV file with these fields:

ItemID;ItemText;ItemVAT;ItemEAN;

Clearly, the separator in this string is ";". The problem is, that values from "ItemText" might contain the ";", too.

I need to filter these strings and replace the containing ";" with something else, like "#" or "$", doesn't matter. I tried counting the ";" with substr_count to determine if I have an additional ";" (in comparision to the count of a "normal" value string). But I don't know how to find the actual ";" in the value. To make things harder, there can be more than one ";" in the value string.

Anyone got some ideas how to filter these unnecessary ";"?

Answer 1

You can do something like this if you have this fields always.

$line = "34;erwffw;wefweef;fwe3242;23342;53453;";
$toArray = explode(";", $line);
$counntSemi = count($toArray);

$newLine[0] = $toArray[0]; // ItemID
$newLine[1] = ''; // ItemText
$newLine[2] = $toArray[$counntSemi-3]; // ItemVAT
$newLine[3] = $toArray[$counntSemi-2]; // ItemEAN

// ItemText filter
for($i=1; $i < ($counntSemi-3); $i++) {
    $newLine[1] .= $toArray[$i]."@"; 
}

print_r($newLine);
// here you can implode this newLine with the semicolons

Answer 2

When a field contains the separator, then it should be quoted, like

valueA;"valueB;with;embedded;separators";valueC

If this is the case you can e.g. merge each array item with the following while it contains an odd nr of '"' characters.

If the creator of the file (the encoder of the data) did not follow that rule then there's no generic way to decode the file correctly because encoding the values "A" and "B;C" would produce the same csv file as encoding the values "A;B" and "C". In that case you can only blame the creator or try to identify ItemVAT based on a pattern you it has