How to automate an FTP,
SFTP import in Timetonic?
Your field teams log their interventions in Timetonic.
Your salespeople track their clients in a CRM.
Your accounting team works in its own tool.
Today,
we will see how to connect Timetonic with your other information system.
In both directions,
with no development required.
Here is a diagram that illustrates the very simple principle of data flow.
To connect Timetonic to your information system,
we use a shared storage space,
a file server hosted by your company or a partner.
First step,
access to the server.
Before setting anything up in Timetonic,
you need to be able to connect with your operating system.
FileZilla,
but there are plenty of other options.
This is where you prepare the folders,
those for exports,
those for imports,
and possibly archival folders for files already processed.
During the import.
With access in place,
you'll set up automated transfers in Timetonic.
The export places a file on the server.
And the import retrieves one.
Your other tools do the same on the other side.
Now,
we will configure a recurring import with archiving.
We go to a contacts table.
In this contacts table,
we will configure an import that will allow us to have weekly updates of contact information and the creation of new contact rows.
Before configuring the import in Timetonic,
we prepare two prerequisites on the server side.
This data import arrives on your server.
First prerequisite,
a reception folder where the files will be dropped.
Here,
the path is HOME in the TTC file located in Émilie's folder,
the file we are concerned with is import contact CRM.
A second prerequisite, and this is important,
a subfolder named archive.
Today's file has just been dropped into the reception folder.
It contains an update of a contact and a new contact.
We will check after the import what it looks like in the table.
Let's return to our Timetonic workspace on our Contacts table.
Here is the state of my table filtered by the client Société Générale.
There is only one contact row.
Notice the phone number ending in 5678.
We keep this screen in mind,
we will return 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, identifying port 22,
the same logic as before for export configuration.
On the file side,
I attach the reception folder we just prepared,
which is in emily-import-contact-crm.
For the file name,
we insert the name identical to the file received on the server.
Then,
the underscore,
an asterisk,
the dot, and the file format.
An asterisk?
Because my CRM exports a file every week with a changing date.
The asterisk means anything at that spot.
And we will tell Timetonic to take the most recent one.
This way,
with each execution, it will find the correct file on its own.
I always keep the option activated that my file arrives with a header line.
Pay attention to the separator,
my CRM exports using a semicolon.
I change it here in the drop-down menu for separators.
Otherwise, Timetonic would try to read the comma and would place all my columns into a single cell.
The text delimiter is indeed double quotes.
To choose the date format of my incoming file,
I have information 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 rows,
I ignore the rows that are duplicated.
Alternatively,
I can choose to stop to generate an error report.
The 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 choose to ignore.
If an import is still in progress when another is triggered,
Timetonic ignores the second one instead of risking a conflict.
You can also choose to queue it for when the previous one is finished.
A weekly import like ours,
the risk is negligible.
So ignoring is sufficient. Here,
we,
in case of failure,
want the import to stop,
but you might prefer to continue the import.
Be cautious with this option that warns you if activated,
you will delete all the rows in your table before the import.
For the notification options,
it’s identical to the previous configuration of the export.
You enter the email of the person to be notified in case of failure and success.
Next,
in the Field Mapping section,
click on Import a CSV file.
I have inserted a template of the CSV we receive on the server.
Here Timetonic proposes the mapping.
It has read my file and displays the columns it has detected.
You can map column by column
or,
to go faster,
if the names are identical,
you can click on auto-associate by header names.
Timetonic will automatically link them through recognition.
One field just needs to be selected,
as it is different,
and I save.
I go to Edit.
I get a view of the mapped fields.
I see that I have 7.
I go back to Edit.
Here,
a crucial point.
The primary key box.
This indicates to Timetonic which column serves as a unique identifier to find the contacts.
I select the contact ID as the unique key.
I save.
And specifically,
what Timetonic will do for each line of the CSV,
it will search in the table for a contact with the same contact ID.
If it finds it,
it updates the fields that have changed.
If it doesn't find it,
it creates a new row.
Without this key,
you would have duplicates for your contacts with each execution.
Next,
in the calendar,
we would like to create a recurrence to schedule on Mondays,
at 6 AM UTC.
This way,
the contact table is updated the day before the teams start their shifts.
You save.
We will run a configuration test.
We have our three green lights,
the connection.
The file in our server is properly found.
In the test,
we analyze ten rows arriving in the CSV file,
where we see one creation and one update.
We will save.
You then find yourself in the file transfer window,
the import we just configured,
which recalls the information with a calendar scheduled each week.
Instead of waiting until Monday...
Instead of waiting until next Monday,
I will click on run now to see immediately the result with success.
A confirmation window indicates that the import has been completed.
Back to our table.
We had only one row with a number ending in 5678.
And with our import,
we have the update.
Of the contact C6's phone number and again!
We switch to the table.
Refresh your data if necessary.
Remember,
we had only one contact in the Société Générale view.
Now we have two.
We can see.
The first point,
the phone number of row C6 has been updated.
The old one ended with 5678,
the new one ends with 9999.
Timetonic found its row using the contact ID we had defined as the key and only updated the phone field.
No duplicates,
no data loss.
Second point,
row C9,
Rousseau and Lody,
legal advisor at Société Générale.
This row did not exist yesterday.
Timetonic recognized a new ID in the CSV and automatically created the row with a link to Société Générale in the client's table.
Now the archiving on the server side.
I go back to my import contact CRM file,
I no longer have the file that we imported.
I open the archive and I see that my file has been archived with the addition of the actual date when the import was completed.
There you go,
you will surely grasp the power of scheduling a data import.
You have a system that runs 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 or CRM on the other.
This doesn't require custom integration,
no development, and no connection to an API.
And we just saw two examples.
Now it’s your turn to explore the uses that correspond to your challenges.
Like making regular backups of 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 left to do is test !
