CODEWITHSUNDEEP
phpmysqlsqlcsvfgetcsv
FarhadD
FarhadD

Reputation: 495

Import CSV File With Field Including Commas In Quotes?

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.

Upvotes: 0

Views: 2924

Answers (2)

Asaph
Asaph

Reputation: 162771

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.

Upvotes: 3

ficuscr
ficuscr

Reputation: 7054

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.

Upvotes: 0

Related Questions