EViews.com

How do I import data from an ODBC database?

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: 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: 2. Load only certain columns from a table: This will load column1, column2, and all columns whose name starts with 'h'.

3. Load only certain columns and certain rows from a table: 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: 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: 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: 5. Read data from a stored procedure: 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: Steve

Thanks.

Follow up question:

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

etc…



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


What is the best way to do this?

Technically, you could run a bunch of queries, one for each country such as: However, there is much more efficient way of "unstacking" your data. First do an import into EViews of the entire table as it is. 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

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.

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

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?

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

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

That means that there is at least one case where two observations have the same ZBBSECURITY and VALUATIONDATE values.