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] How to create an FTM file
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] How to create an FTM file
 Login/Join
 
Member
posted
Currently, I want to create a script that will automatically upload csv(or other valid data type) files, and replace the data for a previously created synonym.

Example: I have a synonym called phone_book. It contains phone numbers from my phone and information about them (how long I've had them in my phone, how often I talk to them etc). I have a csv keeping track of it on my local machine. Today, I added a new person to my phone, so I added them to my csv. My script then uploads the csv to the correct folder. However, phone_book is based on datrec type (phone_book.ftm).

So what I want to do is take my csv file, and replace the data currently in the datrec file, so my synonym will now read the new data. I know I can do this with the upload functionality, but I want to do this with my current upload script.

Things I tried:
I found the app movefile command. If I do a destination extension of FOCTEMP, it will move the file, and change the extension to .ftm. However, that is all it does. It doesn't do any formatting of the .ftm, so the data inside gets messed up.

So the question is, how do I create a real datrec/.ftm/binary file off of a csv (or other data type), in webfocus code? Does anyone know how the regular file upload does it?

This message has been edited. Last edited by: FP Mod Chuck,
 
Posts: 8 | Registered: July 06, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Could you have your script re-create the master? As long as you didn't adjust the original master that is.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1101 | Location: Toronto, Ontario | Registered: May 26, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
If the csv is on your disk, you should be able to create a master file for it with SUFFIX=COMT. Then write a TABLE request that reads the csv, and ON TABLE HOLD FORMAT BYNARY.


WebFOCUS 8206, Unix, Windows
 
Posts: 1716 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
quote:
Originally posted by dhagen:
Could you have your script re-create the master? As long as you didn't adjust the original master that is.

Well the issue is exactly that. I want to be able to change the master however I want, rename titles, change column order, everything. And then have the new data fit that same master. Otherwise, you are correct I would just recreate the master.

Its always the details that make things tricky.
 
Posts: 8 | Registered: July 06, 2018Reply With QuoteReport This Post
Member
posted Hide Post
quote:
Originally posted by BabakNYC:
If the csv is on your disk, you should be able to create a master file for it with SUFFIX=COMT. Then write a TABLE request that reads the csv, and ON TABLE HOLD FORMAT BYNARY.


I "think" that would at least get me closer, however I'm a little unsure how to create that master file using webfocus commands. Is that just a CREATE SYNONYM command? or what could I look at that would help me with that? And can I do it without overwritting my old master file changes.
 
Posts: 8 | Registered: July 06, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Create Synonym assumes you have a DBMS that has a catalogue of your columns. A CSV doesn't have such a thing. So, you may have to open the editor and start typing.

If you have access to the reporting server, you could try to create a master file off of the CSV file and save it. I think it'll end up in /apps/foccache.

If you've customized the target master file, ON TABLE HOLD will erase all of them. So, I'd create a cluster synonym that references that HOLD.MAS and put the customized fields in that. This means your HOLD.MAS will keep getting replaced, but the FINAL.MAS that references HOLD.MAS will keep the changes in it.

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


WebFOCUS 8206, Unix, Windows
 
Posts: 1716 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Waterhead

You should be able to use the upload data on the csv file and WebFOCUS will automatically create the master file.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1694 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Member
posted Hide Post
quote:
Originally posted by FP Mod Chuck:
Waterhead

You should be able to use the upload data on the csv file and WebFOCUS will automatically create the master file.


Because I want to be able to do this in my webfocus script, the upload data is not an option for me.
 
Posts: 8 | Registered: July 06, 2018Reply With QuoteReport This Post
Master
posted Hide Post
This is the code we use to create a master file from a csv file:
  
CREATE SYNONYM [application folder]/[masterfile name] FOR [file path to csv file] DBMS DFIX PARMS ' CONNECTION <local> DELIMITER "," HEADER YES ENCLOSURE """""" RECORDS 0 CHAR_EXT 0 PRESERVEFRMT Yes CDN COMMAS_DOT ' DROP CODEPAGE 1252 CHECKNAMES UNIQUENAMES
END


This will drop any existing synonym (if exists) and create a new one.

Since IBI only wants you to click...click...click... and doesn't want you to know how to code, I couldn't finds any documentation on it. I had to enable traces on my reporting server, import in the csv file as Chuck mentioned, then stop traces to see what the underlying code is.

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


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 493 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Good One


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1694 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Member
posted Hide Post
quote:
Originally posted by Hallway:
code]
CREATE SYNONYM [application folder]/[masterfile name] FOR [file path to csv file] DBMS DFIX PARMS ' CONNECTION DELIMITER "," HEADER YES ENCLOSURE """""" RECORDS 0 CHAR_EXT 0 PRESERVEFRMT Yes CDN COMMAS_DOT ' DROP CODEPAGE 1252 CHECKNAMES UNIQUENAMES
END
[/code]



This worked great for me! Thank you. Now to figure out how to overwrite the data without overwriting the synonym itself.

(Edit) Cluster Synonym on this synonym is unfortunately not an option for me. I need it to be one synonym somehow.
 
Posts: 8 | Registered: July 06, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Sounds like you'll have to start brushing up on MODIFY syntax. I'm a little rusty but it should look something like this.

 
TABLE FILE CSVFILE
PRINT fieldname1 fieldname2
ON TABLE HOLD
END
-RUN
MODIFY FILE TARGETFILE
FIXFORM FROM HOLD
DATA ON HOLD
END
-RUN
 


WebFOCUS 8206, Unix, Windows
 
Posts: 1716 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
Don't forget about the "CREATE FILE TARGETFILE" Smiler
 
Posts: 3045 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Member
posted Hide Post
You guys rock, this is all working for me.

One more thing I have to figure out. How to wipe the TARGETFILE and only put in the new data. Currently its just appending the new upload to the end. Im looking through modify syntax currently, but if anyone knows off the top of their head, I would appreciate it!
 
Posts: 8 | Registered: July 06, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
CREATE FILE TARGETFIlE
-RUN
before you do the other stuff.


WebFOCUS 8206, Unix, Windows
 
Posts: 1716 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
Done, and in working order. Thank you everyone for your input.
My final script, for future notice, looks like this:

-* Create a new synonym based on the uploaded csv
CREATE SYNONYM filepath/filename FOR filepath/filename.fileextension DBMS DFIX PARMS ' CONNECTION DELIMITER "," HEADER YES ENCLOSURE """""" RECORDS 0 CHAR_EXT 0 PRESERVEFRMT Yes CDN COMMAS_DOT ' DROP CODEPAGE 1252 CHECKNAMES UNIQUENAMES
END
-RUN
-* re-create the ftm file, to delete all data inside
CREATE FILE filepath/targetfile
-RUN
-* Holdfile the data in the synonym
TABLE FILE filepath/filename
PRINT *
ON TABLE HOLD
END
-RUN
-* Then add actual data into the ftm
MODIFY FILE filepath/targetfile
FIXFORM FROM HOLD
DATA ON HOLD
END
-RUN


-* DELETE the csv and the synonym created from it, so only the old synonym remains
APP DELETEF filepath filename fileextension -*the synonym created
APP DELETEF filepath filename fileextension -*the csv uploaded
 
Posts: 8 | Registered: July 06, 2018Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] How to create an FTM file

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