Ridgejp
asked on
Uploading a CSV Data Import via PHP & MySql
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?
J
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);
J
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.