I'm working on a major control library cleanup and need to update over a thousand control statements in our GRC instance. The UI isn't practical for this volume.
I've used the CSV import before for smaller lists, but I'm concerned about validation errors or data corruption at this scale. Specifically:
* What's the best practice for preparing the CSV file to avoid formatting issues?
* Are there specific fields, like the sys_id of the control, that are mandatory for the update to work correctly?
* Has anyone run into workflow or audit log complications when updating this many records at once?
That's a big job! I did a similar update for 500+ vendor records. For your CSV prep, make sure you export a few controls first and use that exact column order in your update file. It saved me from weird formatting issues.
And yeah, sys_id is mandatory for updates, but double-check you've got the right sys_id for each row. I once mixed up two exports and updated the wrong records. The audit log was a mess to untangle 😅
Did you test it on a small batch first, like 5 records, to see if any workflows trigger?
Totally agree about matching the column order from an export. I'd go a step further and recommend stripping that file down to *only* the columns you're actually updating, plus the sys_id. It reduces the chance of accidentally changing a field you meant to leave alone.
Your audit log point is spot on. For controls, it's not just workflows you need to watch for. Some compliance frameworks require a documented change reason for each control modification. Bulk updates can sometimes blur those entries together, so it's worth checking if your process logs a single reason for the whole import or one per record.
Trust the data, not the demo.
sys_id is non-negotiable for an update operation, but there's a critical nuance. The import tool will use the first column it identifies as a unique key, so if your sys_id column isn't first, you risk it defaulting to 'number' and creating duplicates. Always structure your CSV with sys_id as column A.
On validation, the most common corruption I see isn't from formatting, but from character encoding in the control statement text itself. You should run your CSV through a plain text editor to check for smart quotes, em-dashes, or non-breaking spaces that the system will choke on.
For audit logs, the primary risk is a single "CSV Import" entry obscuring individual record changes, which is a problem for granular audit trails. Test a batch of 10 records first and immediately check the sys_audit table for a specific control to see how the actions are logged. If they're aggregated, you'll need to script this instead to preserve a change reason per record.
p-value < 0.05 or bust
Agreed on the sys_id placement. I'll add that if you're using a script to generate your update file, explicitly set the `sys_id` column as the first output. Most CSV parsers respect column order, but I've seen BI tools that alphabetize on export, which can break the import.
For character encoding, run a pre-check with this SQL on your staging data: `SELECT * FROM stg_controls WHERE statement_text ~ '[\x80-\xFF]';`. That catches non-ASCII characters before they hit the CSV.
On audit logs, test with 20 records and compare the `sys_audit` entries against the UI history. Some configurations log the entire import as a single transaction, which can collapse individual change reasons into one entry - a real problem for compliance audits.
The sys_id column must be first, but you also need to confirm the import tool's *key detection mode*. On some configurations, it can default to "number" even with sys_id present, which will create a thousand new controls instead of updating them.
Validate character encoding on a staging table first. Use the SQL check user109 posted.
For audit logs, you're right to worry. A test batch of 10-20 will confirm if your changes log individually or as a single transaction. If it's a single entry, you'll need to adjust the import configuration or log each change separately with a script.
Five nines? Prove it.
Good points already mentioned on column order and character encoding. I'd like to ask about a specific preparation step: when you run your pre-import validation, are you checking for mandatory fields beyond sys_id that might be unique to controls? For example, something like a 'control_category' or 'owner' field that could cause the update to silently skip rows if they're missing? I've seen imports fail partially that way.
Also, on the audit log question, does anyone know if the behavior differs between using the native CSV import tool versus a scripted REST API approach for the same bulk update? I'm curious if one gives you a clearer per-record audit trail than the other.
Great question. The others are right about sys_id and encoding, but I'd add that the field validation rules for updates are different than for inserts. If you're updating statements, fields marked "mandatory" in the UI don't always need to be in your CSV for the update to succeed, as existing values are retained. Missing a truly mandatory field will cause the row to fail silently, as user1235 hinted.
On workflow and audit complications, the main issue is transaction grouping. The CSV import often logs as a single action. If you need granular proof of each change for an audit, you might need to split the import into smaller chunks or use a script that iterates with individual API calls. The native tool is efficient but opaque.
—AF
That's a really important distinction about field validation for updates versus inserts, and it hits on a core anxiety I have with bulk operations. The idea of a "silent skip" is concerning. Is there a reliable way to generate a post-import report that specifically flags which rows were not processed, or is the only indication a mismatch between the number you attempted and the number the system says it updated?
Your point about transaction grouping clarifies my audit log worry. If the import logs as a single action, does that mean the `sys_audit` table will show one entry with a list of changed records, or does it literally just log "CSV Import" with no detail on which controls were touched? I'm trying to understand if the granular data is stored somewhere inaccessible or if it's truly lost.
The SQL check is a good catch, but what about users who don't have direct database access for a staging table? We're stuck with the import tool's preview.
Your audit log test is the most useful tip so far. A single transaction entry would be a deal-breaker for our SOX compliance. Do you know if the log groups by the *entire* file or by the import session? If it's by session, splitting the CSV into smaller files might not even help.
Test batch of five is a good start, but it won't catch rate limiting or concurrency issues at 1,000 rows. I once saw an import hang after 200 updates because of a background script lock.
Your formatting advice works, but column order from an export can still break if the source table's schema changes between your export and import. Always verify the field map in the import preview, even with a "template" CSV.
Mixing up sys_ids isn't just an audit log problem - it can corrupt relationship data if your controls link to other tables. A wrong update can cascade.
show the math
All that formatting advice is pointless if you don't first run a test import with a subset that *fails*. Simulate missing sys_ids, bad encoding. See what the error report actually looks like. The system's failure mode is your only real spec.
And don't trust the "successful rows" count. Spot-check by querying for your test data with a known unique string after the import. The log might say it updated, but the data could be wrong.
—EB