如何验证CSV文件数据C＃ [英] How to validate CSV file data C#

问题描述

亲爱的所有人，



我有一个CSV文件，我必须验证数据，csv文件的每一行和每列都是正确的。存储在SQL数据库中。



例如：

CSV文件包含列：FullName，EmailID，DOB，Age。





如果EmailID格式不正确（在第n行中），那么我必须提示用户，在某某行和列，电子邮件ID不正确[在csv]。这样用户可以在该特定行和列中进行更改，然后用户将再次尝试，bcoz将有100行。同样我也需要验证DOB和年龄。



我尝试过：



使用LINQ to CSV，我正在读取CSV文件。

Dear All,

I have an CSV file, where i have to validate the data, that the each row and column of csv file, is correct or not. To store in SQL database.

for example:
CSV file contains columns: FullName, EmailID, DOB, Age.


if EmailID is not in correct format(in nth number of rows), then i have to prompt the user that, in so and so line and column, the emaill-id is not correct[in csv]. So that the user, can do the changes in that particular row and column and then the user will try again, bcoz there will 100's of rows. Similarly i have to verify for DOB and Age, also.

What I have tried:

Using LINQ to CSV, i'm reading CSV file.

public List<ImportCandidates> ImportCandidatesFromExcel()
        {
            List<ImportCandidates> regCandidates = new List<ImportCandidates> { };
            if (fuExcelfile.HasFile || hdnfilepath.Value != "")
            {
                string csvFilePath = "";

                if (fuExcelfile.HasFile)
                {
                    csvFilePath = "" + @"" + RepositoryHelper.
                        SaveDocument(DocumentType.CandidateImport, fuExcelfile.PostedFile); //Excel file path
                    hdnfilepath.Value = csvFilePath;
                }
                else
                {
                    csvFilePath = hdnfilepath.Value.ToString();
                }

                CsvFileDescription inputFileDescription = new CsvFileDescription
                {
                    SeparatorChar = ',',
                    FirstLineHasColumnNames = true
                };

                CsvContext cc = new CsvContext();
                IEnumerable<ImportCandidates> importCandidates;
                try
                {
                    importCandidates= cc.Read<ImportCandidates>(csvFilePath, inputFileDescription);
                    int i = importCandidates.Count();
                }
                catch (Exception)
                {
                    inputFileDescription = new CsvFileDescription
                            {
                                SeparatorChar = ';',
                                FirstLineHasColumnNames = true
                            };
                    importCandidates= cc.Read<ImportCandidates>(csvFilePath, inputFileDescription);

                }                 

                if (importCandidates.Count() > 0)
                {
                    foreach (var i in importCandidates)
                    {
                        ImportCandidates icandidate = new ImportCandidates
                        {
                            PersonalID = i.PersonalID,
                            FirstNameAR = i.FirstNameAR,
                            FirstNameEN = i.FirstNameEN,
                            LastNameAR = i.LastNameAR,
                            LastNameEN = i.LastNameEN,
                            DateOfBirth = Convert.ToDateTime(i.DateOfBirth),
                            Address = i.Address,
                            Email = i.Email,
                            UserName = i.UserName,
                            ContactNumber = i.ContactNumber,
                            Country = i.Country,
                            Gender = i.Gender,
                            OtherDetails = i.OtherDetails
                        };
                        regCandidates.Add(icandidate); ;
                    }
                }
            }
            return regCandidates;
        }

任何人都可以帮助我。





提前致谢。

Can any one please help me.


Thanks in advance.

推荐答案

我用它来导入CSV： GitHub - kentcb / KBCsv：KBCsv是一个高效，易用的.NET解析和写入库，用于CSV（逗号分隔值）格式。 [ ^ ]



要验证电子邮件地址，请选择最适合您的需求： c＃validate email address - Google Search [ ^ ] - 这个对我来说非常好看：正则表达式 - C＃中电子邮件验证的最佳正则表达式 - Stack Overflow [ ^ ]

I use this for importing CSVs: GitHub - kentcb/KBCsv: KBCsv is an efficient, easy to use .NET parsing and writing library for the CSV (comma-separated values) format.[^]

And to validate email addresses, pick the best one that suits your needs: c# validate email address - Google Search[^] - this one looks pretty good to me: regex - Best Regular Expression for Email Validation in C# - Stack Overflow[^]

您好，

最近我制作了一个导入CSV文件的程序，它包含错误检查，规范化和其他内容。您可以根据自己的需要调整此代码，导入的CSV列在Order - >类中描述。方法：InitializeFieldDesc（）。

Hello,
Recently I've make a program to import a CSV file, it's contains error checks, normalizations and another stuffs. You can adapt this code for your purposes, the imported CSV columns are described in class Order -> method: InitializeFieldDesc().

using System;
using System.IO;
using System.Collections.Generic;

public class ImportCSV
{
    List<Order> orders;             // Valid order list 
    List<Order> invalidOrders;      // Invalid order list 
    Logger logger;                  // Logger for info and errors

    /// <summary>
    /// Initialize object and load file data
    /// </summary>
    public ImportCSV(string filePath)
    {
        logger = new Logger();
        orders = new List<Order>();
        invalidOrders = new List<Order>();

        LoadOrders(filePath);
    }

    #region Load orders

    /// <summary>
    /// Load orders from file
    /// </summary>
    /// <param name="filePath"></param>
    /// <returns></returns>
    private void LoadOrders(string filePath)
    {
        // Check if file exists
        if (File.Exists(filePath))
        {
            GetOrdersFromFile(filePath);
        }
        else
        {
            // File not exits
            throw new FileNotFoundException("File not found");
        }
    }

    /// <summary>
    /// Get orders from file and insert in List
    /// </summary>
    /// <param name="filePath"></param>
    private void GetOrdersFromFile(string filePath)
    {
        // set active order in logger
        logger.setActiveOrder(-1);
        logger.AddInfo("Opening file: " + filePath);
        try
        {
            string[] lines = File.ReadAllLines(filePath);

            // insert one order per line
            for (int i = 0; i < lines.Length; i++)
            {
                // logger active order id
                logger.setActiveOrder(i);

                // Initialize order class with field desc
                Order tempOrder = new Order(ref logger);

                logger.AddInfo("Mapping a string into order's object. Line nº: " + i.ToString());

                // Mapping a string into order's object
                tempOrder.LoadOrder(i, lines[i]);

                if (!tempOrder.hasErrors)
                {
                    // Add valid order
                    orders.Add(tempOrder);
                }
                else
                {
                    // Add invalid order
                    invalidOrders.Add(tempOrder);
                    logger.AddInfo("Invalid data detection in line nº: " + i.ToString());
                }
            }
        }
        catch (Exception exc)
        {
            // set active order in logger
            logger.setActiveOrder(-1);
            logger.AddException(exc);
        }

        // set active order in logger
        logger.setActiveOrder(-1);
        logger.AddInfo(string.Format("Ends of orders load process. Valid orders: {0}. Invalid orders: {1}.",
            orders.Count.ToString(), invalidOrders.Count.ToString()));
    }

    #endregion

    #region Public properties get valid & invalid orders

    /// <summary>
    /// Get valid orders
    /// </summary>
    public List<Order> getValidOrders
    {
        get { return orders; }
    }
    /// <summary>
    /// Get invalids orders (data corrupted or string malformed)
    /// </summary>
    public List<Order> getInvalidOrders
    {
        get { return invalidOrders; }
    }

    #endregion

    #region Manage Order 

    /// <summary>
    /// Field metadata
    /// </summary>
    public class OrderField
    {
        public string fieldName;                    // name of the field
        public DataCheck.ChecksTypes checkType;     // cheks type that will pass
        public System.Type dataType;                // Data type
        public bool mandatory;                      // Required data 
        public object value = null;                 // Object value
    }

    /// <summary>
    /// Manage order
    /// </summary>
    public class Order
    {
        private Logger logger;              // Logger var
        List<OrderField> fieldDesc;         // List of order's fields
        private DataCheck check;            // Checking class

        /// <summary>
        /// Initialize an order with fieldDesc class injection
        /// </summary>
        /// <param name="_logger"></param>
        /// <param name="_fieldDesc"></param>
        public Order(ref Logger _logger)
        {
            logger = _logger;
            check = new DataCheck(ref logger);

            // Load fields 
            InitializefieldDesc();
        }

        // todo: automatize this description load
        /// <summary>
        /// Initialize fields description manually 
        /// </summary>
        private void InitializefieldDesc()
        {
            fieldDesc = new List<OrderField>();

            fieldDesc.Add(new OrderField()
            {
                fieldName = "OrderId",
                checkType = DataCheck.ChecksTypes.PositiveInteger,
                dataType = typeof(Int32),
                mandatory = true
            });

            fieldDesc.Add(new OrderField()
            {
                fieldName = "DealId",
                checkType = DataCheck.ChecksTypes.PositiveInteger,
                dataType = typeof(Int32),
                mandatory = true
            });

            fieldDesc.Add(new OrderField()
            {
                fieldName = "Email",
                checkType = DataCheck.ChecksTypes.Email,
                dataType = typeof(Int32),
                mandatory = true
            });

            fieldDesc.Add(new OrderField()
            {
                fieldName = "Street",
                checkType = DataCheck.ChecksTypes.Street,
                dataType = typeof(Int32),
                mandatory = true
            });

            fieldDesc.Add(new OrderField()
            {
                fieldName = "City",
                checkType = DataCheck.ChecksTypes.String,
                dataType = typeof(Int32),
                mandatory = true
            });

            fieldDesc.Add(new OrderField()
            {
                fieldName = "State",
                checkType = DataCheck.ChecksTypes.State,
                dataType = typeof(Int32),
                mandatory = true
            });

            fieldDesc.Add(new OrderField()
            {
                fieldName = "ZipCode",
                checkType = DataCheck.ChecksTypes.NO_CHECK,
                dataType = typeof(Int32),
                mandatory = true
            });

            fieldDesc.Add(new OrderField()
            {
                fieldName = "CreditCard",
                checkType = DataCheck.ChecksTypes.NO_CHECK,
                dataType = typeof(Int32),
                mandatory = true
            });
        }

        /// <summary>
        /// Map order from string, performs check and normalizations 
        /// </summary>
        /// <param name="idOrder"></param>
        /// <param name="orderLine"></param>
        public void LoadOrder(int idOrder, string orderLine)
        {
            string[] orderParts = orderLine.Split(',');

            if (orderParts.Length != fieldDesc.Count)
            {
                logger.AddInvalidDataError("Order line malformed, item counts: " + orderParts.Length);
            }
            else
            {
                // Check all data, to know all the errors at the end of the mapping process 
                for (int i = 0; i < fieldDesc.Count; i++)
                {
                    check.FieldDataCheck(fieldDesc[i], orderParts[i]);
                }
            }
        }

        #region Public properties: field mapping by name

        // Map fields by name

        public int OrderId { get { return (int)fieldDesc[0].value; } }

        public int DealId { get { return (int)fieldDesc[1].value; } }

        public string Email { get { return (string)fieldDesc[2].value; } }

        public string Street { get { return (string)fieldDesc[3].value; } }

        public string City { get { return (string)fieldDesc[4].value; } }

        public string State { get { return (string)fieldDesc[5].value; } }

        public string ZipCode { get { return (string)fieldDesc[6].value; } }

        public string CreditCard { get { return (string)fieldDesc[7].value; } }

        #endregion

        /// <summary>
        /// Return if there is invalid data in 
        /// </summary>
        public bool hasErrors
        {
            get { return logger.thereisInvalidData; }
        }
    }

    #endregion

    #region Utilities

    /// <summary>
    /// Logger class for log & exceptions 
    /// </summary>
    public class Logger
    {
        internal enum LogType
        {
            INVALID_DATA,
            EXCEPTION,
            INFO
        }           // Log's record type

        // Log item struct: type and message
        internal struct LogItem
        {
            public LogType logType;
            public string message;
        }

        // Log dictionary to facilite access by orderId
        private Dictionary<int, List<LogItem>> Log;

        // active orderID
        private int activeOrderId;

        /// <summary>
        /// Initialize logger
        /// </summary>
        public Logger()
        {
            Log = new Dictionary<int, List<LogItem>>();
        }

        /// <summary>
        /// Get order log by id (create if not exist)
        /// </summary>
        /// <param name="orderId"></param>
        /// <returns></returns>
        internal List<LogItem> getOrderLog(int orderId)
        {
            if (!Log.ContainsKey(activeOrderId))
                Log.Add(activeOrderId, new List<LogItem>());

            return Log[activeOrderId];
        }

        /// <summary>
        /// Set active order by orderId
        /// </summary>
        /// <param name="orderId"></param>
        internal void setActiveOrder(int orderId)
        {
            activeOrderId = orderId;
        }

        /// <summary>
        /// Add info to log
        /// </summary>
        /// <param name="orderId"></param>
        /// <param name="message"></param>
        public void AddInfo(string message)
        {
            getOrderLog(activeOrderId).Add(new LogItem()
            {
                logType = LogType.INFO,
                message = message
            });
        }

        /// <summary>
        /// Add exception to log
        /// </summary>
        /// <param name="orderId"></param>
        /// <param name="exception"></param>
        public void AddException(Exception exception)
        {
            getOrderLog(activeOrderId).Add(new LogItem()
            {
                logType = LogType.EXCEPTION,
                message = exception.Message
            });
        }

        /// <summary>
        /// Add invalid data error to log
        /// </summary>
        /// <param name="orderId"></param>
        /// <param name="message"></param>
        public void AddInvalidDataError(string message)
        {
            getOrderLog(activeOrderId).Add(new LogItem()
            {
                logType = LogType.INVALID_DATA,
                message = message
            });
        }

        /// <summary>
        /// Check if there is exceptions in order
        /// </summary>
        /// <param name="orderId"></param>
        /// <returns></returns>
        public bool thereisExceptions
        {
            get
            {
                return getOrderLog(activeOrderId).Exists(i => i.logType == LogType.EXCEPTION);
            }
        }

        /// <summary>
        /// Check if thereis invalid data in order
        /// </summary>
        /// <param name="orderId"></param>
        /// <returns></returns>
        public bool thereisInvalidData
        {
            get
            {
                return getOrderLog(activeOrderId).Exists(i => i.logType == LogType.INVALID_DATA);
            }
        }

    }

    /// <summary>
    /// Utility class for checks and normalize data
    /// Contains a batery of check functions
    /// </summary>
    public class DataCheck
    {
        /// <summary>
        /// List of avaiable checks
        /// </summary>
        public enum ChecksTypes
        {
            String,
            PositiveInteger,
            Email,
            Street,
            State,
            NO_CHECK
        }

        Normalize normalize;        // Normalize 
        Logger logger;              // logger 

        /// <summary>
        /// Initialize data check
        /// </summary>
        /// <param name="_logger"></param>
        public DataCheck(ref Logger _logger)
        {
            logger = _logger;
            normalize = new Normalize();
        }

        /// <summary>
        /// Check data generic function based on OrderfieldDesc
        /// </summary>
        /// <param name="datafield"></param>
        /// <param name="value"></param>
        public void FieldDataCheck(OrderField datafield, string value)
        {
            if (datafield.checkType == ChecksTypes.PositiveInteger)
            {
                PositiveInteger(datafield, value);
            }
            else if (datafield.checkType == ChecksTypes.Email)
            {
                Email(datafield, value);
            }
            else if (datafield.checkType == ChecksTypes.State)
            {
                State(datafield, value);
            }
            else if (datafield.checkType == ChecksTypes.Street)
            {
                Street(datafield, value);
            }
            else if (datafield.checkType == ChecksTypes.String)
            {
                ChkString(datafield, value);
            }
            else
            {
                // Set value without check
                datafield.value = value;
            }
        }

        #region Check functions

        /// <summary>
        /// Check if positive integer
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        private void PositiveInteger(OrderField dataField, string value)
        {
            int temp = 0;
            if (!int.TryParse(value, out temp))
            {
                logger.AddInvalidDataError(string.Format("Invalid {0}: is not an integer", dataField.fieldName.ToString()));
            }
            else if (temp < 0)
            {
                logger.AddInvalidDataError(string.Format("Invalid {0}: is not positive integer", dataField.fieldName.ToString()));
            }
            else
            {
                dataField.value = temp;
            }
        }

        /// <summary>
        /// Generic String check
        /// </summary>
        /// <param name="dataField"></param>
        /// <param name="value"></param>
        private void ChkString(OrderField dataField, string value)
        {
            value = value.Trim().ToLower();
            if ((dataField.mandatory) && (value == ""))
            {
                logger.AddInvalidDataError(string.Format("Invalid {0}: empty string", dataField.fieldName.ToString()));
            }
            else
            {
                dataField.value = value;
            }
        }

        /// <summary>
        /// Check email format _____@___.__ and normalize
        /// </summary>
        /// <param name="Email"></param>
        /// <returns></returns>
        private void Email(OrderField dataField, string Email)
        {
            // Check basic string 
            ChkString(dataField, Email);
            bool invalidFormat = (dataField.value.ToString() == "");

            // Check email contains @
            if (!invalidFormat)
            {
                Email = dataField.value.ToString();
                invalidFormat = (Email.IndexOf("@") <= 0);
            }

            // Check email contains only one @
            if (!invalidFormat)
            {
                invalidFormat = (Email.Split(new char[] { '@' }).Length != 2);
            }

            // Check email contains @ before a "."
            if (!invalidFormat)
            {
                invalidFormat = (Email.LastIndexOf(".") < Email.IndexOf("@"));
            }

            // Save output value if not invalid
            if (!invalidFormat)
            {
                dataField.value = normalize.Email(Email);
            }
            else
            {
                logger.AddInvalidDataError("Invalid email format");
            }
        }

        /// <summary>
        /// Check and normalize State
        /// </summary>
        /// <param name="dataField"></param>
        /// <param name="value"></param>
        private void State(OrderField dataField, string value)
        {
            ChkString(dataField, value);
            if (dataField.value.ToString() != "")
                dataField.value = normalize.State(dataField.value.ToString());
        }

        /// <summary>
        /// Check and normalize Street
        /// </summary>
        /// <param name="dataField"></param>
        /// <param name="value"></param>
        private void Street(OrderField dataField, string value)
        {
            ChkString(dataField, value);
            if (dataField.value.ToString() != "")
                dataField.value = normalize.Street(dataField.value.ToString());
        }

        #endregion

        /// <summary>
        /// Get data errors
        /// </summary>
        public bool ThereIsErrors
        {
            get { return logger.thereisInvalidData; }
        }
    }

    /// <summary>
    /// Utility class for normalize
    /// </summary>
    public class Normalize
    {
        /// <summary>
        /// Normalize email, format required ___@___.__ (previously checked)
        /// </summary>
        /// <param name="Email"></param>
        /// <returns></returns>
        public string Email(string Email)
        {
            var aux = Email.Split(new char[] { '@' }, StringSplitOptions.RemoveEmptyEntries);

            if (aux.Length > 1)
            {
                var atIndex = aux[0].IndexOf("+", StringComparison.Ordinal);

                aux[0] = atIndex < 0 ? aux[0].Replace(".", "") : aux[0].Replace(".", "").Remove(atIndex);

                return string.Join("@", new string[] { aux[0], aux[1] });
            }
            else return aux[0];
        }
        /// <summary>
        /// Normalize street
        /// </summary>
        public string Street(string Street)
        {
            Dictionary<string, string> replaces = new Dictionary<string, string>();
            replaces.Add("st.", "street");
            replaces.Add("rd.", "road");

            foreach (string replace in replaces.Keys)
            {
                Street = Street.Replace(replace, replaces[replace]);
            }

            return Street;
        }
        /// <summary>
        /// Normalize state
        /// </summary>
        /// <param name="State"></param>
        /// <returns></returns>
        public string State(string State)
        {
            Dictionary<string, string> replaces = new Dictionary<string, string>();
            replaces.Add("il", "illinois");
            replaces.Add("ca", "california");
            replaces.Add("ny", "new york");

            foreach (string replace in replaces.Keys)
            {
                State = State.Replace(replace, replaces[replace]);
            }

            return State;
        }
    }

    #endregion
}

这篇关于如何验证CSV文件数据C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！