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,

Could you have your script re-create the master? As long as you didn't adjust the original master that is.

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.

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.

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.

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,

Waterhead

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

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.

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,

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.

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
 

Don't forget about the "CREATE FILE TARGETFILE"

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!

CREATE FILE TARGETFIlE
-RUN
before you do the other stuff.

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