Importing Excel files into SaaS products requires a structured approach to ensure data integrity. Instead of direct processing, it is recommended to treat imports as a reconciliation process between external data and the current system state, using staging tables to validate content before applying changes.
For Go-based applications, the Excelize library is suitable for reading files. Using the Rows iterator is preferred over loading entire sheets into memory for large files. Data should be loaded into a staging table, such as catalog_import_rows, which links rows to a specific import ID and line number for easier error tracking.
Performance and consistency are critical when handling large datasets. Using pgx.CopyFrom allows efficient data transfer to PostgreSQL. Before final application, developers should perform validation, such as checking for duplicate codes, which PostgreSQL's ON CONFLICT clause cannot handle if multiple conflicting rows exist in the same batch.
To prevent data corruption, implement a versioning system for catalogs. By tracking a revision number and using SELECT FOR UPDATE, the system ensures that changes are applied only if the catalog state has not been modified by other processes since the preview was generated.
A final step involves generating a diff to show users the impact of the import, such as the number of new, updated, or deactivated items. For high-risk operations, such as mass deactivation, implementing thresholds for manual confirmation helps prevent accidental data loss.
