Automation Action: Update A Database Using CSV Or Json

This action can be used to automatically insert or update multiple records in a database from CSV or Json text (or file). The column names used in the SQL commands are mapped to CSV columns or Json paths. ThinkAutomation will then create the necessary SQL commands automatically.

Select a Database Type to connect to from the list. You must specify a Connection String that ThinkAutomation will use to open the database. Click the ... button to build the connection string. Click the Test button to verify that ThinkAutomation can connect to the database.

Specify the Table Name to be updated. Click the Get Tables button to read the table names from the database schema. You can then select a table from the Table Name drop-down list.

From the Update Using list, select:

• CSV Data - to update using CSV data or a linked file.
• JSON Data - to update using JSON data or a linked file.

For CSV data, enable the CSV Has Header Row if the CSV data contains column names in the first line.

For Json data, you can optionally specify the Start At Path (using dot notation).

In the CSV/Json Data Or File Path entry, specify the source CSV or Json data. This can be a %variable% containing the data. You can also specify a file path (or a variable containing a file path). If a file path is used, the file will be read and the contents used for the source data when the Automation executes.

Complete the Column Mappings grid to map database columns to your source data columns.

When the Automation executes all records in the CSV/Json will be inserted or updated in the database. The database update is performed within a single transaction. This means that if one insert or update fails, then the transaction is rolled back and no database changes are made.

The number of inserted and updated database rows can be returned to variables. Select the variables from the Assign Inserted Count To and Assign Updated Count To lists.

Mapping Source Columns To Database Columns

You must map columns from the source data to columns in the database table you want to update. The easiest way to do this is to first paste a sample of your CSV or Json data into the CSV/Json Data editor, then use the Auto Map button. Once you have mapped your columns, replace the CSV/Json Data value with your %variable% that will contain the CSV/Json data or file path.

In the Column Mappings grid, for each source column, specify the Database Column Name, Type, Size and Source Column Name Or Value.

Click the Auto Map Database Columns To Data Columns button to auto-map. This will populate the column selector drop-down lists and also match database column names with source column names. You can then manually match where needed.

The Source Column Name Or Value can either be a column name in your source data or a %variable%. If you use a %variable% then the database column will be assigned the fixed %variable% value for each row inserted/updated.

For the Size value, you can specify the database column size for text data types. If a size is specified then the source data will be trimmed before being assigned to the database column to ensure it fits. Specify zero for no auto-trimming.

Enable the Key option for database columns where you want to update existing database records instead of inserting new. Multiple columns can be set as keys. ThinkAutomation will then first check if a record exists with the key field values by issuing a SELECT * FROM table WHERE keyfield1 = @value [AND keyfield2 = @value] command. It will then execute the UPDATE command if a record is found or the INSERT command otherwise.

CSV Data

For example, suppose we have the follow CSV data:

Index,Organization Id,Name,Website,Founded,Industry,Number of employees
1,FAB0d41d5b5d22c,Ferrell LLC,https://price.net/,1990,Plastics,3498
2,6A7EdDEA9FaDC52,"Mckinney, Riley and Day",http://www.hall-buchanan.info/,2015,Ceramics,4952
3,0bFED1ADAE4bcC1,Hester Ltd,http://sullivan-reed.com/,1971,Public Safety,5287              

And a database table:

The Automap button would automatically map the CSV columns to the database columns (ignoring case and spaces). The 'Index' could be marked as a key.

When the Automation executes, 3 new records will be inserted. If the Automation was run again with the same CSV data then the 3 records would be updated if they already exist in the table with the same 'Index' value.

Excel Data

If you want to update a database from an Excel spreadsheet you can use the Lookup From Excel action to read a range of cells in CSV format. You can then use the variable containing the CSV text as your source CSV data.

JSON Data

When using Json data instead of CSV, the Json must be a Json array. The objects in the array must all be the same type. Sub-objects are not supported.

For example, the following JSON data could be used for the above database example:

[
  {
    "Index": 1,
    "Organization Id": "FAB0d41d5b5d22c",
    "Name": "Ferrell LLC",
    "Website": "https://price.net/",
    "Founded": 1990,
    "Industry": "Plastics",
    "Number of employees": 3498
  },
  {
    "Index": 2,
    "Organization Id": "6A7EdDEA9FaDC52",
    "Name": "Mckinney, Riley and Day",
    "Website": "http://www.hall-buchanan.info/",
    "Founded": 2015,
    "Industry": "Ceramics",
    "Number of employees": 4952
  },
  {
    "Index": 3,
    "Organization Id": "0bFED1ADAE4bcC1",
    "Name": "Hester Ltd",
    "Website": "http://sullivan-reed.com/",
    "Founded": 1971,
    "Industry": "Public Safety",
    "Number of employees": 5287
  }
]              

You can use the Start At Path entry to specify an array path within the Json, for example:

{
    "Id": "1234",
    "Customers": [
        {
        "Index": 1,
        "Organization Id": "FAB0d41d5b5d22c",
        "Name": "Ferrell LLC",
        "Website": "https://price.net/",
        "Founded": 1990,
        "Industry": "Plastics",
        "Number of employees": 3498
    },
      {
        "Index": 2,
        "Organization Id": "6A7EdDEA9FaDC52",
        "Name": "Mckinney, Riley and Day",
        "Website": "http://www.hall-buchanan.info/",
        "Founded": 2015,
        "Industry": "Ceramics",
        "Number of employees": 4952
    },
      {
        "Index": 3,
        "Organization Id": "0bFED1ADAE4bcC1",
        "Name": "Hester Ltd",
        "Website": "http://sullivan-reed.com/",
        "Founded": 1971,
        "Industry": "Public Safety",
        "Number of employees": 5287
      }
  ]
}              

If the Start At Path is set to 'Customers', then only the Customers array will be used.