XLSX files and EV7

[fmramos]

Hi all!

There'll be an update/patch to import xlsx files?! I'm managed using 'import' command, but its not doing the right thing when more than one range is used. The online documentation is not enough to understand how EV7 deal with xlsx files (at least I couldn't find enough information; sorry if I'm wrong).

I'm using 'import' do get lots of Excel columns/variables (more than 500). Its being hard to cope with them all. Very slow. EV6/7 deal easily with lots of data in the xls 'old' format. I know xlsx has different structure, but is becoming standard for non-academic users.

I'd like to thanks in advance for tips in this direction.

Fabio

[fmramos]

First, sorry for inserting this in 'tips, tricks and suggestions' instead of 'data manipulation'.

One specific weird thing dealing with xlsx files: command import doesn't get missing observations in the beginning of the named range (series name, NA, NA, ..., NA, x1, x2,..., xn).

Tks,

Fabio.

[EViews Chris]

I'm afraid I'm not sure I understand your comments completely.

Ignoring performance issues, reading an xlsx file in version 7 should behave very similarly to reading the same file saved in xls format. If you have any examples of cases where EViews behaves differently when reading the same data in xls and xlsx format, please let us know by emailing to support@eviews.com and we'd be happy to look into it.

Most of what the import command does does not depend on the format of the incoming data, so xls or xlsx files are meant to behave much the same.

As far as performance goes, reading from xlsx files may be significantly slower than xls files because of the details of the xlsx file format. The xlsx file format consists of several compressed xml files which take some time to decompress and then are relatively slow to process. They are also not well suited to random access within the file.

The bottom line is that reading an xls file will probably always be faster than reading an xlsx file containing the same data, and reading small ranges within a large xlsx file might be much slower than from an xls file.

As far as the weird thing you mention to do with NAs at the beginning at the named range, I think what you're seeing is that EViews is treating the leading empty rows as part of the column header (which is used to determine series name / description). You can get around this in several ways:

1) Use the 'colhead' option to tell EViews that you don't want to treat these as part of the header

eg.

Code: Select all

import test.xlsx range=myrange,colhead=1

2) Use the Excel NA code #N/A in the Excel file to indicate NAs instead of empty cells. EViews will not fold #N/A cells into the column header.

None of this is specific to EViews 7 or to the import command or even to xlsx files (wfopen in EViews 6 with an xls file will behave the same way).

I did notice when playing with this that EViews 7 is not letting you back up and change the column header options in the wizard after issuing a command like:

import test.xlsx my_named_range

We'll look into whether we can enable the 'back' button in the wizard so that you can adjust the column header options interactively if you need to.

[startz]

and what about exporting xlsx files?

[EViews Chris]

We may add writing of xlsx files if there's enough demand for it, but for what EViews currently writes, I'm not sure there's any real benefit over simply saving as a 'csv' file.

An xlsx file is going to be slower to work with and has a row limit of 1 million rows.

Almost every program understands csv files, relatively few understand xlsx files (for now). Excel itself is happy to read either.

I'm not sure I can think of any advantages of xlsx over csv if all you're writing is a single table of data.

[fmramos]

Really tks!!! And as usual tks for the fast answering, too. I'll try these tips tomorrow morning. Slower importing xlsx files is bad, but a reasonable penalty for importing more or less 500 columns together (eg. detailed CPI or PPI). Undoubtedly this forum is the best way to get useful information about EV. Fabio

PS. I agree with Startz about exporting xlsx. Most of non-technical users only have MS Excel. Some don`t even take advantage of text files (like CSV). Exporting to xls/xlsx is a way to soften the communication between academical/technical users and people in general that understand only basic files/graphics. Exporting to xlsx turns easier to link EV data with spreadsheets in general.

[EViews Gareth]

Most non-techincal users won't notice the difference between a CSV file and an Excel file. I believe Excel will take default association with CSV files, so CSV files have a little Excel icon, and double clicking on them will open it up inside Excel.

The only real difference is that non-technical users might not realise that Excel can open CSV files when they are given the save choice from within EViews.

[fmramos]

Not exactly. People often have .xls (or new xlsx) with graphs and simple operations (or not so simple). CSV files deals only with texts. I'm writing about corporate cooperative spreadsheets, several people using the same file. For instance, take my example: I save lots of series with monthly forecasts for inflation, industrial production, external sector etc. Other economists and... sales people take my numbers to presentations. Often they link cells and graphs to their spreadsheets and presentation (Power Point) files. EViews writing my forecasting directly to .xlsx would help. Of course is not as important as importing data, but it'd be useful. This kind of situation is common in economics depts. in non-durable goods companies. Most of employees only uses MS Office applications. It would be nice if economists get more time to study econometrics instead of copying and pasting data to .xlsx files and adapting their number to other non-technical users... It's only a suggestion. Tks.

[startz]

Oh, is there a command on the menu to save data as a .csv file?
(A little puzzling on the help system hints at how to do it, but it ain't intuitive.)

But I can see the argument that csv has advantages.

[EViews Chris]

I guess we all agree that the workaround for the moment is to write to csv (or xls if there are less than 65536 observations) and then do one 'open' and 'save as' operation inside Excel.

If you're working interactively, it's not such a big deal, but I see your point that it adds one more step and stops you being able to automate the whole process inside an EViews program.

It's a relatively painful format for us to work with unfortunately, but I suppose we'd better look into it.

As far as finding csv on the menus, it's true that it is more cryptic than I remembered it (Use 'File... Save As...', change 'Save as type' to 'Text file' then edit the filename to end in '.csv').

We should probably give it its own line in the 'Save as type' list to make things a bit clearer.

[startz]

Given all the nifty new tools in EViews 7, would it be hard to write something internally which wrote a csv file, opened Excel, read it in, saved it as an xlsx file, and deleted the csv?

I know it sounds kludgy, and would require Excel 2007 being on the machine, but if it saved a lot of coding....