Reading multi-sheet Excel files into multi-page EViews files

For requesting general information about EViews, sharing your own tips and tricks, and information on EViews training or guides.

Moderators: EViews Gareth, EViews Moderator

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

Reading multi-sheet Excel files into multi-page EViews files

Postby EViews Gareth » Fri Jan 08, 2010 2:35 pm

One of the programming features added in EViews 7 was the ability to retrieve the names of the sheets in an Excel file. This function can be used as part of an EViews program that brings in each sheet of an Excel file into a separate page in an EViews file.

As an example we'll use the Excel file shown below. Notice it has multiple sheets, containing data on a number of different countries:
Image

Note the Excel file can be downloaded from here.

The following EViews program can be used to bring this in to an EViews workfile. Note the first line of the program contains the path to the Excel file, which you'll have to change to fit where-ever your own Excel file is located.

Code: Select all

%filename = "c:\temp\sheetpanel.xlsx"   'file name of the file to be opened

%sheetnames = @tablenames(%filename)   'find the names of the sheets in that file

%sheetname = @word(%sheetnames,1)   'get the first sheet name

wfopen(wf=panel,page=%sheetname) %filename range=%sheetname    'open the first sheet as a new workfile (with name=panel, and pagename=the first sheet name)


'loop through the remaining sheets, loading them into the workfile one at a time
for !i=2 to @wcount(%sheetnames)
   %sheetname = @word(%sheetnames,!i)  'get the name of the next sheet
   pageload(page=%sheetname) %filename range=%sheetname       'load the next sheet
next


Note that this code assumes that the sheet names in the Excel file are valid EViews page names. One obvious exception to this would be if your Excel sheet names have spaces in their names - EViews pages cannot have spaces.

After running that program, you should end up with an EViews file that looks like this:
Image

And you're done!
Follow us on Twitter @IHSEViews

Return to “General Information and Tips and Tricks”

Who is online

Users browsing this forum: No registered users and 17 guests