I Have A Huge Csv File Now I Want To Store Data Of Csv File In Sql Database With Applying Validation On It Like "Row Value Datatype Mismatch With Columns"

Question

0.00/5 (No votes)

See more:

, +

In this we add rows and column from .csv file to Data Table and want to apply validation that If any mismatch datatype is found in rows of any columns then messages Displayed which line and column have mismatched datatype..

C#

protected void InsertDataIntoSql(string csvfile)
       {
           string [] tempLines = File.ReadAllLines(Server.MapPath("../Documents/" + csvfile));
           DataTable dt=new DataTable();
           string []  colname = tempLines[0].Split(',');

           foreach (string t in colname)
           {
               dt.Columns.Add(t);
           }
           for (int i = 1; i < tempLines.Length; i++)
           {
               string[] line = tempLines[i].Split(',');
               dt.Rows.Add(line);
           }

Posted 2-Sep-14 3:39am

Member 11034643

Add a Solution

Comments

ZurdoDev 2-Sep-14 9:49am

Do you have a question?

Richard MacCutchan 2-Sep-14 10:27am

You need to add some code which checks all the fields before you add each line, and verifies it against some list of rules.

ChauhanAjay 2-Sep-14 13:22pm

what kind of validations are you looking at?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

George Jonsson · Accepted Answer · 2014-09-02T16:53:00

You need to specify the data type of each column and check data against that.

This is one way of many for how you can do it. I hope it can help you on the way.

C#

dt.Columns.Add(t, typeof(int));
dt.Columns.Add(t, typeof(double)); 
etc.

As you are doing this in a loop you need an array with the matching data types or do it manually inside the loop.

C#

for (int i=0; i<colname.length;>{
    if (i == 0)      // Column 1 is of type int
        dt.Columns.Add(colname[i], typeof(int));
    else if (i == 1)      // Column 2 is of type double
        dt.Columns.Add(colname[i], typeof(double));
    // and so on
}

Not the most elegant way and if the number of columns change or their types, then you have to change the code.
The best option is to get the data type from the document, but without any knowledge of the structure of the CSV file I cannot suggest anything else.

The point is that if you have set the data type of each column you can use it to validate the data.

C#

int line = 0;
int col = 0;
try
{
    for (line = 1; line < tempLines.Length; line++)
    {
       string[] columns = tempLines[line].Split(',')
       for (col=0; col < columns.Length; col++)
       {
           // Exception will occur here if there is a mismatch
           object val = Convert.ChangeType(columns[col], dt.Columns[col].DataType);
           dt.Rows.Add(val);
       }
    }
}
catch (Exception ex);
{
    throw new Exception(String.Format("Error at line {0}, col {1}, {2}", line, col, ex.Message);
}