Read multiple csv in zip file

Question

I have a zip file containing some several csv files like:

main_table.csv
sub_table.csv
foo_table.csv

I need to loop through each file in memory and execute query:

$path = 'C:/xampp/htdocs/xampp/content/';
$zipfile = $path.'zipname.zip';

if ($zip->open($zipfile) == TRUE) {
for ($i = 0; $i < $zip->numFiles; $i++) {
    $filename = $zip->getNameIndex($i);
    $table = substr($filename, 0, -4);//remove extension

$query = <<<eof
LOAD DATA INFILE '$path$filename' INTO TABLE {$table}
FIELDS OPTIONALLY ENCLOSED BY '^'
TERMINATED BY '|'
ESCAPED BY ''
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
eof;

$result = $conn->query($query) or die(mysqli_error($conn));

    }
}

It successfully opens zip but I get an error on

'$path$filename'

File 'C:\xampp\htdocs\xampp\content\main_table.csv' not found (Errcode: 2 "No such file or directory")

How do I pass file name from zip to LOAD DATA INFILE?

You can't just open/read a file inside a zipped folder. You need to extract the files from the zip to a location on disk, then you should be able to use `LOAD DATA INFILE ...`. — bassxzero, Sep 07 '18 at 16:11
You need to unzip it first, use the zipArchive class see this link https://stackoverflow.com/questions/8889025/unzip-a-file-with-php — Forbs, Sep 07 '18 at 16:18
You have the filename as a string, but of course no such _file_ lives there. A quick search brings me to: https://secure.php.net/manual/en/function.zip-open.php — , Sep 07 '18 at 16:19
_"You can't just open/read a file inside a zipped folder."_ Yes, you can, with [compression stream wrappers.](http://php.net/manual/en/wrappers.compression.php) (Not sure if this will work with `LOAD DATA INFILE` but you generally don't need to temporarily extract to disk when reading zip files with PHP.) — Alex Howansky, Sep 07 '18 at 17:05
@AlexHowansky I tried with this: http://php.net/manual/en/ziparchive.getstream.php but I get an erorr with my code: Resource id #4' not found (Errcode: 2 "No such file or directory") — Toniq, Sep 07 '18 at 17:20
@AlexHowansky PHP can, but MySQL cannot and since MySQL is the one that needs to read the data... "You can't just open/read a file inside a zipped folder" — bassxzero, Sep 07 '18 at 17:26
@bassxzero - what would the cleanest way to do so? I need to extractTo same location + loop files + delete extracted files when finished. — Toniq, Sep 07 '18 at 17:41
@bassxzero Dang just noticed OP is on Windows. On unix-ish systems, you can push data directly from a compression stream wrapper to a named pipe, then invoke `LOAD DATA INFILE` on the pipe. No need to mess with temp files, permissions, or cleanup. — Alex Howansky, Sep 07 '18 at 18:09

Read multiple csv in zip file

0 Answers0