Video 2/2 · How to automate FTP/SFTP import in TimeTonic ?

How to automate an FTP, SFTP import into Timetonic? Your field teams record their interventions in Timetonic. Your salespeople track their clients in a CRM. Your accounting department works in its own tool. Today, we will see how to connect Timetonic with your other information system, bidirectionally, without any development. Here’s a diagram illustrating the very simple principle of data circulation. To communicate Timetonic with your information system, we use a shared storage space, a file server hosted by your company or a partner. The first step is accessing the server. Before configuring anything in Timetonic, you need to be able to connect to it with an operating system. FileZilla, but there are many others. This is where you prepare the folders, those for exports, those for imports, and possibly archival folders for already processed files. At the time of import, with access in place, you schedule automated transfers in Timetonic. The export drops a file on the server, and the import retrieves one. Your other tools do the same on the other side. Now, let's configure a recurring import with archiving. We go to a contacts table. In this contacts table, we are going to set up an import that will allow us to have weekly updates of contact information and the creation of new contact lines. Before configuring the import in Timetonic, we prepare two prerequisites on the server side. This data import arrives on your server. The first prerequisite is a reception folder where the files will be deposited. Here, the path is HOME in the TTC folder located in Émilie’s folder, the one that concerns us import contact CRM. A second prerequisite, and this is important, is a subfolder named archive. Today’s file has just been deposited in the reception folder and contains a contact update and a new contact. We will check after the import how it appears in the table. Let's return to our Timetonic workspace on our Contacts table. Here is the state of my table in the view filtered by the client Société Générale. There is only one contact line. Make sure to note the phone number ending with 5678. Keep this screen in mind; we will come back to it after the import. For the configuration, go to Automation, Synchronize with external files, Create from a new import, Name your import. For the SFTP connection to the server, identifier port 22, the same logic as before for the export configuration. On the files side, I attach the reception folder we just prepared, which is in emily-import-contact-crm. For the file name, we insert the same name as the file received on the server. After, an underscore, a star, the dot, and the file format. A star? Because my CRM exports a file each week with a changing date. The star means anything goes at this point. And we will tell Timetonic to take the most recent one. That way, each execution will automatically search for the correct file itself. I always keep the information that my file arrives with a header line activated. A small note on the separator, my CRM exports in semicolon. I change it here in the separator dropdown menu. Otherwise, Timetonic would try to read the comma and put all my columns in one cell. The text delimiter is indeed double quotes. To choose the date format of my incoming file, I know that the format is year, month, and day. I select the format in which my file is configured and arrives. And I leave the decimal separator in Timetonic's default format. For the import strategy, for managing duplicate lines, I ignore the duplicate lines, or conversely, I choose to stop to get an error report. For simultaneous execution, after processing the file, I select Archive. The processed file will automatically go into the Archive subfolder we prepared on the server side. We will check later that this is indeed the case. For simultaneous execution, I let it ignore. If an import is still in progress when another one is triggered, Timetonic ignores the second instead of risking a conflict. You can also choose to queue it for when the previous one is finished. For a weekly import like ours, the risk is zero. So ignoring is sufficient. Here, in case of failure, we want the import to stop, but you might very well want to continue the import. Be cautious with this option that tells you if you activate it, you will delete all the rows in your table before the import. For the notification options, it is identical to the previous export configuration. You enter the email of the person to notify in case of failure and success. Then, in the Fields mapping section, click on Import a CSV file. I inserted a CSV template that we receive on the server. This is where Timetonic offers me mapping. It has read my file and shows me the columns it detected. You can map column by column whenever you wish. Or, to go faster, if the names are identical, you click on auto-linking by header names. Timetonic automatically performs the linking through recognition. One field needs to be selected as it is different, and I save. I go to Edit. I get a view of the fields that have been mapped. I see that I have 7. I return to Edit. Here, a crucial point. The primary key box. This is what will tell Timetonic which column serves as the unique identifier for finding the contacts. I select the contact ID as the unique key. I save. And concretely, what Timetonic will do for each row of the CSV, it will look for a contact in the table with the same contact ID. If it finds it, it updates the changed fields. If it doesn’t find it, it creates a new line. Without this key, you would have duplicates for your contacts with each execution. Then, in the calendar, we want to make a recurrence to schedule Mondays at 6 AM UTC. This way, the contact table is ready a day before the teams take their positions. You save. We will run a configuration test. We have our three green lights: the connection. The file in our server is correctly found. In the test, we analyze ten lines arriving in the CSV file, on which we observe a creation and an update. We will save. You then find yourself in the transfer files window, the import we have just configured, which recalls the information with a schedule planned each week. Instead of waiting for next Monday, I will click on run now to see the result immediately with success. A confirmation window informs you that the import has been completed. Back to our table. We had one line with a number ending in 5678. And with our import, we received an update of the phone number for contact C6 and again! We switch to the table. Refresh your data if necessary. Remember, we had a single contact in the Société Générale view. We now have two. We can observe the first point; the phone of row C6 has been updated. The old one ended in 5678, the new one ends in 9999. Timetonic found its line using the contact ID we defined as the key and updated only the phone field. No duplicates, no data loss. The second point, row C9, Rousseau and Lody, legal advisor at Société Générale. This line did not exist yesterday. Timetonic saw a new ID in the CSV and automatically created the line with the link to Société Générale in the client table. Now for the archiving on the server side. I go back to my import contact CRM file; I no longer have the file we imported, I open the archive, and see that my file has been archived with an addition of the effective date when the import was made. There you go, you’ve surely grasped the power of planning a data import. You have a system that operates autonomously. Your reception folder remains clean. Your archives grow richer week after week. And you maintain complete traceability. In just a few minutes, we connected Timetonic to two blocks of your information system, for accounting on one side and the CRM on the other. This does not require custom integration, no development, and no connection to an API. And we have just seen only two examples. Now it’s your turn to explore the uses corresponding to your challenges, such as performing regular backups on your data, archiving your old data, feeding a reporting tool. To go further, find the link to the article in the description of this video. All you have to do is test !