Using CSV to import - it adds apostophies

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Using CSV to import - it adds apostophies

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Using CSV to import - it adds apostophies

Post by VladSun »

http://www.php.net/manual/en/function.str-getcsv.php

Don't use addslashes(), use mysql_real_escape() instead.
There are 10 types of people in this world, those who understand binary and those who don't
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Using CSV to import - it adds apostophies

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Using CSV to import - it adds apostophies

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Using CSV to import - it adds apostophies

Post 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."'
                )
            ");
        }
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: Using CSV to import - it adds apostophies

Post 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
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Using CSV to import - it adds apostophies

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: Using CSV to import - it adds apostophies

Post 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
Post Reply