Import from Excel

The “Import from Excel” script template imports data from a Microsoft .xlsx or .xls file.

Note: If you are importing multiple sheets, as specified through the “Sheet name” parameter, the data from each sheet will be appended into a single table in Civis Platform. All sheets to be imported should have the same header, and the header should be on the first row.

Parameters

• Excel file (required): File ID or local Excel file.
• Sheet name (optional): If this parameter is provided, all sheets whose name matches it will be imported. If not provided, all sheets in the workbook will be imported. This parameter supports both substring matching and regular expressions. 
  • For example, you have a workbook with sheets named ["info", "annual report 2021",  "annual report 2022", "annual report 2023"]. If this parameter is set to "report" (without quotes), it will import "annual report 2021",  "annual report 2022", "annual report 2023". If this parameter is set to “(2021|2022)” (without quotes), it will import only "annual report 2021" and "annual report 2022".
  • To import only one single sheet named “Sheet1” (the default name for the first and only sheet when you create an Excel file), set this parameter to “Sheet1$” (without quotes) – the “$” matches the end of the string, thereby excluding sheet names “Sheet10”, “Sheet11”, etc.
• Database (required): The Civis Platform database to which to write records.
• Database Credential (required): A valid database credential for the Civis Platform database. To use the credential associated with the logged-in user, choose from the dropdown the credential that has this user’s Civis Platform username.
• Schema and Tablename (required): The schema and table name you would like to import the data into, e.g., schema.table.
• Column Data Type Overrides (optional): A mapping from column names to data types to use instead of the data types from the data source. Each line should be in the form of "column_name: DATA_TYPE" (without quotes), e.g., "foo_bar: VARCHAR". Only provide an entry for columns for which you want to override the data type; do not provide entries for columns that should use the default data type from the data source. This parameter is useful for data type issues that would otherwise cause the import to fail, e.g., use "foobar: VARCHAR" if the data source says "foobar" should be a numeric type but it actually contains non-numeric values.
• Existing Table Exists (optional): The action to take if a table with the requested name already exists. Options are:
  • Append: Records will be appended to the existing table
  • Fail: The sync job fails if the table exists
  • Drop: Removes the entire table including column information.
  • Truncate: Records on the table will be wiped out while maintaining the current columns.