Import a csv file with variable filename

Question

My supplier sends every day an update of the order status with a variable static filename to import in my sql db. My cronjob has the following code:

<?php
require('cron_config.php');

// Open and parse the sales.csv file
$fh = fopen("../files/out/orderstatus.csv", "r");

while ($line = fgetcsv($fh, 1000, ","))
{
    $order_id = $line[0];
    $order_status_id = $line[1];
    $date_modified = $line[2];

    // Insert the data into the sales table
    $query = "UPDATE oc_order o SET order_status_id='$order_status_id',
        date_modified='$date_modified' WHERE order_id = '$order_id'";

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

fclose($fh);
$mysqli->close();
?>

In my code you see the static filename "orderstatus.csv". In future I will receive a dynamic filename (example: orderstatus_"order_id".csv. How is it possible to recognize this file with my code? The named order_id is already a part of the sql_query.

Thanks for the help

what's wrong with the method you're using now? If you're getting the same file each day, then overwrite the old one? — Funk Forty Niner, Jan 30 '17 at 16:01
Too bad I am not php guy. You will need to parse the order id out of `orderstatus_order_id.csv`, perhaps using `regex`. then store it into variable and later using this variable in concatenations / parameterizations of SQL: `WHERE order_id = '" . $order_id . "'"` — T.S., Jan 30 '17 at 16:02
`WHERE order_id = '" . $order_id ."'"` - dots, not `+`'s @T.S. in php — Funk Forty Niner, Jan 30 '17 at 16:03
If you don't have any other files in that directory and the id will be created through date - you could take the latest csv file through the timestamp - example [here](http://stackoverflow.com/a/11597482/6140684) . After that i think it's simple to just take the id of the .csv file. — Antonios Tsimourtos, Jan 30 '17 at 16:04
Well I would start by deleting the file once you have processed it — RiggsFolly, Jan 30 '17 at 16:07
@T.S. You're welcome. However, both `'$var'` and `'". $var . "'` do the same thing in mysql. — Funk Forty Niner, Jan 30 '17 at 16:08
GUYS! There is nothing wrong (apart from not being parameterised) with the query in this question. Its about how to get files from a directory — RiggsFolly, Jan 30 '17 at 16:10
I guess you need to look at all the files in the directory and find the one that matches $order_id, is that right? — ADyson, Jan 30 '17 at 16:11
`$order_file = $from_somewhere; $fh = fopen("../files/out/$order_file.csv", "r");` — Funk Forty Niner, Jan 30 '17 at 16:12
Has the content of the file changed as well as the filename??? — RiggsFolly, Jan 30 '17 at 16:14
use the scandir() or glob() to loop through the contents of the folder check the files there against a database list of "already imported files" any that aren't in there use file name as import for your fopen (or fgetcsv probably better) once file is processed write filename in to database so its ignored in future. or to save you a database table just unlink() the file after every parse so the folder is always empty apart from when there's files to process. — Dave, Jan 30 '17 at 16:17
The supplier will change the actual working process for security reasons. My idea is to recognize the file, to import the new status and to delete the file. — 27eleven, Jan 30 '17 at 16:20
my comment above will cover you then both scandir and glob pages have example code for you to rework its fairly trivial — Dave, Jan 30 '17 at 16:23

score 1 · Answer 1 · answered Jan 31 '17 at 07:17

Well done guys. With glob() and unlink() it did the job. Here the code:

<?php
require('cron_config.php');

foreach (glob("../files/out/orderstatus_*.csv") as $filename) {
echo "$filename \n";
}

// Open and parse the sales.csv file
$fh = fopen("$filename", "r");

while ($line = fgetcsv($fh, 1000, ","))
{
    $order_id = $line[0];
    $order_status_id = $line[1];
    $date_modified = $line[2];

    // Insert the data into the sales table
    $query = "UPDATE oc_order o SET order_status_id='$order_status_id',
        date_modified='$date_modified' WHERE order_id = '$order_id'";

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

fclose($fh);
unlink("$filename");
$mysqli->close();
?>

score 0 · Answer 2 · answered Jan 30 '17 at 16:13

0

Why not search for it:

$result = array_filter(array_map(
       function($a){ 
          return strpos($a,'orderstatus_')===0?$a:null;          
       }
       ,scandir('../files/out/')));

The result array will only hold all files from the folder that are begin with orderstatus_.

answered Jan 30 '17 at 16:13

JustOnUnderMillions

3,741
9
12

sure, but that would need the folder to only contain the one file, wouldn't it? – Funk Forty Niner Jan 30 '17 at 16:14
@Fred -ii- Think the OP show not the full logic behind this. Are the files deleted after import/update? Are more CSV at once in the folder? Should then all file imported? And so on ... – JustOnUnderMillions Jan 30 '17 at 16:16
Lordie, it's hard to say and IMHO, thought the question was both unclear/too broad as to where/how that (dynamic) file will be coming from. – Funk Forty Niner Jan 30 '17 at 16:16
So i was point to `How is it possible to recognize this file with my code?` only ;-) – JustOnUnderMillions Jan 30 '17 at 16:18
*lol* dunno. I thought [something like this...](http://stackoverflow.com/questions/41940132/import-a-csv-file-with-variable-filename#comment71061216_41940132) would've done the job if that's what the question is about. – Funk Forty Niner Jan 30 '17 at 16:19
And i just did an answer, because of the `callable` in my answer, thought that was to big for an comment :) – JustOnUnderMillions Jan 30 '17 at 16:20
The supplier will change the actual working process for security reasons. My idea is to recognize the file, to import the new status and to delete the file. The answer from JustOnUnderMillions could work, if i delete the file after updating the status. – 27eleven Jan 30 '17 at 16:20
@27eleven You could have a dedicated folder for this to contain only the one file, check if a file already exists before the new file is to be uploaded and `unlink()` it; that's one way. You could place it outside the root for more security. The cron's time will also depend on this as to how many times it's run though. – Funk Forty Niner Jan 30 '17 at 16:27
Thanks Fred -ii-. The folder /out/ is a dedicated folder. I try first the answer above with deleting the importet file and the i try to optimize it. – 27eleven Jan 30 '17 at 16:54

Import a csv file with variable filename

2 Answers2