Problem with .csv file and missed comma(s)
Hi all I have a problem with CSV opening through PHP code. My PHP code is:
<?php
header("Content-Type: text/html; charset=windows-1251");
echo "<html>
<head>
<title></title>
</head>
<body>
";
$file = "import.csv";
if(file_exists开发者_开发技巧($file)) {
if (($fopen = fopen($file, "r")) !== FALSE) {
echo "<table>\n";
while (($data = fgetcsv($fopen, 1024, ",")) !== FALSE) {
$max = count($data);
$num++;
echo "<tr>\n<td>".$num."</td>\n";
for ($i=0;$i<$max;$i++) {
echo "<td>".$data[$i]."</td>\n";
}
echo "</tr>\n";
}
echo "</table>";
fclose($fopen);
}
}
else {
echo "File doesn't exists!";
}
echo "
</body>
</html>";
?>
The problem isn't in PHP code, the problem is in .csv file. PHP code must work even if there is missing comma, when it show the information the normal way.
The .csv file:
First name,Family,Sex,Date of birth,City,Phone number
One, Ofamily, Male, 1975, LA,13-25-16
Two, Tfamily, Male, 1955, LV, 555-14345
Three, Thfamily, Male, 1958, NY, 15689
Four, Ffamily, Female, 1974, SF, 5897912
Five, Fifamily, Male, 1991, LA, 123456789
Six, Sfamily, Male, 1967, 9876542
Seven, Sefamily, Female,, SF,
<?php
header("Content-Type: text/html; charset=windows-1251");
echo "<html>
<head>
<title></title>
</head>
<body>
";
$file = "import.csv";
if(file_exists($file)) {
if (($fopen = fopen($file, "r")) !== FALSE) {
echo "<table>\n";
while (($data = fgetcsv($fopen, 1024, ",")) !== FALSE) {
$num++;
echo "<tr>\n<td>".$num."</td>\n";
foreach($data as $k => $v) {
switch ($k) {
case 0 : // first name
case 1 : // family
case 2 : // sex
case 4 : // city
if (is_numeric($v)) {
array_splice($data,$k,0,'');
}
break;
case 3 : // date of birth
case 5 : // phone number
if (!is_numeric($v)) {
array_splice($data,$k,0,'');
}
break;
}
}
foreach($data as $v) {
echo "<td>".$v."</td>\n";
}
echo "</tr>\n";
}
echo "</table>";
fclose($fopen);
}
}
else {
echo "File doesn't exists!";
}
echo "
</body>
</html>";
?>
If you don't have control of the incoming CSV, you're not going to be able to use fgetcsv. How is it supposed to know if there's a missing ,?
Unfortunately, you're going to have to write your own function to handle this. I would start by reading each line into an array. Then looping through the line and explodeing them by a comma. Then, you'll have to check each value and try to determine if somethings missing from the resulting array.
Let's look at the problematic line in your example.
Six, Sfamily, Male, 1967, 9876542
Here's what we know about it:
- It contains one less value then all the rest, so we should run some data consistency checks on it.
- We know the first and second values are going to be strings that do not equal "Male" or "Female".
- We know the third value should always equal either "Male" or "Female".
- We know the fourth value is going to be a year and should always be a numerical value.
- We know the fifth value is missing and it should be a city code and will always be two letters in length.
Based on that information, you should be able to write some checks to determine if one of those values doesn't equal what you'd expect, and then fix it.
The problem is most likely to do with lines like:
Six, Sfamily, Male, 1967, 9876542
Where there is no city information. In this case, you'll never get "9876542" to show up in the phone column unless you apply some logic to determine that this isn't the city, and to skip to the next column. What you should do though, so that you have 6 columns in each row, is that instead of resetting $max every time, you should just set it once after reading the header. Then display that number of columns from each row you read.
加载中,请稍侯......
精彩评论