0

I am importing the following data from a CSV file, whose formatting I have absolutely no control over:

CampaignName,"""keyword""",441,11683,3.77%,2.99,112.82,"1,318.02",1.7,12,109.84

As you can see, one of the fields has a long number with a comma separator, though the value is enclosed in "..."

The data is imported into SQL using the following piece of code:

        while (($data = fgetcsv($handle)) !== FALSE) {
            $import="INSERT into ".$date."_keywords(Campaign,Keyword,Clicks,Impressions,CTR,CPC,CPM,Cost,Position,Conversions,Cost_per_conv) values('$data[0]','".mysql_real_escape_string($data[1])."','".mysql_real_escape_string($data[2])."','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]')";
            mysql_query($import) or die(mysql_error());
        }

Even though the long number is within quotes, fgetcsv doesn't seem to be able to handle it, and as a result saves the number as $1 and drops the rest of the quoted text.

It does pick up all the remaining fields correctly - so it doesn't seem to be just supposing that the , is a separator and the 318.02 is the next value.

Question is, how do I get the full number to get added to the database?

Edit: I have read the other thread about dealing with commas in CSV files and this file already has the data in double-quotes as that thread suggests, so that's not the issue.

Shawn
  • 47,241
  • 3
  • 26
  • 60
FarhadD
  • 485
  • 5
  • 14
  • 3
    fgetcsv() has a bunch of extra arguments to define separators, quotes, etc... use them. – Marc B Jun 01 '15 at 21:23
  • 1
    http://php.net/manual/en/function.fgetcsv.php – Jose Manuel Abarca Rodríguez Jun 01 '15 at 21:24
  • I tried using the enclosure argument as follows: while (($data = fgetcsv($handle, 1000, ",", "\"")) !== FALSE) { It made no difference – FarhadD Jun 01 '15 at 21:35
  • Also tried: while (($data = fgetcsv($handle, 0, ",", '"')) !== FALSE) { ...... Still doesn't make a difference... it's picking up the value as $1 and ignoring the ,318.02 – FarhadD Jun 01 '15 at 21:43
  • Have you ensured that the data type is correct in SQL? What do you have it set as? – BrianAtkins Jun 01 '15 at 22:10
  • The data type is set in SQL as float(9,2) - should be OK. And I did read the other thread, but all that one seems to suggest is to put the data in double quotes, which it already is! – FarhadD Jun 01 '15 at 22:28

2 Answers2

3

I don't think there is any problem with the CSV parsing. It's a type coercion issue at the MySQL level. You need to strip the comma out of 1,318.02 before inserting into your column of type float(9,2). You should insert 1318.02, not 1,318.02.

Instead of:

... $data[8] ...

do this:

... str_replace(",", "", $data[8]) ...

Incidentally, you may have some SQL injection vulnerabilities in the code you've posted, depending on the source of the CSV data. Using PDO prepared statements is advised.

Asaph
  • 159,146
  • 25
  • 197
  • 199
  • 1
    Brilliant! That worked. I had to do the following in the insert statement: floatval(str_replace(',', '' , $data[8])) – FarhadD Jun 01 '15 at 23:04
0

Not seeing the issue... What version of PHP are you on?

$crapData = 'CampaignName,"""keyword""",441,11683,3.77%,2.99,112.82,"1,318.02",1.7,12,109.84';

var_dump(str_getcsv($crapData));

Provides me the following output:

array(11) {
  [0]=>
  string(12) "CampaignName"
  [1]=>
  string(9) ""keyword""
  [2]=>
  string(3) "441"
  [3]=>
  string(5) "11683"
  [4]=>
  string(5) "3.77%"
  [5]=>
  string(4) "2.99"
  [6]=>
  string(6) "112.82"
  [7]=>
  string(8) "1,318.02"
  [8]=>
  string(3) "1.7"
  [9]=>
  string(2) "12"
  [10]=>
  string(6) "109.84"

}

Can see it live here.

ficuscr
  • 6,975
  • 2
  • 32
  • 52