Skip to main content
Version: v4

FTP/SFTP

Step 1: Create a data source​

There are two ways to create a data source: click the "+" button next to Data Source in the sidebar, or click the Create a Data Source button on the Overview page.

1_ftp

Step 2: Configure the connection​

In the dialog form, fill in the connection information and click Submit. The connection fields are described below.

3_ftp

Basic​

  • Name: The display name of the data source in Canner Enterprise. You can change it later.
  • Data Source Type: The data source type. Select FTP.

Data Source Properties​

File Format​

Each Canner Enterprise data source can connect to only one file format. Choose one of the file formats supported by Canner Enterprise. For example, if you choose CSV, only CSV files are connected, even if the specified FTP path contains JSON, Excel, CSV, and other formats.

URI​

The FTP path to connect to.
Path format: ftp(s)://<host>:<port>/<folder_name>

Special characters in the URI such as :?#[]@!$&'()*+; cause the system to misinterpret it, so make sure your folder and file names don't contain them.

Scan Type​

1. Directory​

Connects all files of the specified format in the directory.
Based on your folder structure and the two scenarios below, enter the folder path you want in URI.

Example file structure:

Superstore/
├─ Orders/
│  ├─ Orders_2014.csv
│  ├─ Orders_2015.csv
├─ People/
│  ├─ People_East.csv
│  ├─ People_North.csv
│  ├─ People_South.csv
│  ├─ People_West.csv

Scenario A. Each file under the specified path is a separate table
Connect the Directory at URI ftp://35.65.32.100/Superstore/Orders.
Once connected, this source has two tables in Canner Enterprise: Orders_2014 and Orders_2015.

Scenario B. Each folder under the specified path is a separate table
Connect the Directory at URI ftp://35.65.32.100/Superstore.
Once connected, this source has two tables in Canner Enterprise: Orders and People.

Note

This scenario requires all files in each folder to have the same format and schema.

Recursive Scan​

Check this option if the specified directory contains subfolders and you want all of them to be queryable in the workspace once connected.

2. Single file path​

Connects a single file at the specified path.
Enter the file path you want in URI.

Example file structure:

Superstore/
├─ Orders/
│  ├─ Orders_2014.csv
│  ├─ Orders_2015.csv

Example: connect the file at URI ftp://35.65.32.100/Superstore/Orders/Orders_2014.csv.
Once connected, this source has one table in Canner Enterprise: Orders_2014.

Note

If you choose Excel as the File Format, you can only connect files with Single file path. Enter the path of a single file, and the system parses each worksheet (Sheet) in the Excel file into a separate table.

3. Multiple file paths​

Connects all files of the specified format in the directory, lets you include/exclude specific files, and combines them into a single table.

Note

This requires all included files to have the same format and schema.

Enter the folder path you want in Basic URI, then set the file paths to include or exclude.

  • Include file paths: The file paths under the Basic URI to connect (wildcards (*) are supported, e.g. /<folder-name>/*.csv)
  • Exclude file paths: The file paths under the Basic URI to exclude (wildcards (*) are supported, e.g. /<folder-name>/*.csv)
Example file structure:

Superstore/
├─ 2021/
│  ├─ Orders_2021.csv
│  ├─ Orders_history.csv
│  ├─ People_2021.json
│  ├─ .metadata
├─ 2022/
│  ├─ Orders_2022.csv
│  ├─ People_2021.json
│  ├─ .metadata

Example: connect the Directory at Basic URI ftp://35.65.32.100/Superstore.
Include file paths: /2021/Orders_2021.csv /2022/Orders_2022.csv
Exclude file paths: /2021/Orders_history.csv
Once connected, this source has one table in Canner Enterprise, Superstore, which combines the two files /2021/Orders_2021.csv and /2022/Orders_2022.csv.

  • Recursive Scan

Check this option if the specified directory contains subfolders and you want all of them to be queryable in the workspace once connected.

  • Username / Password

The username and password used to connect to this SFTP server.

File Format Details​

  • File content with headers: Whether the CSV file has a header. When on, the first row of the file is parsed as column names.
  • Skip Lines: Skip the first N lines of the file.
  • Separator Character: The CSV delimiter.
  • Explore Timeout: Sets the timeout for connecting to and parsing the data source. If the connection fails and the Status error message shows an Explore Timeout error, try increasing this value to give the system more time to parse.
    • info

    Notes when the File format is Excel​

    1. Canner Enterprise only connects tabular data. Formatting such as fonts, bold, italics, underline, borders, and background colors is not shown in the connected table.
    2. Canner Enterprise only connects tabular data. Charts are not imported.
    3. Sparklines in worksheet cells are converted to null after connecting.
    4. For cells containing formulas, the calculated values are connected.
    5. If a column in a worksheet contains data of different types (mixed-type content), the system fails to read the data. Mixed-type content often occurs when:
      • A column has both text-formatted and number-formatted values
      • A column has a formula computing a total in the last row
      • Different cells in a column contain different formulas
    6. Canner Enterprise does not currently support the Time number format. If a worksheet contains this format, only the year/month/day is shown, not the full original data. For example, 2000-01-01 07:00 AM is shown as 2000-01-01.

    If there is a mixed-type content problem, the system shows an error message (see below). Use "Manage Mapping Columns" to change the column type to "varchar", and the data can be read normally.

    Manage Mapping Columns​

    Go to the Tables tab of this data source, and select Manage Mapping Columns on the table (the connected worksheet) you want to change.

    • Select Manage Mapping Columns
    • Enter the column type changes and click Submit to finish (click Reinitialize to restore the initial state)

    Step 3: Finish​

    After you submit, you are returned to the list page shortly, and FTP appears in the data source list.

    4_ftp