How do I import data from an ODBC database
Moderators: EViews Gareth, EViews Jason, EViews Steve, EViews Moderator
How do I import data from an ODBC database
How do I import data from an ODBC database?
-
- EViews Developer
- Posts: 789
- Joined: Tue Sep 16, 2008 3:00 pm
- Location: Irvine, CA
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
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
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?
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 Developer
- Posts: 789
- Joined: Tue Sep 16, 2008 3:00 pm
- Location: Irvine, CA
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:
Using Program:
Steve
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.
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
more on query, please
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.
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 Developer
- Posts: 789
- Joined: Tue Sep 16, 2008 3:00 pm
- Location: Irvine, CA
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
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
MS Query
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?
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 Developer
- Posts: 789
- Joined: Tue Sep 16, 2008 3:00 pm
- Location: Irvine, CA
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:
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
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
-
- Posts: 2
- Joined: Mon Jan 19, 2009 9:58 pm
Re: How do I import data from an ODBC database
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"?
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"?
-
- 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
That means that there is at least one case where two observations have the same ZBBSECURITY and VALUATIONDATE values.
Follow us on Twitter @IHSEViews
Who is online
Users browsing this forum: No registered users and 19 guests