Transfer Excel data into Isodraw

[acs_alan]

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

http://www.opensourcetutorials.com/tutorials/Server-Side-Coding/Perl/writing-excel-files-perl/page1.html

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

[thendricks]

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.

[acs_alan]

1. Hi Trevor,
   

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:

1. Open CGM
   
2. Remove unwanted elements (Done)
   
3. CGM Filename into VarFileName
   
4. Search xls for the same name as the VarFileName
   
5. Add the Row# to VarRow
   
6. Put cell contents of the required column from VarRow into VarInput1 and VarInput2
   
7. In Isodraw take the variables VarFileName, VarInput1 and VarInput2 and make the neccessary text elements at the required location (predefined).
   
8. Save (Batch?)
   
9. Repeat for next CGM (Batch?)
   

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