Importing data from an xlsx file

For questions regarding programming in the EViews programming language.

Moderators: EViews Gareth, EViews Jason, EViews Moderator

cfarmelo
Posts: 2
Joined: Sat Apr 07, 2018 12:56 am

Importing data from an xlsx file

Postby cfarmelo » Mon Apr 09, 2018 9:56 am

I want to write a program file that imports time series data from an excel (xlsx) file, but am having problems with how I write the command in the program. I understand that I need to use the import command, but could someone tell me the exact command that I need to use.
- the data is in a file called test.xlsx
- columnn A has the date (annual data from 1990 to 2017)
- columns B to BF contains the data (i.e. 57 data series)
- Rows 1 and 2 have text describing the data in each column
- Row 3 has the series name (a short text name of between 3 and 6 digits) that I want to use in eviews

I have managed to get the import command to work and read some of the data into a work file. HOWEVER:
- it only reads in some of the data (I don't know what or where the argument goes, to say that I want it to read in 57 series (or all the series in the file)
- it seems to assign what's in row 2 as the series title (which is a very long text description of the data). I want it to use the 3 - 6 digit name I've given in row 3, but don't know what I have to use to do this.

Thank you, in advance, for your assistance!

I'm using eviews 8

Chris

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

Re: Importing data from an xlsx file

Postby EViews Steve » Mon Apr 09, 2018 2:06 pm

Our import wizard is usually pretty good and showing you what options are available during import. The one you're looking for is "Header type" on page 2 of the import wizard:

scr1.png
scr1.png (18.87 KiB) Viewed 267 times

Then, it's a matter of correlating that option with our IMPORT documentation to find out what the exact name of the argument is for the IMPORT command. In this case, it's namepos.

So here's what the command would look like:

Code: Select all

import my_file.xlsx range=Sheet1 colhead=3 namepos=last names=(DATE) format=(D)

The colhead attribute tells EViews you have 3 header rows, and then namepos tells EViews to use the last header row as the name value. The other two header row values get concatenated into a single long Description (see Label view on each object to see this value). I also threw in the names attribute to tell EViews to name the first column as "DATE" and the format attribute to tell EViews the first column is a Date type.

If you have blank rows (or columns), you'll have to manually remove them from the spreadsheet before attempting to import otherwise IMPORT will stop at the blank row.



By the way, EViews 9 added a new Captured Command window that shows you your generated commands after each action performed thru our GUI. That's actually how I generated the above command (using EViews 9) as it's faster than looking up the nitty-gritty details in our help files.

In addition, EViews 10 expanded the IMPORT command with support for reading in each header row as a separate object attribute which you can explicitly name and save with each object.

cfarmelo
Posts: 2
Joined: Sat Apr 07, 2018 12:56 am

Re: Importing data from an xlsx file

Postby cfarmelo » Mon Apr 16, 2018 12:01 pm

Steve, thank you very much - that worked perfectly!

Chris


Return to “Programming”

Who is online

Users browsing this forum: No registered users and 7 guests