Data Sources (CSV, Excel, JDBC)

Data Sources (CSV, Excel, JDBC) in ServiceNow – Complete Guide for CAD Exam

Introduction

Data Sources are a foundational component of ServiceNow's data import framework. They define where and how external data enters the platform. Whether you're bringing in user records from a CSV file, asset inventories from an Excel spreadsheet, or live data from an external database via JDBC, understanding Data Sources is essential for the ServiceNow Certified Application Developer (CAD) exam and for real-world implementations.

Why Data Sources Are Important

Organizations rarely operate with ServiceNow as their only system. Data constantly needs to flow in from HR systems, CMDBs, ERPs, procurement tools, and flat-file exports. Data Sources provide a standardized, repeatable, and auditable mechanism to:

- Bring external data into ServiceNow in a controlled manner
- Feed Transform Maps, which handle the logic of mapping and transforming data before it lands in target tables
- Enable scheduled, automated imports so data stays current without manual intervention
- Maintain a staging (import set) table that acts as a buffer, allowing validation and error handling before committing records

Without Data Sources, administrators and developers would need to write custom scripts or use ad-hoc approaches, leading to inconsistency, errors, and maintainability challenges.

What Is a Data Source?

A Data Source is a record in ServiceNow (stored in the sys_data_source table) that defines the connection details and configuration for importing external data. It specifies:

- Type: The format or protocol of the external data (e.g., File, JDBC)
- Format: The file format when the type is File (e.g., CSV, Excel, XML, JSON)
- Import Set Table: The staging table where raw data lands before transformation
- Connection Details: File attachment, file path, or JDBC connection parameters
- Sheet/Header Information: For Excel files, which sheet to read; for CSV, the delimiter and header row

How Data Sources Work – The Import Set Framework

Data Sources are part of ServiceNow's broader Import Set framework. Here's the end-to-end flow:

1. Data Source Definition: You create a Data Source record specifying the type, format, and connection information.
2. Data Load: Data is loaded from the source into an Import Set Table (a staging table). Each load creates an Import Set record that groups the rows together.
3. Transform Map: A Transform Map defines how columns in the import set table map to fields in the target table (e.g., the Incident table, User table, or a custom table). It can include field mappings, coalesce fields, and onBefore/onAfter transform scripts.
4. Transformation: The transform runs, applying mappings and scripts. Records are inserted, updated, or ignored based on coalesce logic.
5. Completion: The import set rows are marked with a state (inserted, updated, error, ignored), providing a full audit trail.

Data Source Types in Detail

1. CSV (Comma-Separated Values)

- Type is set to File and Format is set to CSV
- You can attach a CSV file directly to the Data Source record or retrieve it from a file path on the MID Server
- Key configuration options include: delimiter character (comma by default, but can be tab, pipe, etc.), header row presence, and character encoding
- CSV is the most commonly used format for simple, flat data imports
- When a CSV file is loaded, ServiceNow automatically creates columns in the import set table based on the header row

2. Excel (XLS/XLSX)

- Type is set to File and Format is set to Excel
- You specify the Sheet Number or Sheet Name to indicate which worksheet to import
- The Header Row field tells ServiceNow which row contains column headers (default is row 1)
- Excel files can contain multiple sheets, but each Data Source imports from only one sheet at a time
- If you need to import from multiple sheets, you need multiple Data Sources

3. JDBC (Java Database Connectivity)

- Type is set to JDBC
- This allows ServiceNow to connect directly to an external relational database (e.g., MySQL, Oracle, SQL Server, PostgreSQL)
- Requires a MID Server to act as a bridge between ServiceNow (cloud) and the on-premises or network-accessible database
- Configuration includes: MID Server selection, JDBC driver class, connection URL, database username and password, and a SQL query or table name
- The SQL query defines exactly what data to pull from the external database
- JDBC Data Sources are powerful for real-time or scheduled synchronization with external databases
- Important: The MID Server must have the appropriate JDBC driver (JAR file) installed in its mid_server/extlib directory

Key Configuration Fields on a Data Source Record

- Name: A descriptive name for the data source
- Import set table label/name: The staging table name (auto-generated or manually specified, prefixed with u_imp_)
- Type: File or JDBC
- Format: CSV, Excel, XML, JSON (applicable when Type is File)
- File retrieval method: Attachment, file path (via MID Server), or FTP
- MID Server: Required for JDBC and file-path-based imports
- Header row: Row number containing column headers (Excel)
- Sheet number: Which sheet to read (Excel)
- SQL Statement: The query to execute (JDBC)
- Connection URL: JDBC connection string

Scheduled Imports

Data Sources can be associated with Scheduled Imports to automate recurring data loads. A Scheduled Import combines:
- A Data Source (where to get data)
- A frequency/schedule (when to run)
- Optionally, an associated Transform Map (to automatically transform after loading)

This is critical for keeping ServiceNow data in sync with external systems without manual effort.

Coalescing – Avoiding Duplicate Records

When importing data, you often want to update existing records rather than create duplicates. This is handled by coalesce fields in the Transform Map. A coalesce field acts as a unique key: if a match is found in the target table, the record is updated; if not, a new record is inserted. Understanding coalescing is essential and frequently tested on the exam.

Import Set Table vs. Target Table

- Import Set Table (Staging Table): A temporary holding area where raw data from the Data Source lands. It mirrors the structure of the incoming data.
- Target Table: The actual ServiceNow table where data ultimately needs to reside (e.g., cmdb_ci_server, sys_user, incident).
- The Transform Map bridges these two, mapping columns from the staging table to fields on the target table.

Common Exam Scenarios

- You may be asked about what happens when a CSV is loaded without a header row (columns get generic names like u_field_1, u_field_2)
- Questions about what is required for a JDBC Data Source (MID Server, JDBC driver, connection URL, credentials)
- Scenarios about importing Excel files and choosing the correct sheet
- Understanding the order of operations: Data Source → Import Set Table → Transform Map → Target Table
- Questions about how to handle duplicate data (coalesce fields)
- Understanding that the import set table is automatically created if it doesn't exist

Exam Tips: Answering Questions on Data Sources (CSV, Excel, JDBC)

1. Remember the Flow: Data Source → Import Set (Staging) Table → Transform Map → Target Table. This sequence is fundamental and appears in many questions. If a question asks what happens first or what connects to what, trace this flow.

2. JDBC Always Requires a MID Server: This is a frequently tested point. Since ServiceNow is cloud-based, it cannot directly access on-premises databases. A MID Server is mandatory for JDBC Data Sources. If you see an answer option mentioning JDBC without a MID Server, it is likely incorrect.

3. CSV and Excel Are File Types: When the Data Source type is File, the format can be CSV, Excel, XML, or JSON. Don't confuse Type with Format. JDBC is a Type, not a Format.

4. Excel Sheet Configuration: Remember that for Excel Data Sources, you must specify the sheet number or sheet name. Each Data Source handles one sheet. Multiple sheets require multiple Data Sources.

5. Coalesce Is About Updates, Not Inserts: When a question asks how to prevent duplicate records during import, the answer involves setting coalesce fields on the Transform Map. Coalesce = match key = update if found, insert if not.

6. Import Set Tables Are Staging Areas: They do not represent final data. Data in import set tables is raw and untransformed. The transformation step is what moves data to the target table with proper mapping and logic.

7. Scheduled Imports Combine Data Source + Schedule: If a question asks how to automate a recurring data import, the answer is a Scheduled Import, not just a Data Source alone.

8. Watch for Script-Related Questions: Transform Maps support onBefore and onAfter transform scripts. onBefore scripts run before each row is mapped; onAfter scripts run after each row is processed. Know the difference.

9. File Retrieval Methods: Data can come from an attachment on the Data Source record, a file path accessible via MID Server, or FTP. Know that file path and FTP methods require a MID Server, while attachments do not.

10. Eliminate Wrong Answers: If an answer choice suggests that Data Sources directly write to target tables (skipping the import set), it is wrong. The import set framework always stages data first.

11. Encoding and Delimiters: For CSV, be aware that you can configure the delimiter (comma, tab, semicolon, pipe) and character encoding (UTF-8, etc.). If a question mentions data with special characters or non-standard delimiters, think about these settings.

12. Robust Testing Strategy: In practice and on scenario-based questions, always test with a small dataset first, validate the import set data, check the transform log, and then scale up. This best-practice thinking can help you choose the best answer in situational questions.

Summary

Data Sources are the entry point for external data in ServiceNow. They support CSV and Excel files for flat-file imports and JDBC for direct database connectivity. Combined with Import Sets and Transform Maps, they form a powerful, auditable, and automatable data integration framework. For the CAD exam, focus on the end-to-end import flow, the differences between the three main data source types, the role of the MID Server in JDBC imports, coalesce logic in Transform Maps, and how Scheduled Imports automate the process. Mastering these concepts will prepare you for both exam success and real-world ServiceNow development.