Create Multiple CSV files into a downloadable Zip

[jarvis]

Hi All,

 

I'm hoping someone can assist. Basically, I'm using Wordpress to output a bunch of review data. I have over 30,000 reviews and each review contains a significant amount of data.

 

If I try to export all the data in one hit, it simply times out. So I thought I could create a loop in order to break down the data into more manageable chunks, then output multiple CSV files.

 

The problem is, the zip file is empty. I know I have to use a zip as my understanding is that you can' create multiple files from looping. My code is below, although I've simplified it as you don't need to know all the columns:

#get the total count of reviews
$reviews = array (
	'post_type' 	=> 'reviews',
	'post_status'	=> array('publish','draft')
);	
$wp_gr_query = new WP_Query( $reviews );

$total = $wp_gr_query->found_posts;

$rows_per_file = '5000';

$no_of_csv_files = ceil($total/$rows_per_file);

#create an array, this will be used to pass each CSV file and create our ZIP
$files = array();

#iterate through the reviews
for ($x = 0; $x <= $no_of_csv_files; $x++) {
	
	#determine the start point on each iteration
	$offset = ($rows_per_file * $x);	
	
	#create our file name
	$files[] = $x.'_report_'.date("d-m-Y_H-i",time());
	
		$args = array(
			'posts_per_page'	=> $rows_per_file,
			'offset'			=> $offset,
			'post_type'			=> 'reviews',
			'orderby'			=> 'date',
			'order'				=> 'DESC',
			'fields'			=> 'ids'
				
		);
		$data = array();
		$loop = new WP_Query($args);
		if( $loop->have_posts() ):
	
			#create column headers
			$data[] = array(
				"Review ID"
			); 			
	
			while( $loop->have_posts() ): $loop->the_post();
				$id = get_the_ID();	

				$data[] = array(
					"Review ID" => apply_filters( 'the_title', $id)
				);
	
			endwhile;
		endif;
	
		#create the download
		$output = fopen("php://output", "w");
		foreach ($data as $row) {
			fputcsv($output, $row); // here you can change delimiter/enclosure
		}
		fclose($output);	
	
		#reset 
		wp_reset_postdata();	


} #end for loop

$zipname = 'file.zip';
$zip = new ZipArchive;
$zip->open($zipname, ZipArchive::CREATE);
foreach ($files as $file) {
  $zip->addFile($file);
}
$zip->close();
header('Content-Type: application/zip');
header('Content-disposition: attachment; filename='.$zipname);
header('Content-Length: ' . filesize($zipname));
readfile($zipname);	

I'm hoping I've merely missed the glaringly obvious but can't see for looking!

 

Any help is very much appreciated

 

Thanks in advanced

[ginerjm]

Maybe you want to post this on a WP specific forum?

[jarvis]

Thanks @ginerjm

 

I thought that but figured it's more about the PHP loop and CSV/Zip creation. The Wordpress side is fine

[requinix]

You're creating output with that CSV stuff. It'll conflict with the zip file data. Remove it.

[jarvis]

Hi @requinix

 

Thanks for the reply.

 

I've removed the following:
 

		#create the download
		$output = fopen("php://output", "w");
		foreach ($data as $row) {
			fputcsv($output, $row); // here you can change delimiter/enclosure
		}
		fclose($output);	

However, it still creates an empty zip file

 

Surely, you need this to create the CSV files during each iteration?

[jarvis]

Probably incorrect but would i need to do something more like:

		$output[$x] = fopen("php://output", "w");
		foreach ($data as $row) {
			fputcsv($output[$x], $row); // here you can change delimiter/enclosure
		}
		fclose($output[$x]);	

Purely because each file needs to be created? Problem is, even with this in the code, it still doesn't create the files in a downloadable zip.

 

Thanks

[ginerjm]

Are you two latest posts all the code you need to create a zip? Why aren't you creating a file that you can then later zip up?

[jarvis]

Thanks @ginerjm

 

Because it times out when I try to create one file. So I believe by splitting this into smaller files, then zipping them, it 'should' sort the issue

[requinix]

After running this script, are you able to download file.zip directly?

[jarvis]

Hi @requinix

 

The zip does download but it's empty, So i'm assuming it's not creating the CSV files

[requinix]

...Wait...

 

It's not creating files? Well no, of course not. The only file it's creating is file.zip. Are you saying

$files[] = $x.'_report_'.date("d-m-Y_H-i",time());

those files are supposed to be created by the script? Then the script needs to do that - the code I mentioned earlier won't because it was writing to php://output instead of the right file.

 

So fix that so the CSVs are being generated, then you should also add code after the zip is created that will delete those files (unless you want to keep them after the fact?), then send the zip file.

[jarvis]

Just as an update, I've now got this working.

 

I approached it slightly differently in the end.

 

It creates the CSV files I need and writes them to a temp directory

I then recursive loop through and all those files into a zip

The zip was saved to the server and worked, yet the download version didn't. It seems I needed to add ob_clean() and flush()

The zip download now works

All files are removed from the server to keep it clean.

 

Here's my code:

$reviews = array (
	'post_type' 	=> 'reviews',
	'post_status'	=> array('publish','draft')
);	
$wp_reviews_query = new WP_Query( $reviews );

$total_reviews = $wp_reviews_query->found_posts;

$rows_per_file = '5000';

$no_of_csv_files = ceil($total_reviews/$rows_per_file);

for ($x = 0; $x <= $no_of_csv_files; $x++) {

	$offset = ($rows_per_file * $x);	
	
		$args = array(
			'posts_per_page'	=> $rows_per_file,
			'offset'			=> $offset,
			'post_status'		=> array('publish','draft'),
			'post_type'			=> 'reviews', 
			'orderby'			=> 'date',
			'order'				=> 'DESC',
			'fields'			=> 'ids'
				
		);
		$data = array();
		$loop = new WP_Query($args);
		if( $loop->have_posts() ):
	
			#create column headers
			$data[] = array(
				"Review ID"
			); 			
	
			while( $loop->have_posts() ): $loop->the_post();
				$id = get_the_ID();	
				$data[] = array(
					"Review ID" => apply_filters( 'the_title', $id)
				);
	
			endwhile;
		endif;
	
		$filename = $x.'_report_'.date("d-m-Y_H-i",time()).'.csv';
		
		#create the download
		$output[$x] = fopen(__DIR__ . '/csv/'.$filename , "w");
		foreach ($data as $row) {
			fputcsv($output[$x], $row); // here you can change delimiter/enclosure
		}
		fclose($output[$x]);	
	
		#reset 
		wp_reset_postdata();	


} #end for loop

# Get real path for our folder
$rootPath = realpath('csv');

# Initialize archive object
$zipname = '_report_'.date("d-m-Y_H-i",time()).'.zip';
$zip = new ZipArchive();
$zip->open($zipname, ZipArchive::CREATE | ZipArchive::OVERWRITE);

# Initialize empty "delete list"
$filesToDelete = array();

# Create recursive directory iterator
/** @var SplFileInfo[] $files */
$files = new RecursiveIteratorIterator(
    new RecursiveDirectoryIterator($rootPath),
    RecursiveIteratorIterator::LEAVES_ONLY
);

foreach ($files as $name => $file){
	
    # Skip directories (they would be added automatically)
    if (!$file->isDir()){
		
        # Get real and relative path for current file
        $filePath = $file->getRealPath();
        $relativePath = substr($filePath, strlen($rootPath) + 1);

        # Add current file to archive
        $zip->addFile($filePath, $relativePath);
		
     	# Add current file to "delete list"
		$filesToDelete[] = $filePath;        		

    }
}

# Zip archive will be created only after closing object
$zip->close();

header('Content-Type: application/zip');
header('Content-disposition: attachment; filename='.$zipname);
header('Content-Length: ' . filesize($zipname));
#add to correct issue	
ob_clean();
flush();
readfile("$zipname");

# Delete all files from "delete list"
foreach ($filesToDelete as $file){
	unlink($file);
}
# Now remove the ZIP file
unlink($zipname);

I must credit Dador on Stackoverflow as this certainly helped with a different approach

 

I hope that helps someone else should the need arise

 

Thank you also to those who took the time to read and comment on this forum, as ever, it's always appreciated