What's the best way / best practice to Read Excel File as a Source, in 8.2, which is Stored on a Shared Drive? The Excel file is a simple one tab, several columns, the format will not change.

This message has been edited. Last edited by: FP Mod Chuck,

Try Excel (via direct retrieval) in the Adapter list on the reporting server. Unless of course you can upload into a real database.

This can't reside on the WebFOCUS Servers. It resides in a folder on a Windows Shared Drive.

The WebFOCUS server needs to be able to access that file, so it needs to be in the path... are you on unix or windows for the WF? It needs to be a 'folder'you can navigate to... mounted directory or symlink or such.

In addition to that, in our experience Excel 2007+ files aren't great with parallel access to their contents.

WebFOCUS has to unzip the file to read it, but as far as I understand, it doesn't leave it unzipped and ends up doing so over and over. This can result in it overwriting a previously unzipped version that's still in use by a parallel running TABLE request on the same file, resulting in errors.

You might want to HOLD it to a FOCUS file, or better yet, a table in a proper RDBMS. Both performance and concurrent access will be a lot better.

How often does that excel file change?

What I'd suggest to do, instead of accessing the excel directly, "cacheing" it along the lines what colleague Wep5622 above is suggesting. What you want to do is make a batch job that reads the excel into a FOCUS table you hold on the server disk. Or if it is updated randomly, make a "refresh" report/schedule the user can invoke after saving (unless you have a sniffer and trigger there in the share)

That way, the "use" of the excel is 1:1 by the batch job only so the conflicts above get mitigated, and you can then run reports off the FOCUS table as you please.

The question with using cacheing is "how often does the data change" and "how often does it get accessed". Say you have a dashboard with 10 dropdowns each running a dynamic report joining a couple tables. Now running a SQL query as a batch once at night vs. 1000 users 9am running the same dashboard refresh does magic to load and response times.

Thanks Guys,

As far as the following quote goes, I need to start by getting WebFOCUS to read the file in the first place. Accessing it on the share drive seems to be the issue.

We're running WebFOCUS on Unix and my file is on a Windows share drive.

quote:

make a batch job that reads the excel into a FOCUS table you hold on the server disk.

Microsoft has an ODBC connection on Unix. I haven't tried this myself but that might be the approach for what you're trying to do.

Alternatively, it might be a lot easier for you to use a CSV file instead of Excel if that's an option.

Ok, is it actually a shared drive on a physical box, or is it a SAN drive (you could access otherwise)

So, then, you need to mount the share... something like:

mount -t cifs -o username=<share user>,password=<share password>,domain=<domain> //<ip address>/<share name> /mnt  

Depends on what you have available on your boxes, google-fu the specific versions, theres stuff to do to keep the mounts after reboot or if you need different authentication or stuff.

Thanks All,
I'll get with my Admins about this and let post my final results.

test 123