Page 1 of 1

Using CSV to import - it adds apostophies

Posted: Wed May 15, 2013 10:24 am
by simonmlewis

Code: Select all

//loop through the csv file and insert into database
    do {
        if ($data[0]) {
            mysql_query("INSERT INTO subscribed_upload (email) VALUES
                (
                    '".addslashes($data[1])."'
                )
            ");
        }
This is part of a tutorial I found to insert CSV rows.
The issue is, CSV is separated by ".
So how do I remove these " before they go in, to save having to strip them out when we do exports?

Re: Using CSV to import - it adds apostophies

Posted: Wed May 15, 2013 10:37 am
by VladSun
http://www.php.net/manual/en/function.str-getcsv.php

Don't use addslashes(), use mysql_real_escape() instead.

Re: Using CSV to import - it adds apostophies

Posted: Wed May 15, 2013 10:41 am
by simonmlewis
Literally like this?

Code: Select all

            mysql_query("INSERT INTO subscribed_upload (email) VALUES
                (
                    '".mysql_real_escape($data[1])."'
                )
Because that says:
Fatal error: Call to undefined function mysql_real_escape() in C:\xampp\phpMyAdmin\site\includes\import.inc on line 19
Which is that exact line.

Re: Using CSV to import - it adds apostophies

Posted: Wed May 15, 2013 10:57 am
by simonmlewis

Code: Select all

if ($data[0]) {
            $newdata=mysql_real_escape_string($data[1]);
            mysql_query("INSERT INTO subscribed_upload (email) VALUES
                (
                    '".$newdata."'
                )
            ");
        }
This inserts, but still keeps the " at the end of each string.
I want to remove them.

Re: Using CSV to import - it adds apostophies

Posted: Wed May 15, 2013 10:59 am
by simonmlewis
Bingo:

Code: Select all

if ($data[0]) {
        
            $newdata = trim($data[1], '"'); // Outputs email with no quotes

            mysql_query("INSERT INTO subscribed_upload (email) VALUES
                (
                    '".$newdata."'
                )
            ");
        }

Re: Using CSV to import - it adds apostophies

Posted: Wed May 15, 2013 3:27 pm
by mecha_godzilla
Hi,

Just to add some information in case you're not already aware of it - double quotes (") are used when the text they encapsulate might contain one or more instances of the value delimiter (usually a comma, but not always). Some applications automatically output every value in double quotes when it isn't strictly necessary though, but applications like Excel usually work correctly because it knows what data types it's using for each cell.

Rather than using trim(), you might be better to use str_replace() instead:

Code: Select all

$text = 'Here is "some text" with "double quotes" in it';
echo str_replace('"', '', $text);
This will work on the whole string, not just the beginning and end of it. If you need to remove other non-standard characters that you don't want saved in your database records, you can also use preg_replace() to convert multiple dashes (---) to a single one (-), convert ampersands (&) to "and", etc.

Don't forget that PHP also has functions available to directly parse/output CSV files, and you can also change the default delimiter and boundary characters settings if necessary.

HTH,

Mecha Godzilla

Re: Using CSV to import - it adds apostophies

Posted: Thu May 16, 2013 9:00 am
by simonmlewis
I do have a new issue - this is my upload CSV code.

Code: Select all

if ($_FILES[csv][size] > 0) {
    //get the csv file
    $file = $_FILES[csv][tmp_name];
    $handle = fopen($file,"r");
    //loop through the csv file and insert into database
    do {
        if ($data[0]) {
            $newdata = trim($data[1], '"'); // Outputs email with no quotes
            mysql_query("INSERT INTO subscribed_upload (email) VALUES
                (
                    '".$newdata."'
                )
            ")or die(mysql_error());
            $count = $count + 1;
        }
    } while ($data = fgetcsv($handle,1000,",","'"));
}
The issue is, it's not uploading all the field. I've getting parts of fields. "net" rather than the whole "asdfasfd@asdfsadf.net" for example.

Each row in CSV is like this:
"fred@fred.com"

What I have I done wrong here?

Re: Using CSV to import - it adds apostophies

Posted: Thu May 16, 2013 1:55 pm
by mecha_godzilla
Hi,

What do the contents of $data[1] look like before the trim() operation, and what does $newdata look like?

Also, you should really be using mysql_real_escape_string() when constructing your query string:

Code: Select all

$newdata = trim($data[1], '"'); // Outputs email with no quotes
$query = "INSERT INTO subscribed_upload (email) VALUES ('" . mysql_real_escape_string($newdata) . "')";
mysql_query($query) or die(mysql_error());
HTH,

M_G