Jump to content

Upload CSV


iceblox

Recommended Posts

Hi All,

 

My script has been working but since i have added more feilds in i keep getting an error message.. Can anyone see anything wrong that i am missing?

 

 

$file = 'http://www.urmob.co.uk/feed/single.php?partner=mpdu&selection=A&cashback=A&fields=Offer.ID-Offer.PhoneCost-Offer.TotalCost-Offer.MonthlyCost-Offer.FreeGift-Offer.OfferCashback-Offer.AutoCashback-Offer.OfferRental-Offer.OfferLength-Offer.Link-Model.ID-Model.Make-Model.Name-Model.ImageLarge-Model.ImageSmall-Model.Popularity-Tariff.Name-Tariff.ContractLength-Tariff.Rental-Tariff.NiceName-Tariff.FreeMins-Tariff.FreeMinsType-Tariff.FreeTxts-Tariff.Allowance-Tariff.CostLLP-Tariff.CostLLO-Tariff.CostSNP-Tariff.CostSNO-Tariff.CostONP-Tariff.CostONO-Tariff.CostVMP-Tariff.CostVMO-Tariff.CostSMS-Tariff.CostMMS-Network.Name-Network.Image-Merchant.Name-Merchant.Image-Deal.Popularity&track=URMOB-xmake-xmodelx-xtariffx'; //file name here
//we read the CSV file here
$lines = file($file);

//now we take each line and explode it then insert to DB
foreach ($lines as $line) {
$cols = explode('"', trim($line));
mysql_query("INSERT INTO deals (ID, PhoneCost, TotalCost, MonthlyCost, FreeGift, OfferCashback, AutoCashback, OfferRental, OfferLength, OfferLink, ModelID, MakeName, ModelName, ImageLarge, ImageSmall, ModelPop, TariffName, ContractLength, TariffRental, NiceName, FreeMins, FreeMinsType, FreeTxts, Allowance, LLP, LLO, SNP, SNO, ONP, ONO, VMP, VMO, SMS, MMS, NetworkName, NetworkImage, MerchantName, MerchantImage, DealPop) VALUES ('$cols[1]', '$cols[3]', '$cols[5]', '$cols[7]', '$cols[9]', '$cols[11]', '$cols[13]', '$cols[15]', '$cols[17]', '$cols[19]', '$cols[21]', '$cols[23]', '$cols[25]', '$cols[27]', '$cols[29]', '$cols[31]', '$cols[33]', '$cols[35]', '$cols[37]', '$cols[39]', '$cols[41]', '$cols[43]', '$cols[45]', '$cols[47]', '$cols[49]', '$cols[51]', '$cols[53]', '$cols[55]', '$cols[57]', '$cols[59]', '$cols[61]', '$cols[63]', '$cols[65]', '$cols[67]', '$cols[69]', $cols[71]', '$cols[73]', '$cols[75]', '$cols[77]')")or
die(mysql_error());
}

 

 

This is the error message I am getting

 

 

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '://www.urmob.co.uk/i/networks/Vodafone.gif', 'Mobile Shop', 'http://www.urmob.co' at line 1 

 

These relate to; NetworkImage, MerchantName, MerchantImage

 

Any help would be appreciated

Link to comment
Share on other sites

I don't understand why you are exploding by double quotes. CSV files are comma/semicolon/whatever seperated. The double quotes enclose the column data, so that the program reading the file knows that it should treat the seperator like text until another double quote has been reached. Furthermore, the double quotes can be escaped to tell the program to treat it like text, as well.

 

Long story short, you will need a slightly more sophisticated reader for your CSV file.

 

One last thing, it would be reasonable to assume that the column data should be escaped with addslashes() before it is inserted.

Link to comment
Share on other sites

You can try this. It should work, but I'm not entirely sure, as I took it from a data inserter I made a while ago.

 

<?php
// Establish database connection here

// Data
$file = 'http://www.urmob.co.uk/feed/single.php?partner=mpdu&selection=A&cashback=A&fields=Offer.ID-Offer.PhoneCost-Offer.TotalCost-Offer.MonthlyCost-Offer.FreeGift-Offer.OfferCashback-Offer.AutoCashback-Offer.OfferRental-Offer.OfferLength-Offer.Link-Model.ID-Model.Make-Model.Name-Model.ImageLarge-Model.ImageSmall-Model.Popularity-Tariff.Name-Tariff.ContractLength-Tariff.Rental-Tariff.NiceName-Tariff.FreeMins-Tariff.FreeMinsType-Tariff.FreeTxts-Tariff.Allowance-Tariff.CostLLP-Tariff.CostLLO-Tariff.CostSNP-Tariff.CostSNO-Tariff.CostONP-Tariff.CostONO-Tariff.CostVMP-Tariff.CostVMO-Tariff.CostSMS-Tariff.CostMMS-Network.Name-Network.Image-Merchant.Name-Merchant.Image-Deal.Popularity&track=URMOB-xmake-xmodelx-xtariffx';
$table = "deals"; // MySQL table to insert into
$fields = "ID, PhoneCost, TotalCost, MonthlyCost, FreeGift, OfferCashback, AutoCashback, OfferRental, OfferLength, OfferLink, ModelID, MakeName, ModelName, ImageLarge, ImageSmall, ModelPop, TariffName, ContractLength, TariffRental, NiceName, FreeMins, FreeMinsType, FreeTxts, Allowance, LLP, LLO, SNP, SNO, ONP, ONO, VMP, VMO, SMS, MMS, NetworkName, NetworkImage, MerchantName, MerchantImage, DealPop";
$s = file_get_contents($file);

$rs = "\n"; // Row seperator
$cs = ","; // Column seperator
$esc = "\\"; // Escape character
$quot = "\""; // Column data encloser

// Parse input
$row = 0;
$col = 0;
$data = array();
$escape = false;
$quote = false;
for($i = 0; $i < strlen($s); $i++) {
if(!empty($quot) && ($s{$i} == substr($quot, 0, 1)) && !$escape) {
	$quote = !$quote;
}
elseif(!empty($quot) && ($s{$i} == $esc) && !$escape) {
	$escape = true;
}
elseif(($s{$i} == $cs) && !$quote) {
	$col++;
	$escape = false;
}
elseif(($s{$i} == $rs) && !$quote) {
	$col = 0;
	$row++;
	$escape = false;
}
else {
	$data[$row][$col] .= $s{$i};
	$escape = false;
}
}

function prepare($v) {
$v = is_array($v) ? array_map("prepare", $v) : addslashes(trim($v));
return $v;
}

// Insert the data
foreach($data as $row) {
$row = prepare($row);
$query = "INSERT INTO ".$table." (".$fields.") VALUES ('".implode("', '", $row)."')";
echo "Executing \"".$query."\"... ";
if(mysql_query($query)) {
	echo "<span style=\"color: green;\">Success!</span><br>\r\n";
}
else {
	echo "<span style=\"color: red;\">Failed!</span> Reason: ".mysql_error()."<br>\r\n";
}
}

?>

Link to comment
Share on other sites

HI Boom.dk,

 

Thanks for your input, I have tried that script you put up and had no joy. I understand what your saying about my simple script. The most annoying thing is it works and still does as long as remove the 10 extra rows i added. Which would make me think i havent code it right but i have checked it so many times, I cant see any mistakes..

 

Im getting this error with the script you posted

 

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 35 bytes)

Link to comment
Share on other sites

<?php
$file = 'http://www.urmob.co.uk/feed/single.php?partner=mpdu&selection=A&cashback=A&fields=Offer.ID-Offer.PhoneCost-Offer.TotalCost-Offer.MonthlyCost-Offer.FreeGift-Offer.OfferCashback-Offer.AutoCashback-Offer.OfferRental-Offer.OfferLength-Offer.Link-Model.ID-Model.Make-Model.Name-Model.ImageLarge-Model.ImageSmall-Model.Popularity-Tariff.Name-Tariff.ContractLength-Tariff.Rental-Tariff.NiceName-Tariff.FreeMins-Tariff.FreeMinsType-Tariff.FreeTxts-Tariff.Allowance-Tariff.CostLLP-Tariff.CostLLO-Tariff.CostSNP-Tariff.CostSNO-Tariff.CostONP-Tariff.CostONO-Tariff.CostVMP-Tariff.CostVMO-Tariff.CostSMS-Tariff.CostMMS-Network.Name-Network.Image-Merchant.Name-Merchant.Image-Deal.Popularity&track=URMOB-xmake-xmodelx-xtariffx'; //file name here
//we read the CSV file here
$lines = file($file);

//now we take each line and explode it then insert to DB
foreach ($lines as $line) {
$line = addcslashes($line, "'"); //Add this to clean up quotes
$cols = explode('"', trim($line));
mysql_query("INSERT INTO deals (ID, PhoneCost, TotalCost, MonthlyCost, FreeGift, OfferCashback, AutoCashback, OfferRental, OfferLength, OfferLink, ModelID, MakeName, ModelName, ImageLarge, ImageSmall, ModelPop, TariffName, ContractLength, TariffRental, NiceName, FreeMins, FreeMinsType, FreeTxts, Allowance, LLP, LLO, SNP, SNO, ONP, ONO, VMP, VMO, SMS, MMS, NetworkName, NetworkImage, MerchantName, MerchantImage, DealPop) VALUES ('$cols[1]', '$cols[3]', '$cols[5]', '$cols[7]', '$cols[9]', '$cols[11]', '$cols[13]', '$cols[15]', '$cols[17]', '$cols[19]', '$cols[21]', '$cols[23]', '$cols[25]', '$cols[27]', '$cols[29]', '$cols[31]', '$cols[33]', '$cols[35]', '$cols[37]', '$cols[39]', '$cols[41]', '$cols[43]', '$cols[45]', '$cols[47]', '$cols[49]', '$cols[51]', '$cols[53]', '$cols[55]', '$cols[57]', '$cols[59]', '$cols[61]', '$cols[63]', '$cols[65]', '$cols[67]', '$cols[69]', $cols[71]', '$cols[73]', '$cols[75]', '$cols[77]')")or
die(mysql_error());
}

?>

 

 

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 35 bytes)

means out of memory maybe try increasing the memory ie

ini_set("memory_limit","32M");

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.