Advertisements
Home > Excel > Import Excel Sheet with multiple Recordsets

Import Excel Sheet with multiple Recordsets


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

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

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

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

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

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

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

Advertisements
  1. Faisal Siddiqui
    February 24, 2011 at 6:38 pm

    Thanks and very nice, i checked and it’s working fine with slight change in option of right click as “Define Name” instead of “Name a range”.

    But
    I have another excel sheet which is very complex compare to what you have shown, with many cells merged in some area and split in another area. In that excel sheet on right click i don’t see the options “Define Name” or “Name a range”. Any help in this regard ?

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: