How do I import data from an ODBC database

For questions regarding the import, export and manipulation of data in EViews, including graphing and basic statistics.

Moderators: EViews Gareth, EViews Jason, EViews Steve, EViews Moderator

Thommo
Posts: 14
Joined: Wed Sep 17, 2008 10:56 am

How do I import data from an ODBC database

Postby Thommo » Thu Sep 18, 2008 10:36 am

How do I import data from an ODBC database?

EViews Steve
EViews Developer
Posts: 789
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Postby EViews Steve » Thu Sep 18, 2008 11:20 am

To import data from an ODBC Data Source, you'll need 2 things:

1. Enterprise Edition of EViews 5 (or later).
2. An ODBC DSN (Database Source Name) defined on the same system that EViews is on. This DSN should point to the database you'd like to read from and specifies the database server, the user to login as, and possibly the default database (if applicable) to connect to.

Once you have your DSN ready, you can start the read process by:

1. In EViews, go to File->Open->Foreign Data as Workfile.
2. Select "ODBC Database..." from the Files of Type dropdown (should be at the bottom of the dropdown).
3. Select your DSN. If you don't have one yet, you can create one by clicking on the New button. Click OK.
4. On the "Select Table" popup, specify the table to import or a query. Click OK.
5. On the "Table read specification" popup, specify the columns to import. Click OK.

Once complete, you should have a new workfile that contains a series for each column imported.

To do this programmatically, you can use either 'wfopen' or 'pageload' to load in the data. 'wfopen' is used to read data into a new workfile where as 'pageload' is used to read data into a new page in the current workfile. Examples of how to use these two methods follow:

1. Load entire table into EViews:

Code: Select all

wfopen(type=odbc) dsn tablename @keep *
pageload(type=odbc) dsn tablename @keep *

This will load every column found in the table into a new workfile.

NOTE: The name of the new workfile (and/or page) will match the dsn by default. To specify a different workfile name (and/or page name) use the following:

Code: Select all

wfopen(type=odbc, wf=newname, page=newpagename) dsn tablename @keep *
pageload(type=odbc, page=newpagename) dsn tablename @keep *


2. Load only certain columns from a table:

Code: Select all

wfopen(type=odbc) dsn tablename @keep column1 column2 h*
pageload(type=odbc) dsn tablename @keep column1 column2 h*

This will load column1, column2, and all columns whose name starts with 'h'.

3. Load only certain columns and certain rows from a table:

Code: Select all

wfopen(type=odbc) dsn tablename @keep column1 column2 h* @selectif column1<10
pageload(type=odbc) dsn tablename @keep column1 column2 h* @selectif column1<10

This will load column1, column2, and all columns whose name starts with 'h', but only those rows where column1 has a numeric value less than 10.

NOTE: Though this method will work, it is not the best method to filter rows. The better method will be to use a query with a where condition instead of a tablename (see #4).

4. Read data from a query:

Code: Select all

wfopen(type=odbc) dsn "query string" @keep *
pageload(type=odbc) dsn "query string" @keep *

This will load all columns from the resultset returned by the specified query. The query can specify which columns to return and any specific where conditions.

NOTE: EViews, by default, will wait up to 30 seconds for the database to start returning the data. After that, a timeout error will be displayed. If the query needs more time to execute, you can specify a higher timeout value:

Code: Select all

wfopen(type=odbc, timeout=secs) dsn "query string"
pageload(type=odbc, timeout=secs) dsn "query string"

Also, the @keep and the @selectif property can still be used to further restrict which columns and rows to actually import. When running this command in EViews interactive mode, not specifying @keep will result in the "Specify Table" popup being displayed to allow you to choose which columns to import.

Here's an example of an import using a query string with a timeout value:

Code: Select all

wfopen(type=odbc, timeout=60) mydb "select column1, column2 from mytable where column1 < 10" @keep *
pageload(type=odbc, timeout=60) mydb "select * from mytable where column1 < 10" @keep *


5. Read data from a stored procedure:

Code: Select all

wfopen(type=odbc) dsn "{call spName}" @keep *
pageload(type=odbc) dsn "{call spName}" @keep *

This will load all columns from the resultset returned by the specified stored procedure. If the stored procedure takes in parameters, you can specify them like this:

Code: Select all

wfopen(type=odbc) mydb "{call GetCustomData(5, 'custom text')}" @keep *
pageload(type=odbc) mydb "{call GetCustomData(5, 'custom text')}" @keep *


Steve

Thommo
Posts: 14
Joined: Wed Sep 17, 2008 10:56 am

Postby Thommo » Fri Sep 19, 2008 11:34 am

Thanks.

Follow up question:

I have some data in our database in the following format:


Code: Select all

Country,   GDP,      Year
USA,      13843825,   2007
Japan,   4289809,   2007
Russia,   2087815,   2007
USA,      13000000,   2006
Japan,   4170000,   2006
Russia,   1720000,   2006

etc…



I would like to get this into EViews but separated out so that each country has its own series such as:

Code: Select all


USA
2007: 13843825
2006: 13000000

Japan
2007: 4289809
2006: 4170000

Russia
2007: 2087815
2006: 1720000



What is the best way to do this?

EViews Steve
EViews Developer
Posts: 789
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Postby EViews Steve » Fri Sep 19, 2008 11:55 am

Technically, you could run a bunch of queries, one for each country such as:

Code: Select all

wfopen(type=odbc) dsn "select * from mytable where country = 'USA'" @keep *

However, there is much more efficient way of "unstacking" your data. First do an import into EViews of the entire table as it is.

Code: Select all

wfopen(type=odbc) dsn mytable @keep *

Then you can "unstack" the data by doing one of the following:

Using GUI:
    1. In the new workfile that contains your stacked data, select Proc->Reshape Current Page->Unstack in New Page.
    2. In the Series Name textbox, enter the name of table column that represents your identifiers (in your case, Country).
    3. In the "One or more series names" textbox, enter the name of the column that represents observation identifier (such as your Year column).
    4. Leave the other fields with their default values and click on OK.
This will create a new page in your workfile that contains a new series for each unique Country. If you don't like the names of the new series objects, you can rename them manually by right-clicking on each and selecting "Rename...".

Using Program:
    1. Use the pageunstack command as follows:

    Code: Select all

    pageunstack(wf=myNewWF, page=myNewPage) Country Year


    Because the wf option was specified (wf=myNewWF), the new page will be created in a new workfile instead of the current one. The new workfile will contain a new series object for each unique Country found in the original Country column.

    2.If you don't like the auto-generated names of the new series objects, you can mass rename them with the following command:

    Code: Select all

    rename gdp* *

    which will just remove the beginning gdp on each object.

Steve

jsheldon
Posts: 7
Joined: Tue Sep 30, 2008 8:08 am
Location: USA

more on query, please

Postby jsheldon » Tue Sep 30, 2008 8:52 am

Hi Steve,

I have a SQL Server database that resides on another machine in our department (so, not the one EViews is installed on), and I'm trying to learn how to use Eviews to query this database and analyze the data. I can connect to it, but I do not want to just import whole tables. I need to use queries on joined tables in order to extract what I need. Following your list of procedures above, I get stuck on step 4:

4. On the "Select Table" popup, specify the table to import or a query. Click OK

What does that Build Query... button do? It would be nice if it went to some sort of query builder, because at this stage you cannot see all the tables and their field names. But that doesn't seem to be the case. I guess you can try to type a query in the box from memory, but in that case what would that button be for? I cannot find any description of this dialog box (and how to use it) in the help files.

EViews Steve
EViews Developer
Posts: 789
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Postby EViews Steve » Tue Sep 30, 2008 10:14 am

jsheldon:

The Build Query button uses an external application called Microsoft Query (msqry32.exe), which used to be installed by default whenever you installed Microsoft Office. It may be now be optional so it may not have been installed on your machine (if you already have Office). If it isn't, the Build Query button will not be enabled.

To install MS Query, you'll have to run the setup for Office and include it in the installation (usually under Office Tools). Once installed, verify that the registry has the following value pointing to msqry32.exe:

[HKEY_LOCAL_MACHINE\Software\Microsoft\Shared Tools\MSQuery]
Path="path to your copy of msqry32.exe"

If this entry exists then the button should be enabled. Once enabled, you can click it to get the MS Query builder popup that lists all the available tables along with associated columns for you to choose from.

Also, here's a link to a Microsoft help page that describes how to register MS Query properly in the event that the button is still not enabled after installation. http://support.microsoft.com/kb/126556

Steve

jsheldon
Posts: 7
Joined: Tue Sep 30, 2008 8:08 am
Location: USA

MS Query

Postby jsheldon » Tue Sep 30, 2008 3:29 pm

Thanks, Steve, for explaining what was supposed to happen with the Build Query button. (Someone should add that to the EViews help.) I've confirmed that:

MS Query is installed.
The registry entry is pointing to the right place.
It loads from within Word and Excel.

However, it still doesn't work from within EViews. The first time I click on the Build Query button after starting Eviews, a taskbar button flashes briefly to indicate that MS Query is loading, but it disappears. It's as if the program loads and ends right away. I checked the windows task manager, and the msqry32 process is not running. Subsequent clicking on Build Query during the same EViews session does nothing.

Any idea what could be causing MSQuery to load and close?

EViews Steve
EViews Developer
Posts: 789
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Postby EViews Steve » Tue Sep 30, 2008 4:14 pm

jsheldon:

EViews talks to MS Query using DDE which has some limitations. Basically, you don't want to be running MS Query already when you click on the "Build Query" button. You also don't want any other application using MS Query via DDE running either (such as an Office app or another instance of EViews). In case you haven't tried this already, please shutdown all of your office apps & EViews, make sure MS Query is not running (check Task Manager Processes tab for msqry32.exe) and then restart a single instance of EViews.

If this doesn't help, you'll have to provide more details:
    1. Window version.
    2. EViews version.
    3. MS Query version (run msqry32.exe manually and go to the About screen).
    4. Office version (or versions if multiple are installed)?
    5. Describe the steps you used to confirm that MS Query was working from Word (version?) and/or Excel (version?).

If you'd like, you can also email me directly (steve@eviews.com) with this information so I can reply to you via email for a quicker response...

Steve

Andrew Scott
Posts: 2
Joined: Mon Jan 19, 2009 9:58 pm

Re: How do I import data from an ODBC database

Postby Andrew Scott » Mon Jan 19, 2009 10:06 pm

I have a similar problem to Thommo. I have an SQL query returning data as follows

ZBBSECURITY VALUE VALUATIONDATE
SFSW2Index 0.980000 2005-07-01
CC1Comdty 1439.000 2005-07-01
AUDJPYCurncy 84.05000 2005-07-01
SFSW2Index 0.970000 2005-07-02
CC1Comdty 1440.000 2005-07-02
AUDJPYCurncy 84.07000 2005-07-02

...and so forth. I have tried the Unstack in New Page Process, using zbbsecurity in the series name box and valuationdate in the observation identifier box; it returns an error message "ID series do not uniquely identify workfile observations"?

EViews Gareth
Fe ddaethom, fe welon, fe amcangyfrifon
Posts: 13307
Joined: Tue Sep 16, 2008 5:38 pm

Re: How do I import data from an ODBC database

Postby EViews Gareth » Mon Jan 19, 2009 11:58 pm

That means that there is at least one case where two observations have the same ZBBSECURITY and VALUATIONDATE values.
Follow us on Twitter @IHSEViews


Return to “Data Manipulation”

Who is online

Users browsing this forum: No registered users and 19 guests