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,
June 26, 2019, 01:00 PM
dhagen
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
June 26, 2019, 01:01 PM
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.
WebFOCUS 8206, Unix, Windows
June 26, 2019, 02:13 PM
Waterhead
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.
June 26, 2019, 02:17 PM
Waterhead
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.
June 26, 2019, 02:43 PM
BabakNYC
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
June 26, 2019, 02:57 PM
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.
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats
June 27, 2019, 10:11 AM
Waterhead
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.
June 27, 2019, 11:25 AM
Hallway
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
Prod: 8202M1
Test: 8202M4
Repository:
OS:
Outputs:
June 27, 2019, 11:27 AM
FP Mod Chuck
Thank you for using Focal Point!
Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats
June 27, 2019, 11:49 AM
Waterhead
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.
June 27, 2019, 12:14 PM
BabakNYC
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
June 27, 2019, 12:46 PM
Doug
Don't forget about the "CREATE FILE TARGETFILE"
June 28, 2019, 11:45 AM
Waterhead
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!
June 28, 2019, 11:47 AM
BabakNYC
CREATE FILE TARGETFIlE -RUN before you do the other stuff.
WebFOCUS 8206, Unix, Windows
June 28, 2019, 03:26 PM
Waterhead
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