We help IT Professionals succeed at work.

Uploading a CSV Data Import via PHP & MySql

Ridgejp
Ridgejp asked
on
208 Views
Last Modified: 2017-03-09
Hi,

I'm using the code below to upload a CSV Data import directly into my website. I've written this to alleviate the need to process the import via phpMyAdmin using the csv import - so that others can upload the files without the need to back-room access.

However, I'm finding that my import routine has a sensitivity to 'special characters' in php so for example if one of the records contained a delivery from "St John's Ambulance" the ' in the wording "John's" would cause the whole row to be rejected. This is also true for trialling spaces and the following characters " ' \ ` / ( ) +.

How, do I dial down the sensitivity in this regard for field that maybe contain such characters or should it be this way - I know that in phpMyAdmin you can always check the box that states "Do Not Abort on Insert Error" which over-rides these errors.

Can you do the same in the import file?

$file = $_FILES['file']['tmp_name'];
        
        $handle = fopen($file,"r");
        // fgets = misses the header row.
        fgets($handle);
        
        while(($fileop = fgetcsv($handle,10000,",")) !== false)
        
            {
            
            $dateOfInvoice          = $fileop[0];
            $dateOfPurchaseOrder    = $fileop[1];
            $poNumber          		= $fileop[2];
            $supplier               = $fileop[3];
            $supplierNumber         = $fileop[4];
            $dateOfBookingIn        = $fileop[5];
            $SKU             		= $fileop[6];
            $supplierPCode          = $fileop[7];
            $supplierDesc           = $fileop[8];
            $unitsRequired          = $fileop[9];
            $cartonsSize            = $fileop[10];
            $cartonsRequired        = $fileop[11];
            $qtyReceived            = $fileop[12];
            $receiptedBy            = $fileop[13];
            $dateSysUpdated         = $fileop[14];
            $comments             	= $fileop[15];
                
            $query1 = "INSERT into goodsIn 
                                        
                            (    dateOfInvoice
                                ,dateOfPurchaseOrder
                                ,poNumber
                                ,supplier
                                ,supplierNumber
                                ,dateOfBookingIn
                                ,SKU
                                ,supplierPCode
                                ,supplierDesc
                                ,unitsRequired
                                ,cartonsSize
                                ,cartonsRequired
                                ,qtyReceived
                                ,receiptedBy
                                ,dateSysUpdated
                                ,comments
                                
                            ) 
                                            
                                VALUES
                                            
                            (  '$dateOfInvoice'
                              ,'$dateOfPurchaseOrder'
                              ,'$poNumber'
                              ,'$supplier'
                              ,'$supplierNumber'
                              ,'$dateOfBookingIn'
                              ,'$SKU'
                              ,'$supplierPCode'
                              ,'$supplierDesc'
                              ,'$unitsRequired'
                              ,'$cartonsSize'
                              ,'$cartonsRequired'
                              ,'$qtyReceived'
                              ,'$receiptedBy'
                              ,'$dateSysUpdated'
                              ,'$comments'

                            )";

            $result = $mysqli->query($query1);
                        
            }

            unset($fileop);

Open in new window


J
Comment
Watch Question

CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Most Valuable Expert 2011
Author of the Year 2014
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION