Importing data from FTP server into Zoho Creator

Zoho Creator has grown to be a great cloud-based app creation platform. You can build simple apps in the matter of hours or have complex solutions up and running much quicker than a from-scratch project.

Manipulating data is a core capability of (Zoho) Creator . There is a rich toolset of UI elements, business logic components, and reporting tools to make your life easier. If you need more functionality you can always create custom functions and behaviours using the powerful Deluge scripting language. To take it even further there is a rather capable REST API to connect your app to the outside world.

In this post I’ll show you how to import data from an FTP server into a Creator app using a Deluge function and a little middleware.

Note: I assume that you are familiar with Creator and Deluge. We will also get some help from a PHP script, so some PHP knowledge is helpful too, but not necessary. You can use the code in this tutorial without changing anything other than the FTP url and account.

Problem

You have created a sales management app that keeps track of sales. The app is working great and users are able to place orders and record each transaction. There is, however, a small bottleneck.

The company uses a legacy inventory management system to keep track of all the goods and material available to sell. The only way this system can communicate with the outside world is via FTP.

Every morning at 1:00AM EST the legacy system exports a list of all products and available stock to a CSV file and places it on company’s FTP server. Other software systems in the organization consume that file by importing it manually every morning.

Your sales management app is no exception either and needs to import this CSV file every morning to have the updated product list available.

Solution

Deluge offers getURL(), a function that allows apps to retrieve data from external sources using HTTP protocol. In our example, this data is CSV, but it could very well be JSON, or plain text.

Since getURL() can only send HTTP requests, connecting to a secure (password protected) FTP server is not directly possible. For this purpose you need to either place the remote file(s) on a non-secure site, or use a middleware to extract data for you. Placing private company data in a non-secure location is never recommended. So the only solution is to create a small middleware that takes care of extracting data from the FTP server and sends it back to the Creator app. Now, a middleware can do way more than that, but for the purpose of our tutorial all it needs to do is to connect to the FTP server, load the CSV file, and send its contents back via a HTTP response.

Step 1 – The Data File

Before everything make sure you have the demo CSV file, inventory.csv, ready and uploaded to the FTP server. A sample file is shown below:

https://gist.github.com/4ad0bdb9d6794db4e407

Step 2 – The Middleware

First, we need to create the middleware and deploy it on a web server. Note that you can’t store this in Creator and you will need to have access to a web server, such as where your company site is hosted. For this purpose we are going to use the good old PHP because it is easy to code and maintain and works on pretty much any web server.

On your local machine create a PHP file, ftp.php, and paste the code below in it. Make sure you change the values in lines 5-7 and line 9 to reflect your server settings.

https://gist.github.com/b1d4dc87c7034dc07641

Now upload ftp.php to the web server. Test if the script works properly. Simply open the PHP file in the browser. It should return the contents of the CSV file and display it in the browser similar to the following figure.

Note: Depending on the version of PHP on your web server, there is a memory limit to how big of a CSV file you can load up in one go. If you need to access massive CSV files, you may need to change the code in the middleware.

Step 3 – The Creator App

For our little demo, we are going to create a simple app featuring 2 forms:

1. Products – This form contains the latest products available in stock. Our deluge script will pull the inventory every morning and update the list of products in this form.
2. Add Order – This form allows users to place new orders. It collects order, customer, and products information. A list of available products will be populated from Products to make sure employees are selling what is in stock.

I assume you know how to create forms in Creator, but to make sure you have all the necessary fields for the deluge function to work, here is how the Products form should look like:

Now our app is ready to receive data. By creating a form, the data storage mechanism behind it is also created. I’m reluctant to use the term table here because unlike other environments, in Creator each form represents both the visual and the data layer of each entity.

Step 4 – The Deluge Function

To create a function in Creator follow these steps:

1. Click on Workflow button on the left sidebar.
2. Select Functions tab.
3. Select Write script / Copy same function button.
4. Type in the code below in the text area.
5. Click Create the Function.

https://gist.github.com/2d6255700434f10044c5

The code above simply sends a request to ftp.php and loads the results, i.e. the content of the CSV file, into variable csv. Then it displays the loaded data showing that everything works as intended.

Go ahead and click on Execute Script and see the results. The content of inventory.csv, which you have placed on the FTP server earlier will be displayed in a dialog box. You have successfully imported data from a remote FTP server into Zoho Creator, but there still remains a very import step: Storing imported data in Products form.

Step 5 – Storing Imported Information

The next step is to parse the CSV data and insert each row into Products.

Update importInventory() function with the following code:

https://gist.github.com/566ef37028e0d74e4568

The code is self-explanatory, but let’s revisit a few points here:

• Line 7: To break the CSV data and put each line into a Deluge list item, “\n” (Lf) is used as a separation indicator. Sometimes CSV lines are not broken with “\n”. If you experience an issue when importing try using “\r” (Cr) instead.
• Line 10-13: If you skip emptying Products before each import, new data will be appended to the existing list of products, which is not what we want. In other scenarios appending may make perfect sense and you can omit these lines.
• Line 34-41: The actual insertion of a record into Products happen here. So make sure you are finished with data processing or cleanup before this point.

Save the changes by clicking Save Script and execute. Hopefully you will get a message that script executed successfully.

Now run the application and open Products Report view (created automatically). Newly imported rows should be displayed in the report.

Step 6 – Scheduling the Imports

One last thing remaining is to make sure this magical script runs every morning and imports an updated list of products. Creator offers a convenient scheduler to help automate various processes.

To schedule importing products on daily basis, follow these steps:

1. Select Schedules tab. You will see 3 different types of schedules available.
2. Create a custom schedule by clicking the Create button in the Custom Schedule box.
3. Under When to do? tab fill in the boxes as shown below and then click Next.
4. Under What to do? tab enter the function name we want to run, i.e. importInventory(), save the changes, and click Execute Now to ensure everything works.

That is it. Creator will run the importInventory() function every morning at 2:00 AM EST and import the latest inventory list into Products.

Summary

Congratulations! You have just created a solution for one of the common challenges of today’s database apps in a few hours, thanks to Zoho Creator. By getting a little help from a simple middleware written in PHP we were able to access information on a secured FTP server. For HTTP requests (common with JSON and XML data sources) you don’t need a middleware and you can pretty much do all the work from within Creator. This means one less piece of software to maintain and that is always good news.

If you know a better way to import data into Creator, or an improvement to the current method, please share it in the comments.