Using import/wfopen instead of read

For questions regarding the import, export and manipulation of data in EViews, including graphing and basic statistics.

Moderators: EViews Gareth, EViews Jason, EViews Steve, EViews Moderator

Vladimir_Yorrick
Posts: 16
Joined: Tue Oct 14, 2008 12:40 am

Using import/wfopen instead of read

Postby Vladimir_Yorrick » Mon Sep 16, 2013 12:50 am

Hi,

Can someone please help me with an efficient use of import or wfopen instead of read in:

Code: Select all

wfcreate a 1995 2015

for %sheet sheet1 sheet2 sheet3 sheet4 sheet5
read(t,d4,s={%sheet}) data.xls 5
next


The code above is nice since it automatically reads in the variable names (located in column c). FYI, '5' is the number of rows of data.

I'd like to not use read, which will also let me use a .xlsx instead of .xls file.

Thanks in advance for any suggestions.
Attachments
data.xls
(42 KiB) Downloaded 116 times

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

Re: Using import/wfopen instead of read

Postby EViews Gareth » Mon Sep 16, 2013 7:28 am

Code: Select all

wfopen data.xls range="Sheet1!c1:x9" byrow
Follow us on Twitter @IHSEViews

Vladimir_Yorrick
Posts: 16
Joined: Tue Oct 14, 2008 12:40 am

Re: Using import/wfopen instead of read

Postby Vladimir_Yorrick » Tue Sep 17, 2013 5:10 pm

Thanks for the suggestion.

A couple more questions:

1. How can I make it read in or create the dates (i.e. 1995 2015)?

2. I actually have more like 40 worksheets. How would I make it import the data (all located in the same cell locations) in all sheets in as few lines of code as possible, since

Code: Select all

wfopen data.xls range="Sheet1!c1:x9" range="Sheet2!c1:x9" range="Sheet3!c1:x9" range="Sheet4!c1:x9" range="Sheet5!c1:x9" byrow

doesn't do what I want it to do and

Code: Select all

wfopen data.xls range="Sheet1!c1:x9" byrow
wfopen data.xls range="Sheet2!c1:x9" byrow
wfopen data.xls range="Sheet3!c1:x9" byrow
wfopen data.xls range="Sheet4!c1:x9" byrow
wfopen data.xls range="Sheet5!c1:x9" byrow

opens 5 separate workfiles when I'd like all the data to be imported into the one workfile (plus, with aropund 40 worksheets it's gonna take 40 lines in the program?

Thanks again for any suggestions.

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

Re: Using import/wfopen instead of read

Postby EViews Gareth » Tue Sep 17, 2013 5:21 pm

The code I provided does read in the dates.

Use IMPORT rather than WFOPEN to import into an existing workfile.
Follow us on Twitter @IHSEViews

Vladimir_Yorrick
Posts: 16
Joined: Tue Oct 14, 2008 12:40 am

Re: Using import/wfopen instead of read

Postby Vladimir_Yorrick » Wed Sep 18, 2013 10:12 pm

Thanks very much Gareth for your suggestion.

This does what I want it to do:

Code: Select all

wfopen data.xls range="Sheet1!c1:x8" byrow
import data.xls range="Sheet2!c1:x8" byrow
import data.xls range="Sheet3!c1:x8" byrow
import data.xls range="Sheet4!c1:x8" byrow
import data.xls range="Sheet5!c1:x8" byrow
'import data.xls range="Sheet6!c1:x8" byrow
'import data.xls range="Sheet7!c1:x8" byrow
'import data.xls range="Sheet8!c1:x8" byrow
'import data.xls range="Sheet9!c1:x8" byrow
'...
'import data.xls range="Sheet40!c1:x8" byrow
delete series* 'Delete the first three rows, the first being the date and the second two are left blank intentionally

But with around 40 Excel worksheets would you be able to suggest a more efficient or neater way of doing this?

FYI, I am currently doing something like:

Code: Select all

wfcreate a 1995 2015
for %sheet sheet1 sheet2 sheet3 sheet4 sheet5 ... sheet40
read(t,d4,s={%sheet}) data.xls 5
next

which, though it uses Read (which I am trying to avoid), takes only 4 lines. Can I use string lists in range? (something like range={%sheet}+"!c1:x8")

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

Re: Using import/wfopen instead of read

Postby EViews Gareth » Thu Sep 19, 2013 7:48 am

Code: Select all

%range = %sheet + "!c1:x8"
import data.xls range=%range byrow
Follow us on Twitter @IHSEViews

Vladimir_Yorrick
Posts: 16
Joined: Tue Oct 14, 2008 12:40 am

Re: Using import/wfopen instead of read

Postby Vladimir_Yorrick » Fri Sep 20, 2013 12:53 am

Hi Gareth,

I don't mean to be annoying but something is bothering me.

Could you please try the sequence of experiments below.

1. Using the data.xls file above, run:

Code: Select all

tic

wfcreate a 1995 2015

for %sheet sheet1 sheet2 sheet3 sheet4 sheet5
read(t,s={%sheet},d4) data.xls 5
next

!elapsed = @toc

statusline !elapsed seconds

The average of five runs is 0.08 seconds.

2. Now run:

Code: Select all

tic

wfcreate a 1995 2015

for %sheet sheet1 sheet2 sheet3 sheet4 sheet5
%range=%sheet+"!c1:x8"
import data.xls range=%range byrow
next
delete series02 series03

!elapsed = @toc

statusline !elapsed seconds


This averages 0.16 seconds, an average of 2.3 times slower than Experiment 1.

3.Now, save the file in .xlsx format (which is what I would normally do when not using 'read') and run:

Code: Select all

tic

wfcreate a 1995 2015

for %sheet sheet1 sheet2 sheet3 sheet4 sheet5
%range=%sheet+"!c1:x8"
import data.xlsx range=%range byrow
next
delete series02 series03

!elapsed = @toc

statusline !elapsed seconds


This takes on average 0.99 seconds, which is 14 times slower than Experiment 1.

The data.xls(x) file is just an example for this posting. Using my actual data (Excel) file, which has around 40 worksheets:
- Using 'read' averages 0.23 seconds
- Using 'import' (with a .xlsx file) averages 17.24 seconds, which is 88 times slower
- Using 'import' (with a .xls file) averages 1.55 seconds, which is 8 times slower.

I've noticed the statusline saying 'Uncompressing file' when I use 'import' with a .xlsx file.

I guess my question is: Why is using 'import' (which is what I want to use) so much slower than using 'read'? [Using import is far quicker with an Excel 2003 file but still considerably slower than using read.]

If this is just the way it is then no worries. But I am just wondering how I can make wfopen/import work for me since I wanna move away from using read, but when it takes almost 20 seconds to run a program it is a bit annoying.

Thanks for looking at this.

Vlad.

Vladimir_Yorrick
Posts: 16
Joined: Tue Oct 14, 2008 12:40 am

Re: Using import/wfopen instead of read

Postby Vladimir_Yorrick » Wed Sep 25, 2013 4:48 pm

Hi,

I'm hoping to get some advice on using 'import' with Excel 2010 (.xlsx) files.

With Gareth's help, I can import my data (very straighforward stuff/arrangement, nothing tricky, all 'tables' of same dimensions) without problems.

But it is slow. It takes almost 20 seconds to run the program where all it does it import the data.

The one thing I notice is, whilst importing, the statusline says 'Uncompressing file...'.

'Import' is a lot quicker using the same file but saved in an .xls (as opposed to .xlsx) format. Using the old 'read' and not using 'wfopen/import' altogther runs the way it should ... blink of an eye.

What am I not understanding?

Thanks in advance.

Vlad.

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

Re: Using import/wfopen instead of read

Postby EViews Gareth » Wed Sep 25, 2013 5:34 pm

.xslx is a compressed file format. .xls is not.

Try running the program whilst the file is open in Excel. Should be much faster (since Excel has already uncompressed).
Follow us on Twitter @IHSEViews

Vladimir_Yorrick
Posts: 16
Joined: Tue Oct 14, 2008 12:40 am

Re: Using import/wfopen instead of read

Postby Vladimir_Yorrick » Wed Sep 25, 2013 6:51 pm

Cool, thanks Gareth. It now runs in half the time (and having the Excel file open is actually what I'd prefer).

One thing though ... in my case using import is still considerably slower than using the old read command (with a .xls file). I wonder if it's because import caters for many more 'arguments' than read and it searches for those before running and since I'm reading my data using a loop with many import statements the time just adds up.

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

Re: Using import/wfopen instead of read

Postby EViews Gareth » Wed Sep 25, 2013 7:54 pm

It is just the increased intelligence of import over read. It takes longer to analyze the data. Especially if it is byrow.
Follow us on Twitter @IHSEViews

farrel
Posts: 67
Joined: Thu Sep 18, 2008 11:13 pm

Re: Using import/wfopen instead of read

Postby farrel » Thu Aug 09, 2018 7:29 am

EViews Gareth wrote:.xslx is a compressed file format. .xls is not.

Try running the program whilst the file is open in Excel. Should be much faster (since Excel has already uncompressed).


Dear Gareth,
I tried running the program, while the EXCEL file is open, but I've got error message saying "Sharing violation for file 'C:\USERS ....". There is no error with EXCEL being closed.

How did you mean to run Eviews program with EXCEL being open?

Andrei

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

Re: Using import/wfopen instead of read

Postby EViews Gareth » Thu Aug 09, 2018 7:55 am

Are you sure it was an Excel file and not a CSV file?
Follow us on Twitter @IHSEViews

farrel
Posts: 67
Joined: Thu Sep 18, 2008 11:13 pm

Re: Using import/wfopen instead of read

Postby farrel » Fri Aug 10, 2018 5:48 am

as I see it - Yes! at least I judge the file by extension, which is ".xlsx"

farrel
Posts: 67
Joined: Thu Sep 18, 2008 11:13 pm

Re: Using import/wfopen instead of read

Postby farrel » Mon Aug 13, 2018 11:28 pm

EViews Gareth wrote:Are you sure it was an Excel file and not a CSV file?


Gareth,

Whether your Eviews is working with opened EXCEL (.xlsx) file?


Return to “Data Manipulation”

Who is online

Users browsing this forum: No registered users and 7 guests