![]() |
||||||||||||
Go ![]() | New ![]() | Search ![]() | Notify ![]() | Tools ![]() | Reply ![]() | ![]() |
Member |
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, | ||
|
Virtuoso |
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 | |||
|
Virtuoso |
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 | |||
|
Member |
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. | |||
|
Member |
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. | |||
|
Virtuoso |
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 | |||
|
Virtuoso |
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 | |||
|
Member |
Because I want to be able to do this in my webfocus script, the upload data is not an option for me. | |||
|
Master |
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,
| ||||||||||||||
|
Virtuoso |
![]() Thank you for using Focal Point! Chuck Wolff - Focal Point Moderator WebFOCUS 7x and 8x, Windows, Linux All output Formats | |||
|
Member |
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. | |||
|
Virtuoso |
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 | |||
|
Expert |
Don't forget about the "CREATE FILE TARGETFILE" ![]() | |||
|
Member |
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! | |||
|
Virtuoso |
CREATE FILE TARGETFIlE -RUN before you do the other stuff. WebFOCUS 8206, Unix, Windows | |||
|
Member |
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 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 | |||
|
Powered by Social Strata |
![]() | Please Wait. Your request is being processed... |
|