Knowledge Base

Import Content From CSV

Last Modified:
17 Jul 2019
User Level:
Administrator

You’re not limited to databases when using Content Syncing. There are times when your content is in a spreadsheet. In this case, you can use a CSV (Comma Separated Value) file

These are the steps to follow when creating syncing a CSV file with TERMINALFOUR.

Since TERMINALFOUR v.8.2.1, a CSV driver is included in the product so there is no need to install an additional one.
Contact support if you need advice on this.

1. Copy the CSV File to your CMS Server

Copy the CSV File to the CMS server. For example at

/web/terminalfour/csv/

Have a look at a Sample CSV File. The CSV file needs to use a comma (,) as the delimiter.

2. Configure the Data Source

To set-up the Data Source, you’ll name it and provide the file and driver locations.

Go to System Administration > Configure Integration Tools > Data Sources and populate the fields.

Screenshot of the Data Sources Configuration screen

ItemDescriptionExample

Name

A meaningful name for the Data Source.
This is required.
Staff Profile Data Store
Username You can provide a username here if required. dummyUser
Password You can provide a password here if required. fE62kYDEer
URL The url encoded URI at which the CSV file or directory of files is located.
This must be a location accessible by the CMS.
This can either point to the directory containing the CSV file, to the file itself or to an external address at which the file can be found.
The correct prefix corresponding to the CSV driver must be used in front of the URI.
This is Required.

If pointing to a directory on a Linux server:
jdbc:t4csvjdbc:file:///path/to/directory

If pointing to a directory on a Windows server:
jdbc:t4csvjdbc:file:///c:/path/to/directory

If pointing to a single file 'staff profiles.csv' on a Linux server:
jdbc:t4csvjdbc:file:///path/to/directory/staff%20profiles.csv

If pointing to a single file 'staff profiles.csv' on a Windows server:
jdbc:t4csvjdbc:file:///c:/path/to/directory/staff%20profiles.csv

If pointing to an external CSV file: jdbc:t4csvjdbc:http://samplecsvs.s3.amazonaws.com/Sacramentorealestatetransactions.csv

  Separator Parameter From TERMINALFOUR 8.2.17 you can use a custom separator by appending a parameter to the end of the URL. The separator character must be URL encoded when added to the URL, e.g. appending ?t4-csv-separator=%7C will use a pipe (|) separator rather than the default comma (,) separator. 

This URL Encoding Tool will help you encode characters. 

Driver The name of the driver used for CSV files. This is required. This is always:

com.terminalfour.database.t4csvjdbc.core.JDBC

3. Create a Content Type (if required)

If you haven't one created already you'll need to create a Content Type for the External Content Syncer to store the information that is currently in the CSV File. For example, we could create a "People" content type with the following elements:

Element Name   Element Type
First Name Plain Text
Last Name Plain Text
Email Address Plain Text
Phone Number Plain Text

4. Setting up the Content Sync

Once the CSV has been successfully set up as a Data Source, the content is extracted with SQL (Structured Query Language). To add the SQL go to System Administration > Configure Integration Tools > External Content Syncer.

For details on External Content Syncer General Settings, refer to the page in the documentation.

Click to Create New Data Store and use the following settings:

  • Configure other options as appropriate (see documentation on the External Content Syncer for further information)
  • Select SQL Query
ItemDescription
Name Give the Data Store a meaningful name (e.g., People Import from CSV)
Description Provide a description of the import
Data Source: From the dropdown select the name of the CSV Data Source you configured
Root Section Select the Section where content will be imported to
Channel Select a Channel that will publish the content 

Configure other options as appropriate (see documentation on the External Content Syncer for further information)


Screenshot of the External Content Syncer settings screen

You can add one or all columns using a SELECT statement. The CSV’s filename is used in the FROM clause, i.e., SELECT * FROM [filename]

E.g.:

SELECT * FROM staffprofiles
SELECT Fullname FROM staffprofiles

All non-alpha-numeric characters are removed from column names. Keep this in mind for mapping.In the example above, the columns would be mapped as follows:

Name of Column Name in Mapping
FullName FullName
First Name FirstName
Last Name LastName
Title Title
E-mail Email
Category Category
Staff_Category StaffCategory
Location Location
Number Number

If any of the values in the CSV file contain a comma, it will be interpreted as a new column.
To avoid this you can escape the comma by enclosing the value in double quotes>
e.g., Sara, Connor, "Web Developer – HTML, CSS, JavaScript, PHP", sconnor@t2.com, etc...

If your CSV contains a date value that you want to use in a date formatted T4 Tag, you should import it as a string in the format yyyy-MM-dd HH:mm:ss (e.g. 2018-03-31 23:59:59). Both the date and time are required.

If you're not sure, this article on date and time formats provides examples.

Back to top