How to upload the excel input to the Mssql DB

Help Studio
, ,
1

Hi All,

I need to upload an excel file to mssql db.
But while doing I am getting an error like “Bulk insert: The provided columns in the DataTable do not match the column data types in the database.”
Please do needful to resolve the issue.

@vishal.kp
@supermanPunch
@ppr
@Yoichi
@ushu
@Gokul001

2

@Vrishchik

As the error suggest the column type or the datatype of the columns in db and the datatype of the datatable column should be similar…check the data…to verify everything…first try with one row and then once you are able to fix all…implement same for all rows and then proceed further.this helps
In isolating the issue

Cheers

1 Like
3

Hi @Vrishchik ,

The error message suggests that there is a mismatch between the columns in your DataTable and the column data types in your database table. To resolve this issue, you can try the following steps:

-Check the data types of the columns in your DataTable and make sure they match the column data types in your database table. If they do not match, you may need to convert the data in your DataTable to match the data types in your database table.

-Make sure that the number of columns in your DataTable matches the number of columns in your database table. If they do not match, you may need to adjust the schema of your DataTable or database table to match.

-Check if there are any null or empty values in the columns of your DataTable. If there are, you may need to handle them before inserting the data into your database.

Regards,

3 Likes
4

Actually the problem with bulk insert activity is not working properly when I used the insert activity then it worked.

5

Hi @Vrishchik
The error message “Bulk insert: The provided columns in the DataTable do not match the column data types in the database” indicates that there is a mismatch between the columns in the DataTable and the column data types in the SQL Server database.

To resolve this issue, you can try the following steps:

  1. Check the column data types in the SQL Server database and ensure that they match the data types of the columns in the DataTable.

  2. Make sure that the column names in the DataTable match the column names in the SQL Server database.

  3. Check if there are any extra columns in the DataTable that are not present in the SQL Server database. If so, remove those columns from the DataTable before uploading it to the database.

  4. Ensure that the order of columns in the DataTable matches the order of columns in the SQL Server database.

  5. Check if there are any null values in the DataTable that cannot be inserted into the SQL Server database. If so, replace those null values with appropriate default values or handle them in some other way.

  6. Try to use SQLBulkCopy class to upload excel data to SQL server.

  7. Lastly, you can also try converting the Excel data to a CSV file and then upload it to the SQL Server database using the BULK INSERT statement.

Hope this helps!