SQL with Manoj

You have an important lead set or any other orders/financial recordset in excel and have to import the data in SQL Server. This is not a one time effort but ongoing and in future you have to deals with lot such records/data files. To import valid data in your SQL tables you want to ensure and validate at the frst step instead of correcting the data by firing SQL queries. Also to avoid this manual work to identify the incorrect/invalid data out of thousands of records automated approach would be quite helpful.

Excel not only store data but also have a power to run VBA macro code against and manipulate the data. Lets take a simple example to validate a set of Customer numbers & their insert date, shown below:

-- Test Data which is correct and expected to import in SQL table.
InsertDate	CustomerNo
11/16/2009	91878552
11/16/2009	101899768
11/16/2009	101768884
11/16/2009	123456789123
11/16/2009	101768800

Columns InsertDate should be a valid date & CustomerNo should be numeric & less than 12 characters or max length equal to 12.

-- Lets tweak the test data put some invalid values under both the columns, shown below:
InsertDate	CustomerNo
11/16/2009	91878552
51/96/2009	101899768
11/16/2009	1017abc84
11/16/2009	123456789123
11/16/2009	101768800

If you are on MS Office 2007 create a Macro enabled excel file with extension *.xlsm, OR if you are on 2003 and lower version *.xls file would be fine. Copy the above data in Sheet1 with incorrect values. Now press open up the VBA macro editor or press ALT+F11 keys. Now double click on Sheet1 under “Project -VBAProjects” explorer.
I wish to run the validation while someone tries to save the macro enabled excel file. So the event selected is “BeforeSave”. And the VBA macro code goes below:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

	Dim errorCode As Integer
	Dim errorMsg As String

	Dim numOfRecs As Integer
	Dim counter As Integer

	'---------------------
	'Start - Validate Date
	'---------------------
	Sheet1.Activate
	Range("A2").Select

	errorCode = 0
	numOfRecs = 0

	Do Until ActiveCell.Value = vbNullString

		' Validate Date
		If IsDate(ActiveCell.Value) <> True Then
			errorCode = 1
			ActiveCell.Interior.ColorIndex = 3 'Red
		Else
			ActiveCell.Interior.ColorIndex = 2 'White
		End If

		numOfRecs = numOfRecs + 1

		ActiveCell.Offset(1, 0).Select

	Loop

	If errorCode = 1 Then
		errorMsg = errorMsg + vbCrLf & "- Invalid Insert Date"
	End If
	'-------------------
	'End - Validate Date
	'-------------------

	'--------------------------
	'Start - Validate Customer#
	'--------------------------
	Sheet1.Activate
	Range("B2").Select

	errorCode = 0

	Do Until ActiveCell.Value = vbNullString

		' Check for a valid Customer Number
		If IsNumeric(ActiveCell.Value) <> True Or Len(ActiveCell.Value) > 12 Then
			errorCode = 1
			ActiveCell.Interior.ColorIndex = 3 'Red
		Else
			ActiveCell.Interior.ColorIndex = 2 'White
		End If

		ActiveCell.Offset(1, 0).Select

	Loop

	If errorCode = 1 Then
		errorMsg = errorMsg + vbCrLf & "- Invalid Customer #"
	End If
	'------------------------
	'End - Validate Customer#
	'------------------------

	' Go to first cell.
	Range("A1").Select

	'If errors then display the error msg and do not save the file otherwise save the excel file.
	If errorCode = 1 Then
		MsgBox "Workbook not saved. Following are the fields that contain invalid values." _
			& vbCrLf & errorMsg & vbCrLf & vbCrLf & _
			"Please correct the values highlighted in RED color.", vbCritical, "Data Validation ERROR"

		Cancel = True

	End If

End Sub

Invalid values highlighted RED

Now save the macro code and close the editor. Now try to Save the Excel file, you will get the cells with invalid values highlighted in RED, as shown in the image. The excel file not be saved and when closing you will get the dialog box to save it again & again.

In order to save it correctly you need to correct the invalid values, i.e. InsertDate & CustomerNo, and then save it. The RED highlighted cells will become WHITE for the correct values.

This excel file is now very much validated and clean to import in SQL Server or any other database.

Try this code and tweak it as per your needs.

I’ve also discussed this logic in MSDN’s TSQL forum at following link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/09299d7d-9306-4ea4-bb29-87207572aa04

Suggestions & comments are welcome!!!