I am in a automation where i am downloading csv file and inserting it in my sql ( reading csv and then run query,inserting data row by row) csv having almost 1.15 lakh rows. but problem is that some rows are missing in sql data base. what can be the reason?
- Duplicate Rows: If the CSV contains duplicate rows and your database has constraints (e.g., unique constraints), these rows might be skipped.
- Invalid Data: Rows with invalid data that don’t match the table schema might be rejected.
Solution: Validate the CSV data before insertion to ensure it meets all constraints and data types of the database schema.
Try Using Bulk insert activity.
Hi @Hemant_Deshmukh ,
CSV File Reading Issues:
-
Issue: The CSV file might not be read completely due to incorrect file paths or encoding issues.
-
Solution: Ensure that the CSV file is read correctly by using the “Read CSV” activity with the appropriate encoding settings.
Data Validation Errors:
- Issue: Some rows might contain invalid data that causes SQL insertions to fail.
- Solution: Add data validation and error handling to skip or log invalid rows.
SQL Connection Timeouts:
- Issue: Long-running operations might cause SQL connection timeouts.
- Solution: Increase the SQL connection timeout settings.
Primary Key Violations:
- Issue: Duplicate rows or primary key violations might prevent some rows from being inserted.
- Solution: Ensure that there are no duplicate entries and that the primary keys are unique.
Regards
Sandy
- First did you check if all data is read in csv by doing rowcount?
- Are there any filters?
- Contraints on database table also can effect
- Why not use bulk insert?
Cheers
I tried to use bulk insert but it was giving error that your drivers not support to bulk insert in mysql. ।
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.