Is there an EXCEL spreadsheet link plugin?
-
Thanks TIG.
So how do I get at the data in SU that I want?
Is there some form of data export in SU I don't know about?
I have other questions but this is the fundamental one.
I'm fairly well versed on handling EXCEL text files in or out and manipulating the data in EXCEL. But SU internals are "Terra-incognito" to me and I do not know Ruby, nor do I fathom learning it at this stage of my life.
In my early days I was considered a master in BASIC and pretty good in COBOL or FORTH. Then a master of LOTUS-123 formulas and macros. Built my consulting career around that, but I haven't done any programming in over 20 years. I can almost follow a simple RUBY script, but most functions baffle me.
-
I just found Model-Report.rb by D. Bur.
Better than nothing, it gives me the list of layers and component names amongst some other data, but it has some serious non-functions.
It does not report Component/Group instances if the comp/group is nested in some other Comp/group, and that is where most of my Comp/groups reside.
It reports all layer and material areas as Zero. No volume data.
It does not report component description, only name and instances.
And for some unfathomable reason, it puts the report file in the Plugin folder, not the SU default models folder. That took a while to find!It has not been updated since SU-5 (Apr 2005), so that probably accounts for the lack of some of the data.
That should be relatively easy to fix to SU-8 standards (but not for me to try!!)Oh, and do not put commas in any Comp or Layer name.
-
Find my ComponentReporter++ for more variety
-
@tig said:
Find my ComponentReporter++ for more variety
Found it and will try it later today or tomorrow.
Many thanks.
-
Me again...
TIG, that is impressive.
However, if you know me, there IS a "but"
I have a few queries and a few suggestions to improve it, if you can bear with me.
In the Components Report;
What is GUID?
What can I do with it?In the Instances Report;
There were about 20 layers missing. They all had stuff in them.
The list was not in any sort order. Not a big deal, but I think it should be.
What is SUB-MAT?
In my report they were all "333" so what do I make of that?
Your dimensions are all in Inches, but the Area is in meters. That is an incongruity.
Any plan to include solid volumes?In the Parentage Report;
I figured out what it reports, but the presentation is very difficult to understand what is actually in any nested group/comp. Perhaps a line separation between nests would help, and a layer name at the topmost nest would make it clearer.In general, splitting into 3 reports makes it hard to get a picture of the model. I would suggest amalgamating common report elements or at least placing relational ID numbers and/or layer names on each component data line, then I can amalgamate as I see fit.
I have combined all 3 reports (of my boat model) and D.Bur's report in 1 spreadsheet that I am posting here, unedited except for column width. Compare them, and notwithstanding DB's limited or null info, his presentation is good.
I have a few ideas on a report layout that I would share with you, if you want.
-
This is really an example of what can be done and you adjust it to do what you need...
A GUID is the object's ID from the SKP database you don't have to get/use it!
Layers used by components get listed but not others [it would be possible to list objects by layer - then you might get groups and raw geometry too...]
The tool was written before simple volume reporting became possible in v8...
If I recall SUB-MAT is the material of a sub-component - do you have a material named '333'.
Again, you can adjust the outputs into whatever units suit you by recoding a few lines...
The parentage report is complex but it's really showing how you can extract data you might want - you are unlikely to want all of it ??What do you want to extract exactly?
Having a road map makes getting to the answer easier... -
I realize that I can manipulate the data as I see fit, having a good knowledge of EXCEL.
But some of the component info split between reports is difficult to manipulate without a single unique relational value. SU does provide a comp ID #. Does SU have an ID number for groups?As for the inch/meter aspect, I don't see why you can't capture the data in the current models units.
I do not have a material in this model called 333. In fact every face is painted front and back. The front is usually one of two wood textures, and the backs are all dark gray to avoid shine through at the edges. Look on page 4 (All Data) of the spreadsheet for a materials list. Most of it is [Wood_Floor_Light] Line 103 and all the backs are Dark Gray Line 104.
Here is a preliminary list of much of the data I would like to see (and can use), considering the types of models I usually create. Others may have different needs.
For EACH component and group. This is not necessarily a single report, but if split, the ID is critical to build a relational database.
Name, Author, total Count in model, ID, Description, Bounding Box dimensions, Offset of bounding box centroid to origin (X,Y & Z), total face/surface area, volume (if a solid), list of materials used, drawn on which layer, list of nested comp/groups (name and ID only), contains X # of non grouped entities (not for solids).I would use that last item to ferret out problems, as I tend to make everything a solid, if I can.
For each Layer;
Layer name, list of comps & groups on that layer (name & ID only)Then the usual model stats, as D.Bur depicted.
And I could pee in my pants if you could provide the Origin Offset X, Y & Z of the mass centroid of any solid.
That would be super useful for anyone doing serious mechanical design and needs to calculate the center of mass/balance for airplanes and boats, assuming they would go to that level of detail (I do). I'm attaching a compressed (RAR) file of an earlier WIP of the boat so you can see the level of detail I draw. It was requested in another thread last week.
From all that I can create any number/shape of reports as I need.
An early WIP version. The EXCEL sheet has more stuff referenced in it.
-
NO... I mean manipulate the data exported via the Ruby script
-
@tig said:
NO... I mean manipulate the data exported via the Ruby script
Not sure I understand your question.
I would not manipulate the data elements with a Ruby script, other than formatting them properly. That tends to create a hard format for output. You could never know what the user might want. i.e. Do not put a units indicator on dimensional data (" for inches) just the raw number. I will know what units my model is in, and if you are consistent with a unit type, or can format the number in the current model units, I can easily work with that, but not with a units mix. Same with attaching the type of component (<instance>) to the name. Make it a separate CSV element, then I do not have to strip stuff from the raw data before I can use it.
Just export the raw data as a .CSV file. I have more dexterity (for me anyway) in EXCEL to create any format of report with raw relational data.
If you want, I can dummy up a few EXCEL reports I can use in my boat design and airplane models. That can take a few days though.
-
I mean customize the Ruby code so what it exports is more like what you want already - rather than try to manipulate the data in Excel after it's been exported...
-
@tig said:
I mean customize the Ruby code so what it exports is more like what you want already - rather than try to manipulate the data in Excel after it's been exported...
Like I said, there is far more flexibility to manipulate raw CSV data in EXCEL than you would have trying to be generic in RUBY for everyone. And even more-so, easier for you to script a raw data exporter than a formatted series of canned reports.
Here's a thought.
Create a fixed format CSV data exporter on 1 or more reports, and I will create a general relational DB from that data that anyone with minimal EXCEL knowledge can use to create their own custom reports.
I will give you full rights to it and I will also write documentation. The spreadsheet will automatically import the CSV files from a consistent folder you put them into, format the data and setup the relational headers. All the user needs to do is select which data he wants to see in an output report and create any manipulation formulas as needed. In addition, he can add supplementary data to various data sets (ie: density associated with specific materials) to calculate true weight, assuming you provide Solids volume.
In that way it can be a "live" report that updates every time the data is exported from SU and the EXCEL sheet is recalculated. Basic tasks would be Macro driven, such as importing CSV files, and specific report printing.
I've done stuff like this before, mind you with LOTUS 123 a long time ago, but EXCEL has more functionality now. Even so, it is not a trivial task and would take a few weeks at least for me to perfect it.
If you are interested, PM me and then we can communicate more efficiently via e-mail, rather than though SketchUcation.
-
OK...
Let's assume we have a way of exporting lots of SKP data to a CSV...
Can you compile a list of like info you want to then extract from the CSV into another formatted version...
For example I'd see all of the basic stuff like location, bounds-x/y/z, face-area, instance-volume etc to be in inches and then converted post-export to sqm or whatever...
The data will also vary by 'type' of object - obviously an edge doesn't have an area or volume...
The material data for various thing can be used to assign density etc, we can also split material data in RGB/alpha/texture etc - this is basically what an exporter like my OBJ exporter does but that formats the info into an OBJ format not CSV...
We need an agreed format for the CSV data listing[s] so the columns can be readily accessed in Excel - the first entry of the Row can give us the exported object's type...
I'll make a list you make yours and we can then discuss it 'off-forum'... -
Since I do not know what data is readily available in SU for export, you have my preliminary list above.
Essentially, I would say "everything" but we both know that is not practical, as some stuff is basically useless to create a report on. (ie: which layer is on/off or its colour at time of export)
How's about you publish a list of everything you can export to a CSV here in this (or perhaps a new) thread, and poll the general unwashed as to relevance and usefulness. Then we can eliminate the unnecessary, and group the list logically.
Or if you don't want to make an all encompassing list public at this time, we can do it offline first.
I'm easy either way.Also, you might think about publishing a very limited list of data elements that a user can update, such as Layer, Component or group names that can be exported back to SU to update the model. It is easier to make that sort of text edits in EXCEL than in SU, especially if there is a lot of corrections to make. An SU updater would be an add-on update to the spreadsheet after the Reporter is vetted by SU users.
-
Here's a start... CSVexportProtocols.txtWhat else do you want to extract - most things are accessible somehow...
-
Took a very quick look at the file.
Obviously there is a lot more data than most users (especially me) would be interested in, and some of only cursory curiosity. Then the hard core users could want and make use of those rarefied bits.
We will end up with perhaps 99% of it, but let's start with a subset of the 16 entity types, the one's I think would be of greater interest. Later the other entities can be incorporated by simply expanding on the basic framework of the exporter RB and the reporter spreadsheet, as requested by users.
If you can give me a list of the data elements available for each of the following 8 entities, even if of dubious use (for now) then I can mockup a spreadsheet with a relevant selection of those elements.
Model
Face
Group
Definition
Instance
Material
Layer
TextThe real key here is some unique common identifier so a relational DB can work.
I am going to assume the "REF" is that identifier. So if say, component ZZZ says its "parent" is XXX, then the REF of XXX should be the data element, not the name. The name is useful as well, but not necessary. The RDB function will replace the parent as XXX not its REF. Again, I'm assuming that ZZZ is nested within XXX.Also, any NUL data in an element field must contain a value of some kind, zero or "-" to avoid misplaced columns which will royally screw things up.
Replacing embedded "," with ";" is not a problem for me. However you can also substitute one common alternative extended ASCii code (128-254) for any embedded "," (ASCii 44) that I can search and replace with a "," in the RDB. It should be innocuous to an EXCEL CSV import.
After I see the list of data elements and understand what each really represents (with your tutelage), then we can see where some can be eliminated, or others combined.
At that point you can produce a raw data CSV exporter while I mockup the Spreadsheet.
We will also need 1 or more varied geometric SKP's to test with.
Once we are in sync, I can start defining the detailed spreadsheet functionality.
Then it can be expanded in stages to meet any demand. -
I've started a mockup to test some concepts for the reporter.
I just ran into a very frustrating limitation in EXCEL that LOTUS123 had no problem with. I've queried some expert XL'rs but no reply yet. The available help does not address this at all.
When XL imports a CSV file it opens a new window, rather than import into the open sheet at the time. That file import also puts me into the new window, where I then have to macro copy the CSV data over into the spreadsheet where I want it. Then go back to the CSV file window and close it, which will return me back to the spreadsheet.
I need to define the sheets by name (in a cell reference) to the Macro. There is a "range("name")" function that I use for selecting the files to import, BUT the window switching macro command "Windows("windowname").activate" will not accept it. It wants a hardcoded window name in the syntax, and that would blow me out of the water to give the reporter the flexibility to work with any SU model.
There is another way to do this using a CSV dataset in a TXT file. While that would put the data exactly where I want it, the user has to put up with a data formatting dialog for every CSV file. That is a non-starter.
-
You insert the CSV just the once as a 'linked object' so the XLS updates when the CSV changes... but the XLS formats and uses the CSV data as it wishes...
I think you can have the entire CSV load into an unused worksheet and extract data from that into other worksheets in the same XLS as desired...
I haven't had a chance to think any more about this but I will get back to you... -
Had not thought about linking. Never tried it, but tomorrow I will bone up on it.
The auto-update is intriguing.
thanks. -
I have done this before with a CSV linked into a XLS.
The SKP exporter keeps the CSV up to date and the XLS displays the latest CSV data on opening or can be updated when open [like an Xref].
Because you can't format a CSV you do all of your formating in the host XLS and manipulate cells etc as needed.
As I said you need at east two worksheets in the XLS.
One sheet containing the CSV 'insert' link and the other sheet reading data from it with formatting, formulas etc as needed. Obviously you could have several worksheets presenting different aspects of the CSV's data if desired... -
My concept is a workbook with several sheets, maybe 20+ with all 16 entities reported.
Several CSV data sheets each containing the raw CSV data and a slightly massaged version of that data used for the database. I am leaning towards 1 sheet per entity type, but will combine a few where practical.
A main cover sheet for user controls and macro cell references, plus user guide.
Several canned reports, 1 per sheet. So far, 5 reports.
and as many added user defined reports as wanted, 1 per sheet.The report sheets are divided in 2, the far right side is for the raw data extract from the CSV data sheets, specific to the report in sorted order.
The left side is for the formatted output report.In addition, my concept would open a new CSV import window, from which I transfer the data over to the CSV data sheets, then close that window. I have not yet considered the linking aspect you mentioned yesterday, so this may change.
I was going to make the sheet flexible enough to handle multiple models, 1 at a time, but this am I realized this won't be practical.
So there will be a master template, from which is spawned a copy for each SU model the user wants. The template would be user unalterable, but the copies will just protect critical parts of the sheet. I will publish the model sheet passwords for those who wish to be daring, but not the master template password. Only you and I will know that.
Until I get a solution to the windows switch macro, I am defining several other macro models to do specific housekeeping tasks.
Advertisement