Excel to SQL

Excel to SQL

http://www.sql-excel.com


One of the things that I see IT software development people struggle with is SQL to Excel integration.

SQL to Excel integration unfortunately has been made unnecessarily cumbersome because of the rather primitive awkward way Microsoft has chosen to implement SQL to Excel communication.

That's why I created a way to solve SQL to Excel integration once and for all. It doesn't have to be hard. It doesn't have to take a long time to develop software/spreadsheets which integrates SQL to Excel.

And in fact, Excel is the PERFECT platform for integrating SQL to Excel database access. Excel is PERFECT for rapid application development (RAD).

The right out-of-the-box though, SQL to Excel integration is painful in Excel. Not only that, but the way SQL to Excel was implemented, it created tremendous opportunity for the data to be structured wrong, or at minimum, misinterpreted.

Let me give you one specific example.

I called this example "data drift." What do I mean by "data drift?"

It's when you have a SQL table and it's imported into Excel. The data into Excel spreadsheet is static, imported only one time during an Excel session. However, the data in the SQL table continues to be updated by different user departments in the company.

In essence, the SQL to Excel "data drifts" more and from the corporate database on the server. This is where Microsoft's way of integrating SQL to Excel functionality falls far short.

As you can imagine, this can cause tremendous problems with departments and users. And it's one of the main reasons, SQL to Excel integration projects are so frustrating. I though there had to be a better way of implementing SQL to Excel integration.

My new SQL to Excel tool set I developed, solves that problem once and for all. Because now the data can be refreshed either through seamless and transparent SQL to Excel, or Excel to SQL integrations at key checkpoints and function points in the spreadsheet.

The best part, is that SQL to Excel integration is ALL transparent to the user. While there is updating going on in the background/behind the scenes, the user is unaware of the SQL to Excel communication going on.

Once my way of SQL to Excel has been implemented, the problem of data drift literally disappears. Because it looks like to the user they are really doing SQL to Excel importing and later updating the SQL database IN REAL-TIME.

SQL to Excel, or Excel to SQL doesn't have to be hard to develop either. In fact with my tools, if you're a software developer, I've made SQL to Excel a snap. In less than a few minutes from now, you can be impressing your boss or the users with what you can do with Excel. You'll be able to make Excel do backflips, things you, as a software developer always wanted it to do.

Using my SQL to Excel toolset, rapid application development (RAD) is a reality inside Excel. I urge you to check out my website at http://www.sql-excel.com.

Once you do, you'll never go back to the slow, error prone, painful way of implementing SQL to Excel integrations in the future. SQL to Excel integrations from here forward will actually be fun and make your users extremely happy and confident in the data.

Check it out at: http://www.sql-excel.com

http://www.youtube.com/watch?v=L_WqmW...

Excel to SQL

---

Closed Caption:

excel to sql
as a nike professional
already know
all about excel
this is where your business users build
endless excel workbooks solutions for
anything and everything
and then
they come to you
and they need you to take some
support them
automate them aged attorneys excel to sql
workbooks into real systems uh... hurt
your business i t department
this is great right up until you figure
out
but the solution that these
user-submitted
is never exactly quite the same thing
twice in a row
as the business managers
as their job evolves
um... sort of their workforce
every time they run them
uh... it does something different
everytime you talk to them and get a
different story you can never seem to
get all the requirements so you can
build an official solution
uh... produced for these types of
applications
orignal use excel
and are how do you sequel principal
server
uh... i'm gonna show you something
that will change your life forever
i'm going to show you my secret methods
that will allow you to integrate your
excel workbooks
with your business data
directly
turning your excel workbooks into little
athletes that can empower you a new
users
you can get things done aways
leaders never consider possible you
contain excel to sql but not replacing it but
rather by integrating your excel
workbooks with your business data
rather than building new expensive
systems to replace them
let me show you what i'm talking about
i have a sample worksheet that are put
together i'll just to save uh... your
time signal to washington at the store
basically have a connection string
tell you what server you want tells you
what database you want
throws you a username password in this
case there is an integrated security
uh... and using my my windows organ
uh... i'm using the north when database
sample database for siebel server
um... if you're watching this you
probably know with more from databases
if not you can google it you can
download it you can install it um... and
and you can use it to do your own
samples
but instead of at least imagine your own
data in your own business applications
now i have a sql statement here select
territory i_d_ an employee i'd be from
the employees' territory stable
helena order this
uh... by employees idea for reasons of
the become clear in a minute
here i have a function
their references
affectively these to sell soap a
reference is my connection string it
references
mysql statement
and then it has only to the selby b_-one
uh... that's much triggers so i'll tell
you that my second basically fraternity
zero
doesn't run the risk your statement
imagine a spirit of something
pretty big and heavy weight you want to
be older right when you want uh...
or or or not when you want
uh... i can't put in the year
into the celica puttin who the current
time
loops
now every time i casually to workbook is
gonna run your skills statement because
the time is always changing uh...
you know for his put across that one
so around the first time
when undo all of this and put it back to
my one that i had originally
now here of run the function
executed bs ql went to the server cut
the results of your scene the first row
back now to see all the data
all you have to do is take dysfunction
lets himself be five right now amana
highlighter range
uh... goes down a little ways on how
much they don't let me get back uh... my
tough to for edit
and they control shift enter
that applies that a sql statement to the
range of self
now their runs a statement one time
because this server because the data to
brings it back it puts it in your
workbook and best of all
every time we change the query
broom there should be in it
if i happen to build my function here
with a reference to now
now everytime i take her to work with
their fate
boom-boom because there was a query i
get back today and i want
so now or do we do with this
imagine we come over here in the whole
purpose of this exercise
is that um... my users need to
arbitrarily reassigned
the territories that they have assigned
to the employees even assigned each
territory on a regular basis
uh... somewhere go down
come in and say well it's this employees
want only has two to two territories
uh... to make this territory belong to
employees want
product that they're going four within
one
now it can worry
and i have an update template
basically uh... it's an ask you all
statement you should recognize it
i have to rameters editor tokens of
curly brace three curly brace career is
one curly brace
those are going to be replaced by a
function coped organizer
um... and it's going to pick up values
from this range right here
so now i'm going to go into the cell
and i have
a formula
somalia the formula d here says
amused organizer to take my as sql
template
and replace the values and with the
values from this range over here
i'm going to
have a good statement that says if the
the new employee is different than the
old employees
and if the new employee is present with
another statement i want to get this as
hero statement for the template
organizer was i want an empty string
so here we don't have a new employees
idea so i have nothing
but if i make this to be too
now i get an update statement miss so
and you can see
that it wants to do
to the uh... the the
there we go
update territories that employees
vehicles to return to our equals this
territory if they go to data from here
nike copy this
template all the way down the workbook
thus far down as i have to okay
now
i get updates statements where i need
them where the data doesn't change
adulthood update statements once again
the whole thing iran's in real time has
a chance to work book
now all storm all done
where we want to do it all away to data
back from a database now
this demos computers some force is going
to basically polson data back make some
changes right into the database
will talk about that in a minute
but basically i take this the sequel
that i generate here my output template
and i pass it back into the sickle
server exact function
i'd give it my connection string it
already given it and i get a new
triggers so
in this case i want to give it uh...
do you want as much a rehearsal
and that tells his triggers not mabel
kaf i copied mister mccollum
what you'll see
is that
it tells me
triggers not new wise and unable to
because i need to make this reference to
the cell fixed with them
now i've got my like my uh... formulas
was amusing affixed reference for the
uh...
connection string really fixed reference
for these two cells don't move in the
workbook
but this refers to the s_ ql is not
fixed so that when i copied it down
mccollum it will uh...
here it will update from g four g_-seven
g_-eight and so forth
term a trigger
atas wrote to be added to the workbook
now when i pulled us back
we're gonna see
is that
i'd only have
one territory for the sim card here
played
uh...
characteristics
it just texted now i have more ones
so display command and i may
some of these tend to be ones
and i make this one's not update
pulls it back and now can see all these
ones and here so we can see that it's
doing the updates into the database as
we go
couple things about this
i'd use this
all the time
for doing uh... basically data prep work
where
i know i have to get data out of the
database
i have to give it to user who needs to
go one after one a bunch of stuff in it
and i have to put it back in the
database
you are used to write
spent hours writing as ql statements to
make use update selected deployed into
our q a and you eighty and corruption
process um... arctic hours north of my
time now
i give the user workbook that
effectively has uh... the data pulled
into a dislike this uh... i copia value
to this to a new range and i give it to
the user nasik updated how you see fit
then when i get back
uh... and ask you all template
uh... by
generate usual statements
and often times i will actually copies
out
uh... of here and put them into a text
editor and running to normal software
uh... the planning process
level actually execute them out of the
workbook directly often i don't have
permission to you that in production
uh... you probably don't either
but that said it you you can now
one more steps
down the road
you can imagine
all kinds of things you can do with this
i don't have to tell you what business
sp problems your user struggling with
um...
but imagine that you have an analysis of
stars from something very simple like
this
it pulled back some other data
and another taboo the workbook okay pull
back some more data another tab of the
workbook it doesn't do you look up to
does some aggregations of pivot tables
uh... the users do some manual analysis
on it that you look up some market
research
uh... and in the end
the end up with a one should be the here
uh... like several worksheets
disconnected from the source data
where they have some primary key some
record
and some updated data they have to write
back and you have to put this back in
the database and imagine this isn't
period process
they do this the right to data to go
back and they start over again
if you do that
what you can do is you can have
all this workbook staged
from the very beginning
where they trigger the data french
curvatures exactly right now it's it's
coated so it always pulls of data
um...
so it triples the data every time they
calculate the workbook as they work on
the workbook keeps pulling the data
um... actually prefer not to do this
except for a total pointed to a trigger
self
uh...
would be this one
but assad says trigger it doesn't wanna
pull the data
i thought okay now polls today to was
observables right
now on the update side
generally i don't want to trigger an
able
so uh... when they pull that they had to
do all their analysis of the four into
this thing the users can now
uh... opponent
and you can help them with this
the exact price block
uh...
at on an equal bolat
you know used or procedures for this
because you can permission these things
um... and set them up uh... accordingly
um... at you
you'd probably get the idea for you can
do with this yuki i don't have to make
up any stories for for all the great
things you can be you already know you
already
personal foreign
at the at the lack of integration
between as sql an excel to sql and this little
bitty pieces the enabling technology is
this going to change the way
you use ical server
and the way you use excel to sql
and the things you can teacher users to
do
in your business environment