How to Do Data Validation for Data Upload C# Mvc Using Jquery

By:   |   Updated: 2015-09-03   |   Comments (4)   |   Related: More > Import and Export

Problem

As a SQL Server Business Intelligence Professional, I work with numerous large Excel files every day. I demand to either submit files to others or upload the files to my SQL Server information warehousing system. To ensure the quality of my SQL Server data, I desire to do Excel file data validation of the data in these files per business requirements earlier I even start to process them. What can I do?

Solution

To validate data, we tin rely on Regular Expression for Excel files. One common way to procedure Excel files is to apply the COM Interop interface. In PowerShell, that code is like the following:

$ExcelApp = new-object -com Excel.Application; #and then you tin handle Excel files using this $ExcelApp          

Withal, this approach requires that the Excel application be installed. In many server environments, Microsoft Office is not installed. On the other paw, the operation of this COM approach is unremarkably slow when treatment big Excel files and is not supported:

Microsoft does not currently recommend, and does non support, Automation of Microsoft Office applications from whatsoever unattended, non-interactive client application or component (including ASP, ASP.Internet, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Function is run in this environment.

So in this tip, I will employ a community-tested and approved approach that does not rely on an Excel installation to piece of work with Excel files. This approach relies on an open-sourced DLL file, EPPlus.dll.� Later on downloading the zip file and extracting the EPPlus.dll to a folder, say C:\Tools\EPPlus.dll, right-click the file and from the pop-upward menu click backdrop and then Unblock it as shown below:

This solution has two central points:

• Use EPPlus.dll to read Excel files
• Use Regular Expression to perform validations on the cells of interest

PowerShell Source Code for Excel File Data Validation

Below is the PowerShell source code to be used for the Excel file data validation.

Example

I ran the post-obit examination:

Step 1

Generate examination data, I used AdventureWorks2012 to dump data out every bit csv file.

# generate exam data import-module sqlps -DisableNameChecking gear up-location c:[string]$qry =@" select p.Name ,SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, s.ProductID, s.UnitPrice, due south.LineTotal, s.ModifiedDate from sales.salesorderdetail s inner join Production.Product p on p.ProductID =s.ProductID; "@ # tp_w520 is my laptop name, change it to your own invoke-sqlcmd -server tp_w520 -database AdventureWorks2012 -query $qry | Consign-Csv -Path c:\temp\Test.csv -Force -NoTypeInformation;          

Step 2

The test data has 120,000+ rows. I used Excel 2013 to open the c:\temp\Exam.csv file and modified the last three lines as shown beneath to make the information aberrant and then I saved the modified file as c:\temp\Test.xlsx

• 1st change: [SalesOrderID] should be a number(RegEx check='^\d+$'), but I add a letter of the alphabet "A" in cell (121318, B) to make information technology invalid
• 2nd alter: [ProductID} should exist a number(RegEx check='^\d+$'), simply I add a letter "A" in prison cell (121317, F) to make it invalid
• tertiary change: [ModifiedDate] should be a engagement every bit MM/DD/YYYY((RegEx check='^\d{1,2}/\d{i,2}/\d{four}$'), just I made cell (121316, I) to be 7/131/2008 to make it an invalid data.

Stride 3

Open the PowerShell command line window (ISE is OK besides) and run the following 2 lines of code

. c:\temp\validate-ExcelData.ps1 #dot source the function script, which is saved in c:\temp\ folder Validate-ExcelData -ExcelFile C:\temp\exam.xlsx -Condition @{SalesOrderID='^\d+$'; ProductID='^\d+$'; ModifiedDate='^\d{ane,two}/\d{1,2}/\d{four}$'}          

The result is shown beneath:

Summary

This tip suggests a solution to validate Excel data via PowerShell and Regular Expressions.� The operation is pretty good, for the example above it took less than 15 seconds to run on my 5 year old laptop (Win 7, 8GB). In the ETL world, another common file blazon is a CSV file, this more often than not can be done via Import-CSV and then applying regular expressions to cheque the corresponding columns.

Next Steps

Please read the following tips that take some similar topics:

• SQL Server Regular Expressions for Data Validation and Cleanup
• Pre-Validating Information with PowerShell Before Importing to SQL Server
• Using a Simple SQL Server Majority Insert to View and Validate Data

Related Manufactures

Popular Articles

Nigh the author

Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more than fourth dimension for family, life and more automation.

View all my tips

Commodity Last Updated: 2015-09-03

pfeiferyeted1980.blogspot.com

Source: https://www.mssqltips.com/sqlservertip/4013/excel-file-data-validation-for-sql-server-imports/

Share this post