Excel | SQL with Manoj

Import Excel Sheet with multiple Recordsets

February 16, 2011 1 comment

Importing records from an Excel is a very simple task. Load your data in Excel with appropriate headers and Run the Import/Export Wizard, your records are transfered from Excel sheet to a MS SQL table.

But what if a single Sheet contains multiple record sets with variable headers. Like First 100 rows of Customer data with 10 headers. Then just below 50 rows of Order data with less than 10 or more than 10 headers.

Seems a bit difficult but not impossible. Its tricky though, lets see how:

Lets us suppose your Excel file is in following format shown in image below (Fig-1):
1. Contact recordset &
2. Sales Order recordset

Fig-1 Sheet with multiple recordsets

Now select the Contact recordset including headers as shown in Fig-2 and right-click and select “Name a Range…” option.

Fig-2 Create a named Range

Fig-3 shows a pop-up box where you can apply and provide a name for that range selection.

Fig-3 Range name for Contacts

Similarly repeat this for Sales Order recordset as shown in Fig-4.

Fig-4 Range name for Sales Order

Now you can see 2 named ranges Contacts & Sales in the dropdown in Fig-5.

Fig-5 Check both the Named ranges

Now we are ready to Import the data. As shown in Fig-6 the Import/Export wizard detects the named ranges and explicitly shows them as Source among other 3 sheets of your Excel file. Simply select both of them and make the required changes as you do for Sheets and click the Next button.

Fig-6 Named ranges while Import

Fig-7 Shows both the tables created & data loaded in SQL Server.

Fig-7 Sources getting copied into SQL tables

Now check the records and match them with the Excel sheet, as shown in Fig-8

Fig-8 Check the tables in SQL Server finally

Wow!!! that was simple.

I was asked this question in an SQL interview and I didn’t knew the answer, obviously you don’t need to know everything… but you should. Discussed this question on MSDN TSQL forum and got the suggestion, thus the blog post. Link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/cf849418-d18f-4b7a-99eb-dbfed6269603/#778c0e99-368a-40f2-b9d4-b747c1754853

Categories: Excel Tags: DTS, Excel, Excel Sheet, Import Export Wizard

Excel data validation with VBA macros

February 4, 2011 3 comments

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!!!

Categories: Excel, VBA Macro Tags: Excel, Excel data validation, Macro, VBA

Query Excel file source through Linked Server

November 12, 2010 11 comments

In previous post we saw how to setup a Linked Server for MySQL Database. Now lets go with other data sources. Excel files are the most important source of data and report management in a particular department.

When you need to do some query on Excel data, one way is to use Import/Export wizard, push the excel contents to SQL Server and then query on SQL Server DB. Another and easy way is to create a Linked Server to Excel file and query directly the Excel file itself.

You just need to create the Excel file and execute the following SQL Statements below:

–> For Excel 2003 format:

USE MSDB
GO
EXEC sp_addLinkedServer
	@server= 'XLS_NewSheet',
	@srvproduct = 'Jet 4.0',
	@provider = 'Microsoft.Jet.OLEDB.4.0',
	@datasrc = 'C:\Manoj_Advantage\NewSheet.xls',
	@provstr = 'Excel 5.0; HDR=Yes'

– Now, query your excel file in two ways:

SELECT * FROM OPENQUERY (XLS_NewSheet, 'Select * from [Sheet1$]')
SELECT * FROM XLS_NewSheet...[Sheet1$]

–> For Excel 2007 format:

USE MSDB
GO
EXEC sp_addLinkedServer
	@server= 'XLSX_NewSheet',
	@srvproduct = 'ACE 12.0',
	@provider = 'Microsoft.ACE.OLEDB.12.0',
	@datasrc = 'C:\Manoj_Advantage\NewSheet.xlsx',
	@provstr = 'Excel 12.0; HDR=Yes'

– Now, query your excel file in two ways:

SELECT * FROM OPENQUERY (XLSX_NewSheet, 'Select * from [Sheet1$]')
SELECT * FROM XLSX_NewSheet...[Sheet1$]

Note: If your excel file don’t have headers, then set HDR=No

You may need to execute the following SQL Statements to configure the Linked Server initially:

USE MSDB
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO

>> Check & Subscribe my [YouTube videos] on SQL Server.

Categories: Excel Tags: ACE 12.0, Excel, Jet 4.0, Linked Server, Microsoft.ACE.OLEDB.12.0, Microsoft.Jet.OLEDB.4.0, OPENQUERY, sp_addlinkedserver

Create Parameterized Excel Refreshable Reports

September 24, 2009 4 comments

In my [previous post] I provided steps to created a simple Excel Pivot Report. This Report queries your SQL Server Table and pulls data from it and renders into the Excel Sheet.

Many of the time we don’t require whole data, and want to add conditions to restrict the data, like what if we just need to pull data for Q2 (2nd Quarter) of year 2009. This would require a Date Range to be applied to the SQL Query.

How about Start Date & End Date that will be filled by the user on Excel Sheet on particular cells? And those values will be applied to the SQL Query WHERE clause and will pull just the data lying between those Date Ranges?

>> Here I’ve defined how we can pass Parameters from the Excel-Sheet to the SQL query to filter data.

1. Please refer to my previous post, stop at step 13.

2. Now, open Connection Properties and go to Definition Tab and add the following line at the end of your query:

where [DATE_FIELD] between ? and ?

Now, what does “?” means.

By applying ? (question-mark) in our query in WHERE clause Excel identifies them as 2 parameters and asks you from where to pick the data.

Now point both the parameters to the desired cell in the excel-sheet as shown below:

And finally you are done… when you will close the “Connection Properties” window the pivot report will get refreshed itself and pull the data as per the date you have applied.

Categories: Excel Tags: Parameterized Excel Reports, Pivot Reports, Refreshable Excel Reports

Create an Excel Refreshable Report from database & Pivot Report

September 24, 2009 2 comments

Steps to create a Pivot Report

1. Open Excel Workbook, assuming that we are on sheet1.

2. Click on Data [Tab] — From Other Sources — From Microsoft Query

3. A new window will open “Choose Data Source”, and click OK button.

4. A new window will open “Create New Data Source”.
a. Provide the Data Source name, any name.
b. Select the Database Driver Name: SQL Server
c. Click Connect button.

5. A new window gets open “SQL Server Login”. Provide the DB credentials as shown above. Click OK button.

6. It will connect to the data-source.

7. Now Click on OK button of the “Create New Data Source” window as shown at step-4.
Leave the 4th step for the default table.

8. You will be prompted back to “Choose Data Source” window with a new Data Source entry you just created.

Select it and click on OK button.

9. A new window opens “Query Wizard – Choose Columns”.

Select the number of columns you want to pull out and click on Next Button.

10. Click Next button repeatedly for the next 2 windows. In the final window “Query Wizard – Finish”…

… select “View data or edit query in Microsoft Query” and click on Finish button.

11. You will be redirected to a new tool i.e. “Microsoft Query”.

Here you can update your query, add new criteria and more filters.

When you are done with the query, go to the File menu and select “Return Data to Microsoft Office Excel”.

12. You will be redirected back to Excel with this new small window “Import Data”.

Choose your type of report & worksheet and then click on OK button.

13. You will get the data pulled out from the database for the specific query you applied, shown below:

Click on the Refresh button to get the latest data.

14. To create a pivot report from this report on sheet1.

Click on sheet2 and click on Insert tab and select PivotTable from the drop-down menu as shown in window below.

15. A new window will appear “Create Pivot Table”.

Select the source and destination table range as shown above. Click OK button.

16. PivotTable wizard is on the excel sheet and on the right side are the PivotTable field lists.

You can choose and drop the selected fields on the PivotTable area shown above.

Also you can apply filters and formulas to the PivotTable report.

17. The final PivotTable report is here.

I’ve chosen 4 columns from the table (as shown inside the Row Labels box) and the 5th column is the derived column, count of Customers/Records (as shown inside the Value box).