Export A Formidable Form Entry To CSV On Submit

Recently a client of ours asked if we could implement a solution to allow a Formidable Forms submission to send data as a CSV file to an external server when a user submits the form. The client wanted the form submissions to be imported into a 3rd party software that could only accept CSV files from a folder on an external server so we looked at building a solution.

This functionality can have several use cases and be used as our client required to export a unique CSV file per form entry or you could amend it to allow a single CSV file to be updated with a new row each time a form is submitted.

This tutorial will cover what you’ll need to do in order to store individual CSVs to your web server (Part 1), and then extend the function to export your CSVs to another server using FTP (Part 2).

Part 1: Create the CSV

Firstly, we created a form for users to fill out which will capture essential data we would need to store on the CSV. Our code below assumes the form has the following fields (but you can change these to meet your requirements):

1. First name
2. Last name
3. Email address
4. Organisation ID
5. Organisation Name

In our snippet below, we use the frm_after_create_entry hook which runs after the entry has been created in the database. This allows us to use the data once the form has been submitted.

What the code does

The function, create_csv, will first check to see which form has been submitted. In our case, if the form with an ID of 1234 has a new entry then the rest of our code will trigger.

if($form_id == 1234){ //replace 1234 with the id of the form
  ...
}

We’re storing all the data we need to export onto the CSV into PHP variables. This will make it much easier to pass our $data as you’ll see later on. You’ll need to add rows for each field you want to export giving it a unique variable name. We’re also using the PHP isset function which will handle the scenario of any of the fields being left blank and not containing a value.

$firstName = isset($_POST['item_meta'][202]) ? $_POST['item_meta'][202] : '';
$lastName = isset($_POST['item_meta'][203]) ? $_POST['item_meta'][203] : '';
$email = isset($_POST['item_meta'][204]) ? $_POST['item_meta'][204] : '';
$organizationId = isset($_POST['item_meta'][193]) ? $_POST['item_meta'][193] : '';
$organisationName = isset($_POST['item_meta'][194]) ? $_POST['item_meta'][194] : '';
$refUserId = isset($_POST['item_meta'][197]) ? $_POST['item_meta'][197] : '';
$createdAt = isset($_POST['item_meta'][199]) ? $_POST['item_meta'][199] : '';

If you’re reading this article then you’re most likely familiar with what components make up a valid CSV document. For those of you who aren’t so familiar, CSVs are made up of two main parts: Headers; and Data – separated by commas (or any other valid delimiter).

Simply put, headers are the name of the column whereas the data is the value you’d like to store in that column.

For our function, we need to come up with suitable headers (column names) for our CSV document and store our data in CSV format.

$header = "firstName,lastName,email,organizationId,organisationName,refUserId,createdAt\n";

$data = "$firstName,$lastName,$email,$organizationId,$organisationName,$refUserId,$createdAt\n";

The next stage is to save the file to our web-server and to do that we need to decide on what we want the file to contain. If we want to create a new file for each form entry (as our client did) then we’ll need to create a unique filename for each file. We do this in our code by adding the date & time stamp to each file name however if you want to have just 1 CSV file add a new row to it for each new form entry you can set the filename as something static.

We also need to set a location to save the file on our local server.

In our clients case we set this fucntion up using the Code Snippets plugin and as the code will be executed from the plugin folder we had to add in (__DIR__, 3) in the file path to look 3 directories above where the code runs to then get the correct to the folder they wanted to store the files locally.

The file path and name are then all set on the same line of code using variable set above as seen below.

$fileName = dirname(__DIR__, 3) . "/your-project-folder/" . $refUserId . "-" . $createdAt . "-request" . ".csv";

If you only want to save the CSV file(s) on your own web-server then thats all you need to do. However if you then want to move the files once they have been created to an external server via FTP, keep reading and follow the steps in section 2.

Part 2: Export to another server using FTP

If you wanted to automatically export the CSV file to another server (as per our use case), then you may need to add an additional function to help you do this. Below we’ve outlined this function that will help you on your way to achieving this.

What the code does

Similar to the function created in Part 1, move_csv, will first check to see which form has been submitted. As the form is designed to run side by side with create_csv, if the form with an ID of 01 has a new entry then this will trigger the remainder of the code.

if($form_id == 1234){ //replace 1234 with the id of the form
  ...
}

Please note: In theory, you can copy everything inside the first IF statement in Part 2 and paste this inside the first IF statement in the code block in Part 1. However, we’ve split these functions up to keep things simple for those who would like to stop at Part 1 of this tutorial.

Next, we’ve declared a variable containing the remote file path we’ll be moving our local file to later on.

$remote_file = "/remote-folder-path/" . $refUserId . "-" . $createdAt . "-request" . ".csv";

At this point, we need to DEFINE constants containing details of our remote server. To avoid storing sensitive details directly into our functions.php file, we’ll define our remote server host, username and password in our wp-config.php file instead which we’ll refer to later. Add the following three lines to your wp-config.php file and remember to update the values.

define('ftp_host', 'myremotehost.serveftp.com');
define('ftp_username', 'my_username');
define('ftp_password', 'my_password');

Once we’ve saved our constants securely in our wp-config.php file, we can safely grab these details within our function as we’ll need these when connecting to our server.

$ftp_host = ftp_host; /* ftp_host defined in WP Config */
$ftp_user_name = ftp_username; /* ftp_username defined in WP Config*/
$ftp_user_pass = ftp_password; /* ftp_password defined in WP Config*/

We’re almost ready to move our file to the remote server. What we’ve done is store our local file inside a variable as this will make it a lot easier to reference later.

$local_file = 'https://www.yourwebsite.com/wp-content/your-folder/csv_output/' . $refUserId . "-" . $createdAt . "-request" . ".csv";

The next two lines will enable us to connect and login to our remote server using the FTP details we stored earlier.

$connect_it = ftp_connect( $ftp_host );

$login_result = ftp_login( $connect_it, $ftp_user_name, $ftp_user_pass );

We’re now ready to transfer our local file to our remote server using the IF statement below.

if ( ftp_put( $connect_it, $remote_file, $local_file, FTP_BINARY ) ) {
    echo "WOOT! Successfully transfer $local_file\n";
} else {
    echo "Doh! There was a problem\n";
}

And that’s it. We can now safely close our remote connection.

ftp_close( $connect_it );

Putting it all together

Putting it all together will then look something like this:

add_action('frm_after_create_entry', 'create_csv', 30, 2);

function create_csv($entry_id, $form_id) {
	if ($form_id == 1234) { //replace 1234 with the id of the form

		// Collect the form data - Add a new row for each field you want to export and give it a unique name
		$firstName = isset($_POST['item_meta'][1537]) ? $_POST['item_meta'][1537] : '';
		$lastName = isset($_POST['item_meta'][1538]) ? $_POST['item_meta'][1538] : '';
		$email = isset($_POST['item_meta'][1540]) ? $_POST['item_meta'][1540] : '';
		$organizationId = isset($_POST['item_meta'][1547]) ? $_POST['item_meta'][1547] : '';
		$organizationName = isset($_POST['item_meta'][1548]) ? $_POST['item_meta'][1548] : '';
		$createdAt = isset($_POST['item_meta'][1555]) ? $_POST['item_meta'][1555] : '';

		// The header row of the CSV - Add a name for each field in the form
		$header = "firstName,lastName,email,organizationId,organizationName,createdAt\n";

		// The data of the CSV - Add each of the form fields listed above
		$data = "$firstName,$lastName,$email,$organizationId,$organizationName,$createdAt\n";

		/*
		 * The file name of the CSV.
		 * 
		 * NB: To save a single file per entry you will need to make the file name unique 
		 * This can be done using the entry ID or the date & time stamp by including the hour, minutes & seconds. 
		 * E.g at 12:38:43 the file will be named "TestUser-21-02-05-12-38-43-request.csv".
		 * One second later the time (and file name) will be "12:38:44".
		 * Then a new file "TestUser-21-02-05-12-38-44-request.csv" will be created.
		 * How you name the file will determine if you have a new file for each entry, a new file per user or a single file with all entry data.
		 */

		$fileName = dirname(__DIR__, 3).
		"/your-project-folder/".$refUserId.
		"-".$createdAt.
		"-request".
		".csv";

		/*
		 * Create the CSV file.
		 * If file exists, append the data to it. Otherwise create a new file.
		 */
		if (file_exists($fileName)) {
			// Add only data. The header is already added in the existing file.
			file_put_contents($fileName, $data, FILE_APPEND);
		} else {
			// Add CSV header and data.
			file_put_contents($fileName, $header.$data);
		}
	}
}

// Thats it for creating a CSV file of the data when the form is submitted. To automatically move the file to an external FTP use the function below, if not delete it.

add_action('frm_after_create_entry', 'move_csv', 40, 2);

function move_csv($entry_id, $form_id) {
	if ($form_id == 1234) { //replace 1234 with the id of the form

		/**
		 * Thanks to Shellcreeper for this function
		 * Transfer (Export) Files Server to Server using PHP FTP
		 * @link https://shellcreeper.com/?p=1249
		 */

		/* Remote FTP File Name and Path */
		$remote_file = "/remote-folder-path/".$refUserId.
		"-".$createdAt.
		"-request".
		".csv";

		/* FTP Account (Remote Server) */
		$ftp_host = ftp_host; /* ftp_host defined in WP Config */
		$ftp_user_name = ftp_username; /* ftp_username defined in WP Config*/
		$ftp_user_pass = ftp_password; /* ftp_password defined in WP Config*/

		/* File and path to send to remote FTP server */
		$local_file = 'https://www.yourwebsite.com/wp-content/your-folder/csv_output/'.$refUserId.
		"-".$createdAt.
		"-request".
		".csv";

		/* Connect using basic FTP */
		$connect_it = ftp_connect($ftp_host);

		/* Login to FTP */
		$login_result = ftp_login($connect_it, $ftp_user_name, $ftp_user_pass);

		/* Send $local_file to FTP */
		if (ftp_put($connect_it, $remote_file, $local_file, FTP_BINARY)) {
			echo "WOOT! Successfully transfer $local_file\n";
		} else {
			echo "Doh! There was a problem\n";
		}

		/* Close the connection */
		ftp_close($connect_it);
	}
}

And thats it. We’ve tried to add as much markup as possible to the code itself to help explain further what each part does so amending this to your own needs should be quite straight forward.