Allow commas in fields in a csv file while putting it in database in php

Question

This may seem like a duplicate but I have read just about everything on Stack Over Flow and some of it is too old to work and other stuff I just can't get to work or don't understand.

I need to take a .csv file and upload it into my php script and have it place the data in a database. I have everything working well except that some of the fields may include a comma in the data. My program is seeing that as a new field. I need to to keep the data in that field together.

Here is what I have now...

$cols = 0;
$count = '5000';
if ($_FILES[csv][size] > 0) {
   $dup = 0;
   $skipfirst = 0;
   $filename = $_FILES[csv][name];
   $file = $_FILES[csv][tmp_name];
   $noOfLines = count(file($file));
   $ext = substr($filename, -3);
   if ($ext != "csv") {
      header('Location: https://example.com?msg=notcsv');
      exit;
   }
   if ($noOfLines > $count) {
      header('Location: https://example.com?msg=toolarge');
      exit;
   }
   $handle = fopen($file, "r");
   // loop through the csv file and insert into database
   do {
      if ($data[0]) {
         $col3 = $data['1']; // username - handle
         $col4 = $data['4']; // name
         $col5 = $data['22']; // saddress1
         $col6 = $data['24']; // saddress2
         $col7 = $data['28']; // scity
         $col8 = $data['30']; // sstate
         $col9 = $data['33']; // szip
         $col10 = $data['35']; // scountry
         $col1 = $data['37']; // order number
         $col2 = $data['38']; // order date
         $col11 = $data['52']; // item1
         $col12 = $data['55']; // qty1
         $col13 = $data['63']; // item2
         $col14 = $data['66']; // qty2
         $col15 = $data['74']; // item3
         $col16 = $data['77']; // qty3
         $col17 = $data['85']; // item4
         $col18 = $data['88']; // qty4
         $col19 = $data['96']; // item5
         $col20 = $data['99']; // qty5
         $col21 = $data['107']; // item6
         $col22 = $data['110']; // qty6
         $col23 = $data['118']; // item7
         $col24 = $data['121']; // qty7
         $col25 = $data['129']; // item8
         $col26 = $data['132']; // qty8
         $col27 = $data['140']; // item9
         $col28 = $data['143']; // qty9
         $col29 = $data['151']; // item10
         $col30 = $data['154']; // qty10
         if ($skipfirst != '0') { // made to skip first row because of headers
            $result = mysqli_query($connect, "SELECT id FROM information WHERE order_number='$col1' LIMIT 1");
            $myrow = mysqli_fetch_array($result);
            if ($myrow[id] == '') {
               mysqli_query($connect, "INSERT INTO information (order_number,orderdate,username,name,saddress1,saddress2,scity,sstate,szip,scountry,item1,qty1,item2,qty2,item3,qty3,item4,qty4,item5,qty5,item6,qty6,item7,qty7,item8,qty8,item9,qty9,item10,qty10,complete) VALUES ('$col1','$col2','$col3','$col4','$col5','$col6','$col7','$col8','$col9','$col10','$col11','$col12','$col13','$col14','$col15','$col16','$col17','$col18','$col19','$col20','$col21','$col22','$col23','$col24','$col25','$col26','$col27','$col28','$col29','$col30','0')");
            }
            else {
               $dup++;
            }
         } // end if skipfirst = 0
         $skipfirst++;
      }
   }
   while ($data = fgetcsv($handle, 5000, ",", "'"));
   //
   mysqli_close($connect);
   $count = $count - $noOfLines;
   $msg = "success";
}

Sample data might be...

"12234","56 My St, My Town","NC","434455","Basket Item,Well"

I put the quotes to represent each field. Of course the commas could be in any or all fields at anytime. Hope that makes sense.

Thank you for your help in this.

where is the csv file from? There simply should not be any (unescaped) commas in there... — Jeff, Oct 28 '17 at 09:48
It's coming from a program that I have no control over to change that or I would just change it before the file download. — Randy Thomas, Oct 28 '17 at 09:49
can the commas occur only in the second and in the last item? If so, then you could do a workaroud. — Jeff, Oct 28 '17 at 10:02
related: https://stackoverflow.com/questions/769621/dealing-with-commas-in-a-csv-file?rq=1 — Jeff, Oct 28 '17 at 10:03
If there's no consistent pattern to where commas might show up, and you have no influence on the creation of the CSV file, I say you're out of luck. Garbage in, garbage out. — DanMan, Oct 28 '17 at 10:03

score 0 · Answer 1 · edited Oct 28 '17 at 10:08

0

In your loading of the data, you're putting the enclosure character as being a single quote, you should be using double quotes:

while ($data = fgetcsv($handle, 5000, ",", "\""));

edited Oct 28 '17 at 10:08

DanMan

11,323
4
40
61

answered Oct 28 '17 at 10:05

Nigel Ren

56,122
11
43
55

Allow commas in fields in a csv file while putting it in database in php

1 Answers1