CODEWITHSUNDEEP
phpmysqlwordpresscsvfile-upload
Slimshadddyyy
Slimshadddyyy

Reputation: 4073

PHP - Upload CSV data in custom Wordpress database

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 ?

Upvotes: 2

Views: 4111

Answers (1)

Dan Belden
Dan Belden

Reputation: 1217

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.

Upvotes: 2

Related Questions