Colhead : weird behavior of wfopen

[DBel2012]

Hi,

I'm using Eviews 7

I've got the following file : Test_Colhead_1.xlsx

The data are imported in Eviews usin the following code :

wfopen(type=excelxml,wf=Tempo,page=Mensuel) D:\Eviews\LivraisonsManuf\Test_Colhead_1.xlsx colhead=191 namepos=last @freq m 1992

It does not work. However, if I change colhead=191 for colhead=190,everything looks fine, thhough it should not be. I've tried different things, and I came up with the following conclusion :

Cells from A1:GH191 must not be empty in order for wfopen ... with colhead=191... to work.

What am I doing wrong?

Any help would be greatly appreciated.

Thanks!

[EViews Gareth]

When you say it doesn't work, what, exactly, do you mean? EViews opened the file when I used that command.


As an aside, you're probably better off using the Range= argument, rather than colhead=

[DBel2012]

Good morning Gareth,

The file that was attached contained data downloaded from the Statistics Canada website. This is where we get most of the data we are working with. The data starts on line 192 and the name of the series can be found on line 191. So, I presume that the column headers go from line 1 to line 191.

However, the following command :
wfopen(type=excelxml,wf=Tempo,page=Mensuel) D:\Eviews\LivraisonsManuf\Test_Colhead_1.xlsx colhead=191 namepos=last @freq m 1992 do

does not produce the expected result, though there is no error messages.

One thing is weird is that I use colhead=190 instead of colhead=191, the series are all imported correctly in Eviews, which does not make any sens to me.

Furthermore, if you take a look at the Excel file attached, you will notice that in the first line, only cell A1 contains information, the other cells are empty. In line 2, only cells A2 and B2 contain information, the other cells are empty, etc.

Here is what I did as a test. I've open the Excel file, I've written something in cell B1(or any other cells in line 1) and saved the file. I've rerun the wfopen command and Bingo, every series in the Excel file were imported correctly in Eviews.

I was wondering If you could try what I did to see if you get the same results?

I understand that I could use the range= argument, but I like the colhead one, and I find it more simple since no range need to be supplied.

Thanks!

[EViews Chris]

Here's an outline of what is going on.

When EViews sees the file, it divides it up into three sections: 'table header' lines (which are effectively thrown out). 'column header' lines (which go into variable names and/or descriptions), and lines containing data (which become the observation values).

Exactly how this happens is pretty complicated because there's a lot of things going on that interact.

What's going on in your case is that EViews has decided that the first row in your spreadsheet "Legende" is different from the subsequent rows and should be treated as a table header (which should be thrown out). It then starts reading in the column header from row 2 onwards.

You can see this if you try loading the file interactively with the menu command File...Open Foreign Data as Workfile. The range has been set to start at cell A2 and the top row displayed in the preview window is from row 2 in your spreadsheet.

Because the top row has been discarded, the correct column header count in this case is 190, not 191.

Alternatively, you can force EViews to keep the first row by adding the option "range=A1" to your command. In this case EViews will keep the first row as part of the column headers and there will be 191 rows of column header.

So, you should find that either of these commands should work:

wfopen(type=excelxml,wf=Tempo,page=Mensuel) "c:\documents\Test_Colhead_1.xlsx" range=a2 colhead=190 namepos=last @freq m 1992

wfopen(type=excelxml,wf=Tempo,page=Mensuel) "c:\documents\Test_Colhead_1.xlsx" range=a1 colhead=191 namepos=last @freq m 1992

If the range option is not specified, EViews effectively sets it equal to A2. if you then force the column header to contain 191 rows, you end up going one row too far and things no longer work correctly.

Chris

[DBel2012]

Good morning Chris,

Many many many thanks for clarifying all that. Your help is very much appreciated­.

Danny