[SOLVED] Parse and replace?

[iceblox]

Hi guys i have a script which i use to upload my csv spreadseets to mysql.

 

How can i replace things within the spreadsheet.

 

Lets say one of the cells was "Cookie" and i wanted to replace it to 1?

 

this is my current code, I tried a if and else code with no joy

 

<?php


$file = '../Book1.csv'; //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 test (Model) VALUES ('$cols[0]')")or
die(mysql_error());
}

echo "Upload Completed";

?>

[craygo]

you can use str_replace

 

<?php


$file = '../Book1.csv'; //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));
$newline = $cols[0] == "Cookie" ? "1" : $cols[0];
mysql_query("INSERT INTO test (Model) VALUES ('$newline')")or
die(mysql_error());
}

echo "Upload Completed";

 

Ray

 

?>

[iceblox]

Thanks Ray,

 

Worked a treat..

 

One last question, the script is for a product data feed. Want i want to do is any thing rows that havent been parsed i want to leave out of the feed or put a different table so i can fix? i guess thats why i was trying to do an if statement?

 

Is this easily acheivable?

[craygo]

Not sure I get what you want to do. If you give me some sample data I can help out.

[iceblox]

Ok, so lets say that there are loads of replace codes like the cookie one.. What I want to achieve is any that do no get replaced i dont want them to be uploaded and i want to be informed some way that those were not replaced so i can fix it, if this makes sense?

 

<?php


$file = '../Book1.csv'; //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));
$newline = $cols[0] == "Cookie" ? "1" : $cols[0];
mysql_query("INSERT INTO test (Model) VALUES ('$newline')")or
die(mysql_error());
}

echo "Upload Completed";

[craygo]

well you can store the values you want to replace in an array. You can use array_search to do the replacing. The thing with array search is that it searches the value and returns the key.

 

So

<?php
$array = array('1' => 'Cookie', '2' => 'Pie'); // Add all the values you want to replace here

$file = '../Book1.csv'; //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));
  if(in_array($col[0], $array)){
  $newline = array_search($cols[0], $array);
  mysql_query("INSERT INTO test (Model) VALUES ('$newline')")or die(mysql_error());  // Insert into test table  if value is found
  } else {
  mysql_query("INSERT INTO test2 (Model) VALUES ('$col[0]')") or die(mmysql_error());  // inset into test2 table if not found
  }
}

echo "Upload Completed";
?>

 

 

Ray

[iceblox]

Nice Ray,

 

Just playing with it now.

 

$array = array('N95' => '1', 'U900' => '1'); // Add all the values you want to replace here

$file = '/cron/Book1.csv'; //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));
  if(in_array($col[0], $array)){
  $newline = array_search($cols[0], $array);
  mysql_query("INSERT INTO test (Model) VALUES ('$newline')")or die(mysql_error());  // Insert into test table  if value is found
  } else {
  mysql_query("INSERT INTO test2 (Model) VALUES ('$col[0]')") or die(mysql_error());  // inset into test2 table if not found
  }
}
?>

 

If i wanted to search many rows would i just add extra col? 

 

 

if(in_array($col[0],$col[1], $array)){

Cheers

[iceblox]

hmm it doesnt seem to be grabbing the data from the sheet.

[craygo]

No you would have to loop through the $col array to search. so you would have a loop inside a loop.

<?php
foreach ($lines as $line) {
$cols = explode('"', trim($line));
  foreach($cols as $value){
    if(in_array($value, $array)){
    $newline = array_search($value, $array);
    mysql_query("INSERT INTO test (Model) VALUES ('$newline')")or die(mysql_error());  // Insert into test table  if value is found
    } else {
    mysql_query("INSERT INTO test2 (Model) VALUES ('$value')") or die(mysql_error());  // inset into test2 table if not found
    }
  }
}
?>

[roopurt18]

For future reference, MySQL provides a LOAD DATA which can load a CSV file into a table.  If the file has values you'd like to edit you could load them into a temp table first, modify the values, and then INSERT ... SELECT from the temp table into the final destination.

[iceblox]

Hi Thanks Ray,

 

I think im having some issues..

 

It doesnt seem to be picking up the replace as all of them are going into test2 reather than the other one.

 

Also i cant get it go into seprate columns within the table for example there are all going in like this

 

 
      N95,N95,N95   
      U900,U900,U900   
      N95 8GB,N95 8GB,N95 8GB   

 

All in one column...

 

I thought this would be the way round it but its not..

 

<?php

$array = array('1' => 'N95'); // Add all the values you want to replace here

$file = /cron/Book1.csv'; //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));
  foreach($cols as $value){
    if(in_array($value, $array)){
    $newline = array_search($value, $array);
    mysql_query("INSERT INTO test3 (model, name) VALUES ('$newline')")or die(mysql_error());  // Insert into test table  if value is found
    } else {
    mysql_query("INSERT INTO test2 (Model, name) VALUES ('$value')") or die(mysql_error());  // inset into test2 table if not found
    }
  }
}



?>

[craygo]

if you can send me the csv file And let me know which values you want to store, i can help better.

 

Ray

[iceblox]

Hi Ray,

 

I havent sent you the full feed as it much to big. This is similar to feed but the real one will be in a lot more depth.

 

What i want to do replace is the Gift Column, tariff and Model Column. I know ill have to add a lot of statements in for each different model which is fine.

 

Any row that doesnt have something deleted should be put in the error table.

 

If this makes sense?

 

[attachment deleted by admin]

[iceblox]

Any help from anyone would be appreciated.. Its driving me crazy trying to solve it. Im willing to pay to get it resolved! 

[craygo]

Did you get my PM ??

[DarkWater]

I solved it for him yesterday over MSN and he paid me already.  This topic should have been marked SOLVED.  I'll tell him.