Importing Large CSV files with PHP Part 2: Validating CSV file structure

Sep 29, 2015 Anton Domratchev

In the first part of this series we learned how to quickly we can Importing Large CSV files with PHP Part 1: Import using one query. Using that query means that we will need to perform user input validation before we can safely use it in a production application.

Validating large CSV files could be problematic due to a number of constraints. In this post we will look at a technique I used to perform some checks on the user supplied file before attempting to process it.

First, lets look at what sort of validation we can perform on a user supplied CSV file. We can:

Check to make sure that the file is in fact a CSV file Check that the file is properly formatted such as columns are in the correct order, and that required data columns are present Check that required data is present Check that the data is formatted to the specified column format, such as email is in fact an email and etc.

There may be more specific validation that can be performed such as in multi file uploads when CSV files need to be ordered in a specific way, but for the purposes of this post we are focusing on the above mentioned validations.

Now that we established what we want to validate we need to look at what validation we can perform when the user first uploads the file.

We can easily verify that the file has an extension of CSV which I will explain later. I would avoid attempting to validate file's mime types. From my personal experience I've had trouble validating mime types using Laravel's mimes: rule when file has been saved on multiple platforms and various file processors. We can also verify that the file contains the required columns, as well as verify that the columns which are required by our application are present, we will look at that validation shortly.

So far the validation we are doing will work without a problem, however the other two rules will be a problem. Remember we are working with very large CSV files exceeding 15MB in size. Attempting to validate the entire file one line at the time will not work as we learned in the previous post. We can approach this with a bit of assumptions. We can sample a few lines of the file and make an assumption that the rest of the file is in the similar format. This isn't a perfect solution but it will ween about 90% of the files which don't have the proper formatting or required fields. A better solution would be to import the file and its contents in its original state into a temporary table and process it line by line at a later time. Stay tuned for my upcoming post in this series which will describe how to do that.

Validator class

Most of the validation we will be doing can be done using Laravel's built in rules. You can however define your own custom validation rules if you find that you may need to use these rules in several places in your application. In the projects I've been working on, I tend to extract validation rules out of the model and apply them to the specific domain or a command I am executing. This way my validation rules are more flexible and apply to the action that is being performed rather than the domain as a whole. For this example our CSV importer is going to be used to import new users in the the system. We need to validate that the file's extension is in fact CSV all of our required columns are present. We will also validate that each record has an email address, first and last name.

<?php namespace Acme\Importing ; use Illuminate\Validation\Factory as ValidationFactory ; use Exception ; class CsvImportValidator { /** * Validator object * @var \Illuminate\Validation\Factory */ private $validator ; /** * Validation rules for CsvImport * */ private $rules = [ 'csv_extension' => 'in:csv' , 'email_column' => 'required' , 'first_name_column' => 'required' , 'last_name_column' => 'required' , 'fist_name' => 'required' , 'last_name' => 'required' , 'email' => 'email|required' ]; /** * Constructor for CsvImportValidator * @param \Illuminate\Validation\Factory $validator */ public function __construct ( ValidationFactory $validator ) { $this -> validator = $validator ; } /** * Validation method * @param \Symfony\Component\HttpFoundation\File\UploadedFile * @return \Illuminate\Validation\Validator $validator */ public function validate ( $csv_file_path ) { // Line endings fix ini_set ( 'auto_detect_line_endings' , true ); $csv_extendion = $csv_file_path -> getClientOriginalExtension (); // Open file into memory if ( $opened_file = fopen ( $csv_file_path , 'r' ) === false ) { throw new Exception ( 'File cannot be opened for reading' ); } // Get first row of the file as the header $header = fgetcsv ( $opened_file , 0 , ',' ); // Find email column $email_column = $this -> getColumnNameByValue ( $header , 'email' ); // Find first_name column $first_name_column = $this -> getColumnNameByValue ( $header , 'first_name' ); // Find last_name column $last_name_column = $this -> getColumnNameByValue ( $header , 'last_name' ); // Get second row of the file as the first data row $data_row = fgetcsv ( $opened_file , 0 , ',' ); // Combine header and first row data $first_row = array_combine ( $header , $data_row ); // Find email in the email column $first_row_email = array_key_exists ( 'email' , $first_row ) ? $first_row [ 'email' ] : '' ; // Find first name in first_name column $first_row_first_name = array_key_exists ( 'first_name' , $first_row ) ? $first_row [ 'first_name' ] : '' ; // Find last name in last_name column $first_row_last_name = array_key_exists ( 'last_name' , $first_row ) ? $first_row [ 'last_name' ] : '' ; // Close file and free up memory fclose ( $opened_file ); // Build our validation array $validation_array = [ 'csv_extension' => $csv_extendion , 'email_column' => $email_column , 'first_name_column' => $first_name_column , 'last_name_column' => $last_name_column , 'email' => $fist_row_email , 'first_name' => $first_row_first_name , 'last_name' => $first_row_last_name ]; // Return validator object return $this -> validator -> make ( $validation_array , $this -> rules ) } /** * Attempts to find a value in array or returns empty string * @param array $array hay stack we are searching * @param string $key * */ private function getColumnNameByValue ( $array , $value ) { return in_array ( $value , $array ) ? $value : '' ; } } ?>

Our validator constructs with validation factory so we can quickly make validation of our data. We will be able to resolve this dependency through the IOC container in Laravel. This class only has one public method validate() it accepts a UploadedFile object which we get from using Input::file() , this lets us easily get the extension of the uploaded file to validate it. Within our validate method we use the ini_set() method to adjust PHP settings to auto detect line endings. This solves issues with files saved on multiple platforms. As you can see I am grabbing the first line of the file to verify that it is the header. Next I am grabbing the second line of the file and combine the header with first line of data. This way I can assert that each value is in the appropriate column. Next I build my validation array and run make with my validator factory. In this scenario I am only using the first line, however, this can be modified to use a larger sample of data. At this point I am only concerned with making the sure that the format of the CSV is correct.

Now we just need a small modification to our controller:

<?php namespace Acme\Controllers ; use Input ; use Acme\Importing\CsvImporter ; use App ; class CsvImportController extends BaseController { /** * [POST] Form which will submit the file */ public function store () { // Check if form submitted a file if ( Input :: hasFile ( $csv_import )) { $csv_import = Input :: file ( 'csv_import' ); // Validate that the file is present if ( $csv_import -> isValid ()) { // Create validator object $validator = App :: make ( 'Acme\Importing\CsvImportValidator' ); // Run validation with input file $validator = $validator -> validate ( $csv_import ); if ( $validator -> fails ()) { return Redirect :: back () -> withErrors ( $validator ); } // Lets construct our importer $csv_importer = new CsvFileImporter (); // Import our csv file if ( $csv_importer -> import ( $csv_import ) { // Provide success message to the user $message = 'Your file has been successfully imported!' } else { $message = 'Your file did not import' ; } } else { // Provide a meaningful error message to the user // Perform any logging if necessary $message = 'You must provide a CSV file for import.' ; } return Redirect :: back () -> with ( 'message' , $message ); } } } ?>

Now that we have validation for our file importer we feel much safer about using our single query import. However, this isn't going to solve problems of bad data in CSV. For that we can do a much granular validation with background imports, see Importing Large CSV files Part 3: Processing and Validating data in background.