options for reading Excel files in wfopen

[Wojtek]

I'm reading an Excel file in to a new workfile. I want EViews to start reading from row 7. The following code line

wfopen {%arm_factors_xls} range={%range} firstobs=7

gives me an error "unrecognized file option 'firstobs=7'.

How do I make this option to work?

[EViews Jason]

The documentation incorrectly list 'firstobs' and 'lastobs' as options for the wfopen command when reading from an Excel file. You are correct in using the 'range' option however.

Code: Select all

wfopen {%arm_factors_xls} range=A7 ' starts reading from cell A7

[dagfinnrime]

Hi,

dropping the "latsobs" option is unfortunate. It would be very handy.

I have lots of Excel-files from which I want to import lots of different pieces of information. I know upper left corner, but I don't know the number of columns or observations in each file.

Take this file as an example: https://www.dropbox.com/s/u3rw8mwr2v37b ... .xlsx?dl=0

I need the variable-name in row 6, and the number in row 7 (for all columns). According to documentation, one of the following could work.

Code: Select all

wfopen "NOK_fixed.xlsx" range="Sheet 1!a6" colhead=1 lastobs=1

or

Code: Select all

wfopen "NOK_fixed.xlsx" range="Sheet 1!a6" colhead = 1 scan=1 pagecontract 1 1

Just writing

Code: Select all

wfopen "NOK_fixed.xlsx" range="Sheet 1!a6" colhead = 1

doesn't work because Eviews is formatting it as datenumbers and setting 2 first obs to NA.

I guess I can do it in several steps, finding number of variables (=columns), and then finding the correct "range", but it seems unnecessary complicated.

So how do I this without explicitly setting the range?

Thanks,
Dagfinn

PS! so "scan" is also not working?

[jthodge]

I ran across this topic because I too noticed that scan=[int|all] isn't a recognized option for Excel files, as explained in the Eviews 8.1 Command Reference documentation.

Regarding your question about reading the series names from your file, I would suggest:

Code: Select all

wfopen "NOK_fixed.xlsx" range="Sheet 1" colhead=6 namepos=last

Note that the range can just be listed as the Excel worksheet name and Eviews will assume it starts in cell A1. The colhead option tells Eviews how many header rows there are on the worksheet. The namepos option tells Eviews to get the names from the last row in the column headers (it ignores the other rows in the header).

I'm not sure if this will help with the way Eviews auto-formats the data it reads. Usually, I put any date fields in the first column and Eviews is fairly good at structuring the workfile correctly using this date information. Sometimes you have to experiment with different Excel date formats in the source file so that Eviews can understand that the numbers are dates when it imports the file.

If all else fails, you should be able to use the names=(...) and types=(...) options to define the series names and formats manually.