Automatic Export to Excel - Using the resulting file as a Data Source in Excel (EG5.1)

[paulrockliffe]

I'm exporting a table from EG 5.1 to a folder as a .xlsx file.  I'm using the Export as a Step in Project option so that the .xlsx file is created automatically.  This file is the basis for some data analysis and presentation in Excel using pivot tables and filters to let users select the information that's relevant to them.

I'm trying to use the Data Connection feature in Excel so that I don't have to do anything post-export to have new data drop into the analysis, see here; https://support.office.com/en-gb/article/Connect-data-in-another-workbook-to-your-workbook-3a557ddb-...

I'm getting an error from Excel that basically says it can't do it; "The query did not run, or the database could not be opened."

I've tested the Excel bit with a dummy spreadsheet and it works perfectly, so it looks like the problem is in the way EG is exporting the spreadheet, but I can't see anything in the .xlsx sheet that might be causing a problem.

Does anyone have any experience of this and any ideas what might be happening?  It would be really useful to be able to get this working as it'll fully automate the analysis from start to finish, which would be neat.

Thanks in advance!

[paulrockliffe]

I used the built-in Document Inspector in Excel and it reported that th espreadhseet was using the "Absolute path to the workbook".  I don't know exactly what that means in the context of an exported spreadsheet that doens't have links to anywhere, but I was given the option to fix the error and that has sorted the problem.

So that leads to a follow-up question; does anyone know why and how I can stop EG exporting the files this way?

And given that it stops a very useful feature being automated, is it a bug that would be better being fixed or is it like that for a good reason?

Thanks!

[paulrockliffe]

I couldn't see any options in EG to fix this, but exporting as a .csv does work.  That option is limited as you can't import the data as a pivot table, but for my use that isn't a show-stopper.

I would like to be able to use the .xlsx option though if possible, so would still welcome any knowledge on this?

[Kurt_Bremser]

This seems to refer to the way how the different XML files in the zipped workbook (xlsx) address each other.

After taking a look at the internal structure of .xlsx files created by EG 6.1 and 7.1, they seem to use relative paths.

This might be something that 5.1 does (as it is the first EG version to support the xlsx format, IIRC).

Could you please do the following:

open the xlsx with a proper compression utility (7zip?)

extract [Content_Types].xml

open with a text editor

look at the first PartName= entry and its contents

In my case, I see /xl/workbook.xml, indicating a path relative to the root of the internal directory tree of the xlsx.

[paulrockliffe]

Thanks for the reply, I can't do any of that testing unfortunately, I'm at work without access to Zip or similar.

Your explanation sounds reasonable and would explain the error and also that Excel was able to fix the problem.

I wonder why EG works this way, when Excel is identifying it as a potential problem with the files?

From what you say it doesn't sound like it can be resolved and I'll just have to keep using .csv files to feed my analysis work.

[Kurt_Bremser]

Well, EG 5.1 is fairly old, and as EG 5,6 & 7 are interoperable with SAS 9.2, 9.3 & 9.4, I would recommend upgrading EG (or maybe there's a hotfix for 5.1) and trying that.

I tested both 6.1 and 7.1, and in both "Export as a Step" produced a file that opened without problems in Excel 2010.

[paulrockliffe]

I'm not sure if you've misunderstood my problem, but to clarify, the exported file will open without any problem in Excel, I'm using Excel 2013.  The problem occurs when I use the Data Connection in a separate Excel file to access the data in the exported file.  I'm doing this so that I can build an analysis dashboard that is kept up to date with minimal input, so I can't do anytihng with the exported file as it'll be overwritten when the data is updated.

I can't upgrade EG, though we are supposed to be jumping to the latest version in the next 12 months or so, maybe that'll sort it out.

[AhmedAl_Attar]

Hi Paul,

I have seen developers using Proc Export, to update sheet(s) within an existing Excel Spreadhseet/Workbook witout overriding the entire workbook. And within that same Workbook/Spreadsheet, there were other sheet(s) that referenced cells from the sheet(s) that gets updated by SAS via Proc Export.

Things of it as having Detailed Data, Summaries and Dashboards all in one Template Workbook, that gets updated periodically or whenever the detail data changes.

This approach would allow for Scheduled Batch execution of your EG Project!, and avoids the need to setup Connection and dependencies on/in other workbooks

Just a thought,

Ahmed

[AhmedAl_Attar]

Hi,

I have couple of questions to ask before suggesting an alternative approach

1. Are you using EG 5.1 with Metadata Server, i.e. as a client app for SAS BI/EBI platform?

2. Do you have SAS Add-In to Microsoft Office (AMO) installed?

If you answered "YES" to both, then I would recommend Using the SAS AMO in Excel, to extract the required data directly from SAS, and avoiding EG all together. This way you are working natively in Excel.

If your data has to go through a ProcessFlow within EG, you can convert the ProcessFlow into a StoredProcess and call it from Within Excel via the SAS AMO.

Try this search link, it should return several links to papers related to SAS AMO http://www.lexjansen.com/search/searchresults.php?q=Add-In%20to%20Microsoft%20Office

Hope this helps,

Ahmed

[paulrockliffe]

Hello, thanks for that it's really interesting. 

I don't know the answer to Q1, but I suspect it's a No and Q2 is definitely No.  I don't have any scope to install anything unfortunately, the best I can do is lobby for the AMO to be added.

However, the nature of my work is that I'm doing data manipulation in EG then using this as the basis for Excel tools that summarise and analyse the data for a large and diverse group of interested parties.  As a result the end product has to be independent of any fancy stuff as most users won't get things like AMO if it became available.

Stored processes and scheduling the data updates is the next bit to look at, it would be great to set it up so it looked after itself completely!

[ballardw]

When I see things like others using exported Excel files for the analysis I generally ask "what analysis"? If it is the same stuff time after time then I suggest doing the analysis/manipulation in SAS and providing it too the user so they don't have to do extra work. I have found people spending much time redoing a series of pivot tables that were replaced by some Proc Tabulate or Freq output.

[TomKari]

<2¢ worth> I agree with you totally, but I've never found a solution to the problem. Everyone thinks they're a "wizard" in Excel, and will jump through incredible hoops to get frequently wrong results.

It's Microsoft's world...they just let us live in it.</2¢ worth>

[paulrockliffe]

There are practical reasons why this needs to be done in Excel; it's to be delivered to a large number of people with different interests and also the need to subset to get to 'their' data in a lot of cases.  The data has to be delivered in Excel as that's the only software that the users have that can do any more than just view the results.  Excel is much more flexible (certainly than 5.1, I've not seen later versions) in terms of being able to deliver results that can be tweaked on the fly by the general population.  

The analysis I'm doing in Excel is nothing more than building pivot tables and then using slicers to filter the data.  That approach means I'm not producing a table for every interested party - I can't see an easy way to do that in EG other than running a separate query for each subset, which isn't feasible when there's a large number of subsets required - but peopel can quickly filter and modify the data to get to the answers they're looking for.  I can't see any provision in EG for dynamic reporting, but if it's there, please let me know!