Community Tip - New to the community? Learn how to post a question and get help from PTC and industry experts! X
Hello,
I am required to make changes to a large number of CGM files. Below I will list whats need to be done and the areas I am stuck on. I am to do the following:
Tasks
-Remove unwanted elements
-Insert Text Input #1 at certain location (Data from Spreadsheet)
-Insert Text Input #2 + Input #3 at certain location (Input #2 from spreadsheet and is CGM filename - Input #3 is Data is from spreadsheet)
Current Progress
The removal of unwanted elements I have just completed using the following (Unfortunately the elements to be removed are on the same layer as the wanted elements so i cannot simply delete a layer - hence the long workaround) :
Macro CustomSelection
SELECT RECTANGLE 0 0 650 8.2 with_partial add direct
SELECT RECTANGLE 0 0 7.6 600 with_partial add direct
SELECT RECTANGLE 837.2 0 843 600 with_partial add direct
SELECT RECTANGLE 0 591.7 842.6 600 with_partial add direct
SELECT RECTANGLE 632.6 0 842.6 83.2 with_partial add direct
DELETE SELECTION
End Macro
The next step is to add in 2 text elements which will contain Input #1 and Input #2+#3 (I am confident I can add empty text elements myself).
The Difficult Bit:
Prior to this (or after depending which you recommend happens first) I need to retrieve data from an excel speardsheet. The name of the CGM file is also in a cell on a row within the Excel sheet - we can use this as an identifier to ensure the correct information is transfered to the correct CGM, this filename is also Input #2.
I have no idea if/how Isodraw can read a excel document. I know Perl can and I know Isodraw can execute Perl commands. Below is the research I have found so far:
Running a Perl Script Within Isodraw
http://structuredauthoring.org/arbrtext-isodraw/running-a-perl-script-in-arbortext-isodraw/
Accessing Excel Document via Perl
I am no programmer by any means, I am new to macros and I doing what I can where I can but I feel obtaining the cell data from a spreadsheet is beyond my current ability. If anyonce can assist at any step along the way, it would be greatly appreciated! I believe many individuals would benefit from this knowledge too, now and in the future as this is pretty powerful stuff!
Thanks again.
Alan
Looking through your list, a couple thoughts, as well as questions arise.
Question:
What is the current format of the Excel file? Can you provide a few sample lines so that we can see a sample of the data? (Attach the Excel if possible).
What versions of Excel are you working with? Might be relevant.
Can you provide a sample CGM as well?
Thoughts:
You can actually run basically anything that can be launched from a line command using IsoDraw. It basically just kicks up a CMD window and enters your launch command. The shortcoming of this command is that I'm not aware of a way to monitor the external programs closure. So, if you kick off a script to convert, and the next immediate command in IsoDraw is to process the result, you might get bad results. In situations like this I've tried the worst-case scenario approach and put a wait timer command in.
The batch command will allow you to read a text-based file. It has just been documented in 7.1, so I haven't played with it much other than the sample posted. It has some great possibilities, but I'm sure there is a bug or two.
When I did work with the batch command, make sure the text files are clean of special characters. Characters like ", ', or even + can cause issues. If you need these in, I suggest using a replacement character (basically a custom entity) and do a replace when you bring the string in.
Also, the text files are easier to work with if you use a delimiter. For me, I have a tendancy to use the tilda, but feel free to insert your creative side.
Thanks for spending the time to review my question. I dont think my employer would like any files posted. I understand this may cause difficulties at some stage I will try to answer any questions with regards to the details of the CGM and Excel doc.
The Excel doc is an .xls I have the file and can do further exporting if this makes an easier format. Ideally it would be good to just look at the .xls provided but, I am currently struggling getting Perl to execute a Perl script.
The excel doc has about 5 worksheets, 17 columns and 256 Rows. I need only information from 1 worksheet, 3 columns (but obviously data from all rows in them columns).
I came accross someone making a macro inside excel to export information into a text file - I could look into this and learn how to generate a macro in excel. If this makes it easier to reach the end result, I am assuming it wont be much trouble to export out the information from 3 columns and someone how format the text file so that it keeps the structure of the excel sheet.
FileName Input#1 Input#2
FileName Input#1 Input#2
FileName Input#1 Input#2
Via the above format I assume its easier for 'us' to gain the information accurately avoiding information being displayed on incorrect CGM.
My thoughts on the script process:
Again many thanks
Alan
Update 1
I have managed to use Perl to print in the command window some cell contents, this is good progress. I will try and write certain variables and just get snippets of information rather than a bucket load. I will keep this updated as I progress. My main concern is if a variable I set in the Macro (CGM FileName) can be used in the Perl file and if variables can be set in Perl back and brought back into Macro. Any input is encouraged.
Thanks
Message was edited by: Alan Porter
Update 2
Ok I have nearly finished the Perl Code. The following steps are required:
1.Get the Perl Script to retrieve a defined variable from Isodraw (filename of the CGM)
2.Allow isodraw to retrieve the variables generated from the executed Perl Script
And I think that will be it!!!
Any suggestions/ideas please share.
Thanks
Alan
Message was edited by: Alan Porter