PHP / Symfony：写入CSV大文件

Question

Thank you for helping.

My Spec:

1. I have a very large CSV file
2. I need to process the file and output another large CSV file

My environment:

1. php7.0
2. Symfony 3.4 framework

My current solution:

I am using Symfony Serializer component to read my file, I then process my file and output the file. All in memory. Operation takes around 20 minutes to complete.

I run the script from a Symfony command. I have a service to read the file and a service to export the file.

An improved solution 1:

1. I can load the CSV file into a database table like explained here, with "LOAD DATA LOCAL INFILE" sql query. Very fast operation
2. I can then process my data and save it to another table
3. Then I would use "SELECT ... INTO OUTFILE 'file_name'" to output the file

Advantage: -SQL centered - No memory problem

Disavantage: -DB operations during processing might be expensive

An improved solution 2:

1. I can read the CSV file line by line
2. I process the line
3. I write the line to the output file

Adv: No memory issue Disav: Could take a LOT of time.

An improved solution 3:

1. I can load the CSV file into a database table like explained here, with "LOAD DATA LOCAL INFILE" sql query. Very fast operation
2. I can then process my data in chunks of 1000 and write them to a file

What solution would you use? Or do you have any better solution?

Answer 1

solution 2, without a database. Read from csv, process and output to csv just like someone mentioned in the comments, use fgetcsv() and fputcsv(). Going line by line should hardly consume any memory and it will remove the need for a database in between. The issue with these types of operations is the sequential nature of reading csv files as streams, eventually the speed of the process will boil down to the speed of the operation on the data in between the read and write operations. Using a database in between will just slow the entire process down and be somewhat wasteful.