[eluser]the_unforgiven[/eluser]
Hi all,
I'm looking for a library or some help on creating a upload form that allows uploads of a CSV file that then gets saved to the database overwriting the data that's already there. This will only be done once a year or twice a year by the client via the CMS i have built for them, so it needs to work like I explained.
Is there anyone who can point me in the right direction to create this or is there something already out there?
Thanks for reading and I'll look forward to your replies.
[eluser]davidMC1982[/eluser]
Here's some code I've used to do something similar. I'm using Datamaper ORM throughout my app but in this case, it's only specifically used to clear my table data. After that I used the standard CI DB class.
I'm also getting my CSV file from an external data source. You'd have to replace this to open the csv file.
My code builds one giant INSERT statement. In my case, if the INSERT fails, it has no bearing on the rest of my app. I wouldn't attempt to do this on a table that is critical to the functioning of your site. Instead, I would do the INSERT on a temp table first.
Anyway, this should give you an idea of the steps you might need to take.
Code: function refresh_rates() {
//Clear our table ready for fresh data
$scraped_product = new Scraped_product();
$scraped_product->truncate();
//Get the data from some web resource in csv format
$data = file_get_contents("https://somedatasource.com");
//Explode the csv into an array of lines
$lines = explode("\n", $data);
//Get rid of the first line
$header = str_getcsv(array_shift($lines));
//SQL for bulk insert
$sql = "INSERT INTO scraped_products VALUES ";
//For each of the models in the csv
foreach ($lines as $row) {
//Turn the csv into an array
$row = str_getcsv($row);
if ($row[0]) {
//Give the array the named keys extracted from the header
$product = array_combine($header, $row);
//Set the price for our product
$product['price'] = filter_var($product['price'], FILTER_SANITIZE_NUMBER_FLOAT, array('flags' => FILTER_FLAG_ALLOW_FRACTION));
$product['comp_price'] = filter_var($product['comp_price'], FILTER_SANITIZE_NUMBER_FLOAT, array('flags' => FILTER_FLAG_ALLOW_FRACTION));
//Add our product/price into the sql insert statement
$sql = $sql . "('','" . $product['manufacturer'] . "','" . $product['model'] . "','" . $product['price'] . "','" . $product['link'] . "','" . $product['comp_price'] . "'),";
}
}
//Trim the trailing , and add the ;
$sql = rtrim($sql, ',');
$sql = $sql . ";";
//Load codeigniters db class
$this->load->database();
//Execute the query, if it succeeds, go back to the rates page, otherwise give an error message
if ($this->db->query($sql)) {
// redirect('admin/rates/list_rates');
} else {
echo "Refreshing Rates Failed!";
}
}
[eluser]the_unforgiven[/eluser]
Cool, that looks straight forward i'll have a go at that see what happens...Thanks dude
[eluser]the_unforgiven[/eluser]
Done what you said and i'm getting the following error:
Code: Error Number: 1064
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 '' at line 1
INSERT INTO wc_program_temp VALUES ;
[eluser]TheFuzzy0ne[/eluser]
You're not providing any data to insert into the database. Either ensure that the data is there, or add a conditional to make sure the query isn't executed if there's no data to insert.
[eluser]the_unforgiven[/eluser]
ok well i have the following and getting the file upload or so i thought.
Code: function add()
{
$csv_data = $this->upload->data();
$csv = $csv_data['file_name'];
//Get the data from some web resource in csv format
$data = $csv;
//Explode the csv into an array of lines
$lines = explode("\n", $data);
//Get rid of the first line
$header = str_getcsv(array_shift($lines));
//SQL for bulk insert
$sql = "INSERT INTO wc_program_temp VALUES ";
//For each of the models in the csv
foreach ($lines as $row) {
//Turn the csv into an array
$row = str_getcsv($row);
if ($row[0]) {
//Give the array the named keys extracted from the header
$product = array_combine($header, $row);
//Set the price for our product
$product['price'] = filter_var($product['price'], FILTER_SANITIZE_NUMBER_FLOAT, array('flags' => FILTER_FLAG_ALLOW_FRACTION));
$product['comp_price'] = filter_var($product['comp_price'], FILTER_SANITIZE_NUMBER_FLOAT, array('flags' => FILTER_FLAG_ALLOW_FRACTION));
//Add our product/price into the sql insert statement
$sql = $sql . "('','" . $product['manufacturer'] . "','" . $product['model'] . "','" . $product['price'] . "','" . $product['link'] . "','" . $product['comp_price'] . "'),";
}
}
//Trim the trailing , and add the ;
$sql = explode($sql, ',');
$sql = $sql . ";";
//Load codeigniters db class
$this->load->database();
//Execute the query, if it succeeds, go back to the rates page, otherwise give an error message
if ($this->db->query($sql)) {
// redirect('admin/rates/list_rates');
echo 'Succeeded';
} else {
echo "Refreshing Rates Failed!";
}
}
What am i doing wrong?
[eluser]TheFuzzy0ne[/eluser]
All you're doing is explode()ing the file name. Since that's only a single line, you're array only has a single line, which you're removing with array_shift. You need to open the file in order to obtain any data from it. The array that $this->upload->data() returns is simply information about the uploaded file, not the file itself.
Debug, debug, debug! Check your code each step of the way. Output arrays and strings to make sure they contain the data you think they do.
[eluser]the_unforgiven[/eluser]
Great cheers i'll do just that, will i need to store the uploaded file temporary somewhere?
[eluser]TheFuzzy0ne[/eluser]
The file is already stored in a temporary directory, and remains there until you move it. You can access it like so:
Code: $file_contents = file_get_contents($_FILES['field_name']['tmp_name']);
Obviously, field_name should be replaced with the name of your file upload field.
[eluser]the_unforgiven[/eluser]
Got ya! thanks dude
|
