www.hkvforums.com

I have a table to import.
I want to give users an Excel Template file.
Many columns are optional, but if the user doesn't fill them the import process gives an error.
Instead I want that if a field is empty it must be simply considered as NULL.
How to do this?

You can use the Row_Import server event to set the field to null. Read:
https://aspnetmaker.dev/docs/#/customsc ... row_import

Yes I know, but how can I check i f in the excel import file there is an empty cell, and how can I nullify the value to avoid import errors?

We took another approach, if the cell is empty (NULL), in order to avoid NULL errors, we assign String.Empty (or simply"") to the respective cell.

public bool Row_Import(Dictionary<string, object> row, int cnt) {
    //Log(cnt); // Import record count
    //Log(row); // Import row
    //return false; // Return false to skip import

    foreach (KeyValuePair<string, object> pair in row) {
    	if (Empty(Convert.ToString(row[pair.Key]))) {
        	row[pair.Key] = "";
        }
    }

    return true;
}

Another thing, in order to avoid all kind of conversion issues, etc. we always import to a heap table and after the import is successful we calla stored procedure to process data from the heap to the final destination.
Plus you ca write your own logs regarding how many records were successfully imported, apply your own custom validations, etc.

Thank you, this is a useful code. About the approach we too use the same logic of temporary work tables, loading an excel file with multiple optional fields.