How to extract data from SQL using Excel

[WaitronUnit]

I'm not trained in SQL, and won't be taking classes 'til later this year but need to get data from a SQL file into an Excel file. (1) How does one do this? (2) How can I determine the table names using Excel? Any help is appreciated. Thanks.

 

[ClaireHCM]

The easiest way is using DTS package. You can also use openquery and openrowset.

 

[fredp1]

from withing Excel you can use this...
=SQL.REQUEST("dsn=yourdatabasename;uid=yourUserId;pwd=yourPassword",,2,"select field1,field3 from yourtable1 where table1key=100 and field2='"&$A1&"'")

(You'll need to have Excel ODBC addin installed)
with regard to knowledge of SQL, you'll need some basics to manipulate the data if the information is stored in multiple tables.
You can find out a little more on the sql.Request in Microsoft office forum.
Basically, the above command uses a value in cell A1 to match with column field2 in the table to return 2 fields from the table

As Claire mentioned prior, you can also use DTS. This is great if you want need to schedule the file on a regular basis.
Fred

 

[WaitronUnit]

Thank you both... I'm having to learn as I go along!

 

[fredp1]

Hi,
Sql.request might be misleading for what you want to do as it returns a array. The forum is the VBA Visual Basic for Applications (Microsoft).

Microsfy Excel Query would be a better alternative to sql.request.
Tools, Get External Data, Create New Query...
Its a very good for beginners as it leads you through the data getting processes. All you need is a ODBC connection to your server.

Fred