Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved] Dynamically Creating Master File from Inputted Excel Filename
Go
New
Search
Notify
Tools
Reply
  
[Solved] Dynamically Creating Master File from Inputted Excel Filename
 Login/Join
 
Silver Member
posted
We are trying to report off of Excel/CSV files that users have on their shared network drives. Currently, the html page has a browse file feature that the user can use to navigate to the appropriate Excel/CSV file. The reporting server has access to all of these shared drives. How can we go about creating a master file based on the inputted file name without having to go through the upload file utility in the webconsole? The files will not have a consistent format.

This message has been edited. Last edited by: mlhapner,


WebFOCUS 8.201m
Windows, All Outputs
 
Posts: 28 | Location: Indiana | Registered: June 08, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
You can use CREATE SYNONYM in a Fex. The syntax can be parameterized. Take a look at this post to see how it's done. http://forums.informationbuild...1057331/m/6537019096


WebFOCUS 8203, Unix, Windows
 
Posts: 1304 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Silver Member
posted Hide Post
I get a syntax error when I try to use "CREATE SYNONYM"??

 CREATE SYNONYM TEST_SYNONYM
FOR &INPUTTED_FILENAME
DBMS DIREXCEL 


(FOC1544) Syntax error: SYNONYM TEST_SYNONYM


WebFOCUS 8.201m
Windows, All Outputs
 
Posts: 28 | Location: Indiana | Registered: June 08, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
You have to give it more details such as what app directory do you want the synonym to be created in. So fully qualify your TEST_SYNONYM with something like ibisamp/TEST_SYNONYM.

Also, you have to tell it where to look for HEADERS and how many rows to sample.

Finally, you have to add an END to finish the syntax. I have an Excel file called sql_emp.xlsx in my downloads directory and wanted to create synonym for it in ibi\apps\techniques. The first row is the column headers. The sheet that contains the data I'm using is called Report1 in the Excel file. Here's what I did to create the synonym. You'll have to parameterize the values you want to substitute in your case.

 
CREATE SYNONYM techniques/test_xl
   FOR 'C:\Downloads\sql_emp.xlsx'
   DBMS DIREXCEL 
  DROP
   PARMS 'WORKSHEET Report1 HROWS 1 RECORDS 10 DBMS DIREXCEL CHECKNAMES UNIQUENAMES'
END 


WebFOCUS 8203, Unix, Windows
 
Posts: 1304 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Silver Member
posted Hide Post
BabakNYC,

Amazing! I was able to get it to work. I didn't know this capability even existed.

Thanks so much!


WebFOCUS 8.201m
Windows, All Outputs
 
Posts: 28 | Location: Indiana | Registered: June 08, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
The CREATE SYNONYM syntax is what's generated when you use the GUI for any DBMS/Data.


WebFOCUS 8203, Unix, Windows
 
Posts: 1304 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved] Dynamically Creating Master File from Inputted Excel Filename

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.