How to Import Data from Excel sheet to Sql server 2008

Question

How to Import Data from Excel sheet to Sql server 2008

navbingo20

Valued Member

Points: 72
More actions
January 3, 2015 at 2:06 am

#294047

Hi ,
i have an Excel sheet , that i need to import directly to sql server 2008. how can i do that.
please help me to resolve this issue.
Excel Columns structure
january Feb
2014 2014
Name Id Status phy mat chem phy mat chem
The 4th column is a Merged one ..
Thanks
Navin

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

navbingo20 (1/3/2015)
Hi ,
i have an Excel sheet , that i need to import directly to sql server 2008. how can i do that.
please help me to resolve this issue.
Excel Columns structure
january Feb
2014 2014
Name Id Status phy mat chem phy mat chem
The 4th column is a Merged one ..
Thanks
Navin

Heh... that's going to be tons of fun. Kinda like splitting the data I used to get from Double-Click.net.

Are the phy, mat, and chem listing actually in their own columns and the dated headers for each such group are in a merged set of cells?

Also, are you using a 32 bit SQL or 64 bit?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

p.s. It might take me a bit to get to this one. If someone wants to give this a shot in SSIS (which I'm not going to use for this), I'd love to see how it's done for comparison purposes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

BTW... I've attached a sample spreadsheet that I'm going to use for this. If your spreadsheet is radically different, then make up a small example like I did and attach it. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

navbingo20 Valued Member Points: 72 More actions · Answer 4

Hi,

the excel sheet one you attached is correct..

sql server 2008 r2 i guess it should be 64bit

Thanks

Navin

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

navbingo20 (1/3/2015)
Hi,
the excel sheet one you attached is correct..
sql server 2008 r2 i guess it should be 64bit
Thanks
Navin

Perfect. I'm working on it.

Shifting gears, I'd love to see how someone does this in SSIS... any takers?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

@Navin,

Quick question, please. Do you just want to import the spreadsheet as it is (Flat table that resembles the spreadsheet in structure) or do you want the data to be "normalized" where the monthly group names from the first two rows are expressed as a separate column for each subject where each "cell" for each subject is on a separate row (EAV style)?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

navbingo20 Valued Member Points: 72 More actions · Answer 7

Hi,

i want to get the records for each month, quaterly, yearly for report generation purpose..

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Ok, here we go. I first had to make a couple of assumptions. which is why this code looks a little complicated.

1. 3 months isn't going to be good enough. They'll add more if they haven't already. With that thought in mind, I made it so that the spreadsheet reader code will dynamically expand with no action required on your part.

2. 3 subject areas isn't enough. They might decide to add another subject or two. With that thought in mind, I made it so that the spreadsheet reader code will dynamically expand with no action required on your part.

3. I'm assuming that you want the data in the final table to be somewhat normalized.

4. Name, ID, and Status are 'Common" on each row regardless of how many columns there are in a row. Those 3 items might not be enough. They might add one or two 'Common' items. With that thought in mind, I made is so that the spreadsheet reader code will dynamically expand with no action required on your part.

5. I assumed that the spreadsheet would ALWAYS be saved as the same file name. I didn't try to work around that. The file name is hardcoded in the code.

6. The final table is saved in a Temp Table called "#FinalTable".

I've attached 2 spreadsheet files. The first one is the original 3 months with the "Total" grouping at the end that run from January thru March. Save that spreadsheet somewhere that SQL Server can "see" and has privs to, change the hardcoded file name to match where the file is stored (search for "--Change File path and Name here" in the code to find it more quickly), and run the code. It'll populate the #FinalTable table and then you select from it to use however you want.

Then, pretending that time marches on and they added "April" to the spreadsheet, "Another" 'Common' column (who knows what for), and another set of "subject" columns (I added "Hist"), save the second spreadsheet over the first and run the code to see that the resulting table auto-magically picks up on all the new columns and data.

Of course, it'll also pick up on any new rows, as well, and I've included a new row in that second spreadsheet to prove it.

Last but not least, to prove further resilience to changes, I also shifted the data in the spreadsheet up and to the left 1 cell each.

If you don't want the "Total" ROW to be included, uncomment the line that has the "--This prevents the bottom "Total" row from loading." comment on the same line.

The code and example spreadsheets are attached. The code is heavily commented. Please read the comments (at least the headers) before asking any questions so that you're familiar with the code. It does require the use of OPENROWSET and it does require that you've downloaded and installed the Microsoft ACE drivers. Here's where you can get those if you don't already have them. Don't let the word "Access" throw you off.

http://www.microsoft.com/en-us/download/details.aspx?id=13255

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

And, yeah... there will be an article coming out on this. I have to make a couple of tweaks to handle different types of groupings.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

@Navin,

Any feedback on this? Did it work for you or ???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

navbingo20 Valued Member Points: 72 More actions · Answer 11

@jeff Moden : Just now saw your solutions and the messages.. i will try it from my end and then i will let u know the result..

Thanks alot for your detailed explanation and your quick support

navbingo20 Valued Member Points: 72 More actions · Answer 12

@jeff Moden :

First of all, i am very sorry for the delayed response..

it is perfectly working .. only one thing to be noted is , after downloading the Microsoft ACE driver , we need to install them from command prompt with /passive

Example : drive path > AccessDatabaseEngine_x64.exe /passive

To avoid error when executing the procedure in sql server 2008 64 bit , MS office in 32bit

and @jeff Moden,

in the sheet i am going to add year and month in columns , like the one i attached here..

now in sql , my table looks like the attachment

now i need to generate reports based on this table.. like quarter wise and monthly wise

Quarter PHY MATHS CHEM

Q1 1xx 2xx 3xx

Q2 2xx 1xx 4xx

Q3 2xx 1xx 4xx

Q4 2xx 1xx 4xx

is it possible to generate report like this..

Thanks in advance

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

navbingo20 (1/5/2015)
@Jeff Moden :
First of all, i am very sorry for the delayed response..
it is perfectly working .. only one thing to be noted is , after downloading the Microsoft ACE driver , we need to install them from command prompt with /passive
Example : drive path > AccessDatabaseEngine_x64.exe /passive
To avoid error when executing the procedure in sql server 2008 64 bit , MS office in 32bit
and @jeff Moden,
in the sheet i am going to add year and month in columns , like the one i attached here..
now in sql , my table looks like the attachment
now i need to generate reports based on this table.. like quarter wise and monthly wise
Quarter PHY MATHS CHEM
Q1 1xx 2xx 3xx
Q2 2xx 1xx 4xx
Q3 2xx 1xx 4xx
Q4 2xx 1xx 4xx
is it possible to generate report like this..
Thanks in advance

The "groupname" column that I included will convert directly to a DATETIME datatype. Don't separate Year and Month as separate integer columns. It'll only make your life miserable. Use DATEPART to report by Quarter.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)