Validation of Excel file data before importing to Database

Question

I'm trying to read data from an Excel file to a DataTable. I know that we can assign different DataTypes for a DataColumn when adding columns to a DataTable. So far I have tried the following,

DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[2]
{
    new Datacolumn("column1", typeof(string)),
    new DataColumn("column2", typeof(int))
});

And my excel file has data in two columns as follows,

column1------column2

abc----------------230

def----------------230tr

As you can see above, the second row second column has a value of '230tr' which the DataTable should not accept an throw an exception with the invalid data field and row number.

Praveena.

What about strings that contain numbers? Say WD40? What would you want entered in that instance? — Jon P, Nov 01 '18 at 05:22
Hi Jon, 'WD40' as a string would be fine. But a numeric value as a whole should not be allowed and throw exception. — Praveena Goonasekera, Nov 01 '18 at 05:31
you can use a regular expression to filter the data. Add a function in which you pass the column value and return true/false and based on return just contiune or espace that column. see the link -https://stackoverflow.com/questions/1181419/verifying-that-a-string-contains-only-letters-in-c-sharp — Ajay2707, Nov 01 '18 at 06:00
could u plz show the code that how can u add the above value to datatable columns? — er-sho, Nov 01 '18 at 06:32
Hi Ajay, Using regular expressions is one solution. But I found out that the validation can be done from DataTable itself. Thanks. — Praveena Goonasekera, Nov 02 '18 at 04:42

jubair hridoy · Answer 1 · 2018-11-01T08:31:10.913

you can try in that way.

      DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[2]
        {
          new DataColumn("column1", typeof(int)),
          new DataColumn("column2", typeof(string))
        });

        dt.Clear();
        try
        {
            string input = string.Empty;
            input = Console.ReadLine();
            if (Regex.IsMatch(input, @"^[a-zA-Z]+$"))
            {
                dt.Rows.Add(1, input);
            }
            Console.WriteLine(dt.Rows[0]["column2"]);
            Console.ReadKey();
        }
        catch(Exception ex)
        {
            Console.WriteLine(ex);
            Console.ReadKey();
        }

DaniDev · Answer 2 · 2018-11-01T05:44:11.770

0

In your code, where you are reading the excel values to insert, you should employ the C# function: [Int32.TryParse][1] or float.TryParse;

which will tell you whether the string will convert to a float (or integer) in which case don't insert it.

You can see a detailed example/answer here:

edited Nov 01 '18 at 05:44

answered Nov 01 '18 at 05:36

DaniDev

2,471
2
22
29

Hi DaniDev, Thankyou for the response. I'm using the below implementation when adding rows to the `DataTable`. `using (OleDBDataAdapter oda = new OleDBDataAdapter("SELECT * FROM [excel_sheet]", excel_con)) { oda.Fill(dt); }` And then I'm using 'OracleBulkCopy' to add the data in `DataTable` to my database at once. So I would need to do that validation when adding data to the `DataTable`. How can I achieve that? – Praveena Goonasekera Nov 01 '18 at 05:41
Read your data into a temporary data structure such as a dictionary. hen use the validation. – DaniDev Nov 01 '18 at 05:49
or just use the existing DataTable dt and perform a linq select to eliminate the numeric values – DaniDev Nov 01 '18 at 05:52

score 0 · Accepted Answer · answered Nov 02 '18 at 04:38

Thank you everyone for their response and I found an easy solution for my question while working with DataTables. My main task was to read data rows from an Excel file sheet and validate each row data before adding it to my database table(using OracleBulkCopy) and notify the user with any invalid data fields in the Excel file sheet.

While I was importing data into the DataTable from the Excel file sheet using oda.Fill(dt); I realized that some of the DataRow fields are empty(null) during the process of data addition to DataTable.

As I have mentioned above in the main question we can assign the DataType for a DataColumn in a DataTable as follows,

DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3]
{
    new Datacolumn("column1", typeof(string)),
    new DataColumn("column2", typeof(double)),
    new DataColumn("column3", typeof(DateTime))
});

And my Excel file sheet would be as follows,

column1-----------------column2----------------------column3

abcd----------------------20000-------------------------20-Dec-2018

efgh-----------------------56500.5----------------------xyz17-Mar-2018

ijklm-----------------------67000------------------------1-Jan-2018

In the above table 3rd row 3rd column RowData is in invalid date type (xyz17-Mar-2018). While performing oda.Fill(dt); for the above table, this data field gets added as a null RowData to the DataTable. It would be the same for other columns if the importing data fields are not in the type of defined DataColumn type. So My final step was to check each row data of DataTable which has a null field and throw the user with an error message mentioning the row number and column name.

int errorRowNumber = 0;

private bool validateDataTableData(DataTable dt, string dtColumnName)
{
    Boolean isTrue = false;

    foreach (DataRow r in dt.Rows)
    {
          if(!string.IsNullOrEmpty(r[dtColumnName].ToString()))
          {
                isTrue = false;
                errorRowNumber = dt.Rows.IndexOf(r) + 2;
                break;
          }
    }
    return isTrue;
}

1. I dont see and exception or error message being thrown? 2. How are you removing numeric values as per your original post? 3. your 'isTrue' is always false. — DaniDev, Nov 05 '18 at 21:41

Validation of Excel file data before importing to Database

3 Answers3