EViews.com

How do I export data to an ODBC Database?

To export data to an ODBC Data Source, you'll need:

• 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. The user account must have the ability to create/drop/edit tables.

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

• 1. In EViews, go to File->Save As->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. Your DSN may be hiding under the Machine Data Source tab so be sure to look there if you don't initially see it. If you don't have a DSN setup yet, you can create one by clicking on the New button which will kick off the "Create New Data Source" wizard. Once you've completed this wizard and/or you've found the DSN, click OK.
  4. On the "Specify table" popup, specify the name of the table to create/overwrite. Click OK.
  5. On the "Write specification" popup, specify the columns to export. Click OK.

Once complete, the destination table will contain the specified data.

To do this programmatically, you can use either 'wfsave' or 'pagesave' to save the data. Despite what the name implies, when used to write data to an ODBC database, 'wfsave' only saves the current active page, not all pages. This behavior is identical to 'pagesave'. (In a future patch we will change this to allow saving to multiple tables, one for each page.) But for now, these two functions do the same thing when it comes to ODBC. Examples of how to use these two methods follow:

1. Save all series into a new table:

• Code: Select all

  wfsave(type=odbc) dsn tablename @all @keep * pagesave(type=odbc) dsn tablename @all @keep *
  This will save all rows (@all) from all series (@keep *) to a new table (tablename) from the current workfile & page.
  
  If tablename is not specified, EViews will default to the name of the workfile. If the tablename already exists in the destination database, it will be dropped before being re-created.
  
  NOTE: Only series and alpha series objects will be saved to the database. All other object types are not supported via ODBC.

2. Save only certain series into a new table:

• Code: Select all

  wfsave(type=odbc) dsn tablename @all @keep x1 x2 h* pagesave(type=odbc) dsn tablename @all @keep x1 x2 h*
  This will save all rows (@all) from series x1, x2, and any other series whose name starts with 'h' (@keep x1 x2 h*) to a new table (tablename) from the current workfile & page.

3. Save certain series and certain observations (rows) into a new table:

• Code: Select all

  wfsave(type=odbc) dsn tablename @smpl 2 7 @keep x* pagesave(type=odbc) dsn tablename @smpl 2 7 @keep x*
  This will save observations 2 thru 7 (@smpl 2 7) from all series whose name starts with 'x' (@keep x*) to a new table (tablename) from the current workfile & page.

Currently, EViews does not support calling stored procedures to write data to a database table (or tables). We only support calling stored procedures that return data to bring into EViews -- so we've developed a work around that involves the following:

• a. Save EViews data into a work/temp table in the destination database. Let EViews have full control over the schema of this table.
  b. Once the wfsave (or pagesave) operation is complete, kick off a stored procedure to do the real import from the work/temp table. Because EViews will be expecting a resultset back from the stored procedure, it should return a single dummy row that will get stored by EViews into a new workfile page. After completion, you can then delete the page. For example:

  Code: Select all

  pagesave(type=odbc) dsn temptable @all @keep * pageload(type=odbc, page=temppage) dsn "{call importStoredProc}" @keep * pagedelete temppage

Steve

How do I create the odbc databace and the DNS using eviews?

How do I create the odbc databace and the DNS using eviews?

Creating a database and/or a DNS is outside the scope of this EViews forum but I'll go over the rough details to try and help you out.

Usually you would already have an external database (such as SQL Server, Oracle, DB2, MYSQL, or even MS Access) that you want to push data into. These databases are usually hosted on a different machine on your network and provides centralized access to all of your data.

Each kind of database requires a different way to connect to them. "ODBC" was developed as a standard way of connecting to all of these different kinds of databases so that software like EViews could 'talk' to these different databases without having to do anything custom for each database type.

Hence, if you have a database that also provides you an ODBC driver, you can install their ODBC driver on your machine that is running EViews, create a DSN (see below) to connect that database, and then instruct EViews to write to that database via that DSN.

To create a DSN on your machine, go to Control Panel -> Administrative Tools -> Data Sources (ODBC). There, you should see a tabbed dialog called "ODBC Data Source Administrator". Click on the Add button to begin creating a new DSN.

You should see a list of ODBC drivers already on your system. From this list, pick the driver that matches the type of external database that you're connecting to. For example, if you have a Microsoft SQL Server database, you'd pick the "SQL Server" driver and then click "Finish". If you don't see the name you're looking for, you're going to have to talk to your database administrator about installing the correct driver on your system.

Once you click on Finish, this is where the steps will differ based on the driver you pick. For example, SQL Server will require you to specify a DSN name, server name, authentication type, database login id, password, and optionally a default database. Again, if you don't know this information, you're going to have to talk to your database administrator.

Once you've created this DSN successfully, it is now available to any program running on your machine. At this point, you can launch EViews and start exporting data to (or importing from) your ODBC compliant database.

Microsoft's ODBC page: http://support.microsoft.com/kb/110093

Microsoft releases a set of standard ODBC drivers that can be installed on your system (if it's not already) called MDAC (Microsoft Data Access Components). The current version if MDAC 2.8 SP1. You can get it here: http://msdn.microsoft.com/en-us/data/aa937729.aspx

Steve

Hi all,
Where in this code would I add the 'schema' component?
Thanks!
Bob

wfsave(type=odbc) dsn tablename @all @keep *
pagesave(type=odbc) dsn tablename @all @keep *

I think I have this figured out (would it simply be schema.table?)
IF i want to update an existing table, is it as easy a adding mode=update inside the pagesave command?

EViews can't update a pre-existing table via ODBC. It always just drops the table (if it exists) and then re-creates it to match the columns (object names) that is being pushed.

To update an existing table, you'll have to allow EViews to create a new table and then somehow trigger a stored procedure to push all the rows into the existing table manually.