How to parse csv file into mysql DB with PHP with conditions

Question

how do you parse a csv file into a mysql database with php, From bits I have read from php documents I am not sure how or if its possible to add conditions to the lines that you are reading into the database so that only values that meet the condition are passed into the database.

for instance I currently have a csv file that looks like this

0001D,5879
0001E,0521
0001F,4587
0001G,2220
0001H,2482
0001I,9087
0001J,2255
0001K,2247

the codes before the comma are supposed to only be hex values, what I would like to do is exclude any line (so hex value and its code) that dose not represent a hex code (0-9)-(A-F)... any idea on how the could be accomplished?

Answer 1

This code uses the fgetcsv function to parse out one line at a time from a csv file, putting each delimited field into an array, and returning that array. With your example csv, the array returned will only contain two fields.

Next, after checking to make sure the return value from the fgetcsv isn't false or null, the code checks to see if the first field of the array contains any non-hex characters in the range [G-Z] using preg_match. If it doesn't, then it will put the two fields of the array (representing a line from you csv file) into a new array, which you can operate on later.

<?php
$fileName = "test.csv";
$hexLines = array();

if (($file = fopen($fileName, "r")) !== false)
{
    while (($line = fgetcsv($file, 0, ",")) !== false)
    {
        if (!empty($line))
        {
            // IF first field only has hex chars
            if (preg_match('/^[0-9A-F]+$/', $line[0]) === 1) 
            {
                $hexLines[] = array($line[0], $line[1]);
            }
        }
    }
    fclose($file);
}
print_r($hexLines);

By the way, the above code will work in both PHP versions 5.2.x and 5.3.x. If you were 100% sure that you were going to be using 5.3, I would've written code using SplFileObject instead, since it's object-oriented, and maybe a little easier to use than the above functions.

Answer 2

You're far better off doing this without PHP if possible. Use mysqlimport to import the file, and then use:

-- assuming you use "ID" as your first column like the example 
-- on the mysqlimport page
DELETE FROM <tablename> WHERE CONV(CONV(ID, 16, 10), 10, 16) != 
                              TRIM(LEADING '0' FROM ID);

So, what's going on?

1. First you've imported everything into a table.
2. Then, you've looked through all of the columns which are supposed to be hex. If they cannot be converted to Hex and back (something which is only true of valid Hex numbers, everything else is truncated) and match the trimmed version of themselves (leading 0's need to be accounted for), they are not valid.

If you must use PHP, I would personally simply continually use MySQL's INSERT and I'd still call the above DELETE. I'll bet money that it's faster.