The Import Data feature is available to import data into an existing table that contains fields which correspond to the data to be imported. There are a few methods available to import data which include specifying a URL address that points to either a CSV file or a resource that will return a JSON data set.
To import data into a table:
1. First, ensure that the table to which you are importing data contains all the required fields to hold the data. Note that there is also an option to create fields during the import process.
2. On the sidebar menu, click on the drop-down arrow next to the desired table, and click Import Data. This will display the Import Data Page.
3. Click Create Import at the upper-right corner of the page.
4. On the next screen displayed, select an Import Type from the drop-down option, and follow the steps below based on the import type selected.
The following import types are available:
| CSV File | Easily upload a CSV File |
| CSV Endpoint | Pull data from a public URL that points to a CSV File. This can also be used to import data from one table to another. |
| MYSQL | Pull data from a MYSQL database |
| MSSQL Server | Pull data from a MSSQL database |
| PostgreSQL | Pull data from a PostgreSQL database |
| JSON File | Upload a JSON File from the user's desktop |
| JSON Endpoint | Pull data from a public URL that points to a JSON Data set |
Follow the steps below based on your chosen Import Type:
A. To import a CSV File from your computer:
- Select CSV File from the drop-down, then Click on Choose File to select a CSV file from your computer.
B. To import a CSV from a URL:
- Select CSV Endpoint from the drop-down, then enter the URL address of the location containing the CSV file and click Import CSV.
Note: The CSV Endpoint option can be used to import data from CSV files stored online in applications like Dropbox, Google Drive, or Box . The CSV URL address will be the File Link.
- For Dropbox: Enter the File Link from Dropbox, then set the last part of the link from dl=0 to dl=1 (example: https://www.dropbox.com/s/../ImportFile.csv?dl=1).
To get the File link in Dropbox, simply click Share > Create Link > Copy Link. - For Google Drive: Enter the File Link from Google Drive, then set the last part of the link from "edit#gid=0" to "export?format=csv" (example: https://docs.google.com/spreadsheets/d/../export?format=csv
- To pull data from one table to another, the following link can be used: Ignatius.io/api/report/csv?reportId=XX |
C. To import data from a database:
- Select the import type from the drop-down (MYSQL, MSSQL, PostgreSQL), then enter the Connection Parameters for the specific database which include the database, server, username and Password. Click Connect when all the credentials are entered
D. To import data from a JSON data set:
- Select JSON from the drop-down, then enter the URL address of the resource (API) location that will return the JSON data set. Then, select Import JSON.
5. Once the import step is complete, select the Field Mapping Tab.
6. On the Field mapping screen, under the Import Type Section:
You will have the option to merge in records from the file to the table. The merge option will update the records that already exist in the table based on a merge key field/column specified.
To merge records, select the Merge Key Field (a unique field in the table such as a unique identifier like Order Number), and select a Source Field (the field in your data that corresponds to the Merge Key Field specified).
Sync with input data: This option will automatically remove data that is not in the import file. The sync means it is an exact match of the import data. The sync is essentially an upload to refresh the data that already exists, it will remove all other data that cannot be matched.
Insert unmatched data: This option will be used to insert new records if they do not already exist.
7. On the Field mapping screen, under the Filter Data Section:
You will be able to filter the data you wish to import. To filter data, select a desired Column (field from your data), a Condition (for example “start with” or "is equal"), and enter the Value you wish to filter.
Use the +/- Action buttons to add or remove criteria.
8. On the Field mapping screen, under the Field Mapping Section:
You will map the fields from your data (Source Fields) to the fields/columns in your table.
For each source field you will have the following mapping options:
A. To Existing Field – if you wish to Map to an Existing Field in your Table:
- Select the To Existing Field option and click on the drop-down to select the field on your table you wish to map the source field to.
B. Do Not import - if you do not wish to import a specific column from your data:
- Simply select the Do Not Import option. The data from the source field will not be imported.
C. Create a New Field – if you wish to create a new field in order to import a field that currently does not exist in your table:
- Click Create New Field and enter a Field Name and Field Type on the screen displayed. The option to label the record as unique can be done by selecting "Must be unique", then click Save Changes.
9. Continue to map all source fields as needed. Once the Field mapping has been completed, scroll down and click Next at the bottom right.
9A. If importing data from a CSV File:
- Click Save Changes on the next screen. The import should be complete at this point, and the data should now be imported to your table.
9B. If importing data from a CSV Endpoint URL, JSON URL, or Database: You will have the option to create a data refresh schedule.
A data refresh can be scheduled to automatically import data from the import location into your tables. This will ensure that your data is automatically updated regularly rather than you having to manually refresh the data. This will also save the Field Mapping.
| Note: The Import Field Mapping can be saved when an import is created from CSV Endpoint URL, JSON URL, or Database. If the Field Mapping is saved, you will not need to map your fields again during your next import. An import from a CSV File will not save the Field Mapping. |
If you wish to add a data refresh schedule, follow these steps:
- On the Schedule Screen, enter a Name for the Schedule, a Frequency (Hours, Days, Weeks, Months, Years), and an Amount for the frequency (the interval of time or rate at which the data refresh needs to occur). For example, for a frequency of an hour: the data would be pulled from the Import location every hour.
