Is there a way to paste some data into a generic table drawing other than one cell at a time?

eric_pesty

I have a table of data generate in excel (30 rows x 5 columns) and I want to put this on a drawing, however I can't seem to find a way to paste data across multiple cells...

I can paste a bunch of data into one cell of the drawing table but that's not helpful... I refuse to believe it can't be done, am I missing something obvious?

I really don't want to copy/paste 150 times!

shawn_crocker

Haha!  I also can't seem to find a way to do this?

eric_pesty

@shawn_crocker
Thanks for trying! I got confirmation it's not currently possible, which is pretty crappy and disappointing...
My workaround was to paste each column into its own cell so I only had to paste 5 times. At least that way it's still text, it's just one row...

shawn_crocker

I have needed this from time to time also.  I get "agile" and think it is a great development process but sometimes a need seems just too obvious to leave out.

eric_pesty

Yeah it's interesting how every CAD program seems to re-invent tables seemingly "from scratch"!

You really would expect that if you select data from a table and paste into another table structure it would figure out what you are trying to do. Especially since copy pasting in Onshape tables (mostly) works for config/BOM tables!

bryan_lagrange

There used to be this:

https://forum.onshape.com/discussion/6100/onshape-bom-for-google-sheets-export-any-table-selection

I am not sure if it is active anymore.

eric_pesty

@bryan_lagrange
Interesting, I might have to check that out, although it seems like a very convoluted way to get to what I want...

It seems like a fundamental design limitation in the drawing tables: you can select multiple cells to apply formats etc... But you can't affect the data in a cell without first "entering" that cell. Even selecting a cell and hitting the delete key doesn't affect the data like you would expect so it's no wonder you can't paste across multiple cells.

bryan_lagrange

I was able to connect to google sheets and have that placed in a drawing. When google sheets is connected to Onshape it puts a .json file in a tab. In drawings go to insert bom table and select bom data to insert the .json file onto a drawing sheet.

eric_pesty

Hum,
Seeing you could import a JSON I was hoping to circumvent the google sheet part (it's not really part of my workflow here). It's easy enough to convert data to a JSON table so I tried that but I got an "unable to retrieve BOM data" error.

Part of the issue might be that it's intended to be a BOM rather than just a generic table... I'll have to dig into a it a bit more to see if I can spoof it...

EDIT:
@bryan_lagrange
Can you paste the raw JSON code it spat out in here (should just be plain text)? It looks like there are a number of ways to describe tables in JSON and it might be expecting a specific one...

Thanks,

bryan_lagrange

Here you go:

{"bomTable":{"formatVersion":"1.0","id":"not used","source":"https://docs.google.com/spreadsheets/d/13MzjsA5FAXxqrKTjxME1WSvdBrMi72RzxdeHBGg8iUw/edit","name":"Not implemented","partNumber":"Not implemented","createdAt":"Not implemented","bomSource":{"document":{"documentId":"Not implemented","documentName":"Not implemented"},"workspace":{"id":"Not implemented","name":"Not implemented","description":"Not implemented"},"element":{"id":"Not implemented","type":"Not implemented","name":"Not implemented","description":"Not implemented","partNumber":"Not implemented","revision":"Not implemented","state":"Not implemented"}},"items":[],"headers":[{"name":"test","propertyName":"test","order":1},{"name":"test","propertyName":"test","order":2},{"name":"test","propertyName":"test","order":3}]}}

eric_pesty

@bryan_lagrange
Thanks, that looks like a lot of extra "baggage" just to display 3 cells!

Did a quick attempt at "hacking" it but didn't work... I'm going to do an IR to allow inserting a "generic table" in a drawing (from a JSON... or CSV)

Alex_Pitt

@eric_pesty

Hi, this was bugging me too, as a lot of my BOMs are in refined/processed in excel.

So I dug in a bit today and wrote some VBA code to create a "Save as JSON" excel button that works for Onshape.



to this...




WARNING / DISCLAIMER: This is a total Hack. I have very little coding experience and I've not extensively tested it yet, but it seems to work for at least this one simple example.  I don't really have time to support or refine this code but hopefully it will be useful to some.

Thanks go to the following tutorials

This is good for finding the data range in a spreadsheet...
https://www.youtube.com/watch?v=6PLmcweo50I&list=PLKbOx3gUV_E-udcFZb40qMTaHz6hyf2Nq&index=3

The meat of it is based on this...

https://www.youtube.com/watch?v=xDeSDHyK4PM&t=7s


____________________________

Private Sub SaveAsJSONbutton_Click()

'my addition

Const startBlurb2Replace As String = "{~bomTable~:{~formatVersion~:~1.0~,~id~:~not used~,~source~:~url removed~,~name~:~Not implemented~,~partNumber~:~Not implemented~,~createdAt~:~Not implemented~,~bomSource~:{~document~:{~documentId~:~Not implemented~,~documentName~:~Not implemented~},~workspace~:{~id~:~Not implemented~,~name~:~Not implemented~,~description~:~Not implemented~},~element~:{~id~:~Not implemented~,~type~:~Not implemented~,~name~:~Not implemented~,~description~:~Not implemented~,~partNumber~:~Not implemented~,~revision~:~Not implemented~,~state~:~Not implemented~}},~items~:"

startBlurb = Replace(startBlurb2Replace, Chr(126), Chr(34))

'MsgBox startBlurb

Dim ySheet As Long

Dim xSheet As Long

Dim yData As Long

Dim xData As Long

ySheet = Rows.Count

xSheet = Columns.Count

'this finds last row# of Data (yData) with Error handling for a blank sheet

On Error Resume Next

yData = Cells.Find(What:="*", _

After:=Range("A1"), _

LookAt:=xlPart, _

LookIn:=xlFormulas, _

SearchOrder:=xlByRows, _

SearchDirection:=xlPrevious, _

MatchCase:=False).Row

On Error GoTo 0

'this finds last column# of Data (xData) with Error handling for a blank sheet

On Error Resume Next

xData = Cells.Find(What:="*", _

After:=Range("A1"), _

LookAt:=xlPart, _

LookIn:=xlFormulas, _

SearchOrder:=xlByColumns, _

SearchDirection:=xlPrevious, _

MatchCase:=False).Column

On Error GoTo 0

Dim headers As String

Dim finalHeaders As String

'The following collects values of first row and writes to headers

iRow = 1

For iCol = 1 To xData

If iCol = 1 Then

headers = ",~headers~:[{~name~:~" & Cells(iRow, iCol).Text & "~,~propertyName~:~" & Cells(iRow, iCol).Text & "~,~order~:" & iCol & "}"

Else

headers = headers & ",{~name~:~" & Cells(iRow, iCol).Text & "~,~propertyName~:~" & Cells(iRow, iCol).Text & "~,~order~:" & iCol & "}"

End If

Next iCol

headers = headers & "]}}"

finalHeaders = Replace(headers, Chr(126), Chr(34))

'MsgBox finalHeaders

'below from JSON export tutorial

Set objectProperties = CreateObject("Scripting.Dictionary")

For Each C In ActiveSheet.ListObjects(1).HeaderRowRange.Cells

    objectProperties.Add C.Column, C.Value

Next

Dim collectionToJson As New Collection

For Each r In ActiveSheet.ListObjects(1).ListRows

    Set jsonObject = CreateObject("Scripting.Dictionary")

    For Each C In r.Range.Cells

        jsonObject.Add objectProperties(C.Column), C.Value

    Next

    collectionToJson.Add jsonObject

Next

fileSaveName = Application.GetSaveAsFilename(fileFilter:="JSON Files (*.json), *.json")

If fileSaveName <> False Then

fileNumber = FreeFile

Open fileSaveName For Output As fileNumber

    Print #fileNumber, startBlurb & JsonConverter.ConvertToJson(collectionToJson) & finalHeaders

  

End If

End Sub