PHP - Upload CSV data in custom Wordpress database

Question

my_custom_table:

id              int(11)
product_type    varchar(210)
product_serial  varchar(210)
created_at      datetime


//Upload CSV File
if (isset($_POST['submit'])) {

        if (is_uploaded_file($_FILES['upload_csv']['tmp_name'])) {

                echo "<h1>" . "File ". $_FILES['upload_csv']['name'] ." uploaded successfully." . "</h1>";
        }


        //Import uploaded file to Database
        $handle = fopen($_FILES['upload_csv']['tmp_name'], "r");

        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            $wpdb->insert("my_custom_table", array(
                    "product_type" => $data[0],
                    "product_serial" => $data[1],
                    "created_at" => current_time('mysql', 1)
                 ));
        }

        fclose($handle);

        print "Import done";
}

Print $data output with delimiter ,:

Array
(
    [0] => Product Name;product_serial
)

Array
(
    [0] => iPhone 6;iphone-002
)

Array
(
    [0] => iPhone 6;iphone-003
)

Print $data output with delimiter ;:

Array
(
    [0] => Product Name
    [1] => product_serial
)

Array
(
    [0] => iPhone 6
    [1] => iphone-002
)

Array
(
    [0] => iPhone 6
    [1] => iphone-003
)

Using above, Product Name and product_serial also gets inserted in DB which should be prevented. Also delimiter , does not output correct array while ; does.

How can I prevent CSV column names insertion and insert correct value in Database?

P.S: Using OpenOffice for CSV data insertion. Could formatting be an issue with delimiter ?

Dan Belden · Accepted Answer · 2015-06-26 07:48:20Z

2

The general rule of thumb is the first line of CSV is the column names, therefore a quick skip counter will remove the first row for you:

$counter = 0;
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

    // Skip the first row as is likely column names
    if ($counter === 0) {
        $counter++;
        continue;
    }

    // Insert the row into the database
    $wpdb->insert("my_custom_table", array(
        "product_type" => $data[0],
        "product_serial" => $data[1],
        "created_at" => current_time('mysql', 1)
    ));
}

The other problem is CSV files can have varying column and row delimiters (AKA sometimes columns are sperated by comma, and other times semi colons ...etc) making parsing CSV quite difficult. I this example it seems your column delimiter is semi-colon so modifying your function parameters may fix it for you:

while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {

If you really must support multiple types of delimiter, the following code snippet may help you:

$csvFilePath = $_FILES['upload_csv']['tmp_name'];
$delimiter = $this->detectDelimiter($csvFilePath);

public function detectDelimiter($csvFile)
{
    $delimiters = array(
        ';' => 0,
        ',' => 0,
        "\t" => 0,
        "|" => 0
    );

    $handle = fopen($csvFile, "r");
    $firstLine = fgets($handle);
    fclose($handle); 
    foreach ($delimiters as $delimiter => &$count) {
        $count = count(str_getcsv($firstLine, $delimiter));
    }

    return array_search(max($delimiters), $delimiters);
}

Detect snippet taken from: HERE

Hope this helps.

edited Jun 26, 2015 at 7:48

answered Jun 26, 2015 at 5:35

Dan Belden

1,2171 gold badge10 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Slimshadddyyy Over a year ago

Skip counter removes the first row i.e. column names but did not get your snippet about delimiters. How could I implement the same in my code snippet ?

Dan Belden Over a year ago

Revised my answer, it seems detecting the delimiter is quite tricky and many posts suggest asking the user to provide it with the CSV file. I have revised my answer which will fix your CSV problem, but if you CSVs have varying delimiters (AKA comma and/or semi colon) then you are in for a rocky ride.... Goodluck.

Collectives™ on Stack Overflow

PHP - Upload CSV data in custom Wordpress database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related