Author Topic: Spreadsheet to Table then Query  (Read 1547 times)

GeistBW

  • EA User
  • **
  • Posts: 24
  • Karma: +0/-0
    • View Profile
Spreadsheet to Table then Query
« on: November 24, 2020, 05:55:54 am »
Hi,
I have a spreadsheet that I'd like to import into Sparx, but I want to keep it as a table since I'm needing to query the data. Is the Custom Table Artifact the only way to do this?  If so, how do I perform a query against the data?  I know SQL and can write my own Select statements. The Custom Table just looks like it is for displaying all it's content on the diagram. 

I'm not really needing this to be on a diagram, more of just a table I can load and then do query to find the data.  Once I have the data selected I'm wanting to either export it or copy it to be placed in a spreadsheet.

We are on Version 15.1 use a SQL Server database.

Suggestion or instructions are very welcome.
Thanx

qwerty

  • EA Guru
  • *****
  • Posts: 12731
  • Karma: +361/-295
  • I'm no guru at all
    • View Profile
Re: Spreadsheet to Table then Query
« Reply #1 on: November 24, 2020, 08:56:21 am »
If you tell me how to create such a "custom table" I will have a look.

q.

RoyC

  • EA Administrator
  • EA Practitioner
  • *****
  • Posts: 1297
  • Karma: +21/-4
  • Read The Help!
    • View Profile
Re: Spreadsheet to Table then Query
« Reply #2 on: November 24, 2020, 09:05:03 am »
Best Regards, Roy

GeistBW

  • EA User
  • **
  • Posts: 24
  • Karma: +0/-0
    • View Profile
Re: Spreadsheet to Table then Query
« Reply #3 on: November 24, 2020, 09:46:33 am »
Regarding: create such a "custom table" I will have a look
 - That's what I'm asking: how can I create this type of a table.
      - Does the table need to be created outside of Sparx or can it be created inside Sparx? I have a spreadsheet with about 20 column (Ex: First Name, Last Name, etc).
      - Is the Custom Table Artifact the only way to do this?

I was hoping there was a way to do this with the Database Builder.  Or is this only for creating table and not really for storing data? I thought it could possibly store data since there was a SQL Scratch Pad.

I need to be able to load more data from a spreadsheet at various times.
 - If this can only be done with a Custom Table Artifact:
     - is there an easier way to load the data than to manually append rows in the Data Tagged Value in XML?
     - I'm not very familiar with Javascript, is there a sample that I can use to do a select?
 - If this can be done in the Database Builder, is there a link to the instructions?

Thanx


qwerty

  • EA Guru
  • *****
  • Posts: 12731
  • Karma: +361/-295
  • I'm no guru at all
    • View Profile
Re: Spreadsheet to Table then Query
« Reply #4 on: November 24, 2020, 10:47:58 am »
LMAO You can add ports to a custom table.

q.

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 11717
  • Karma: +453/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Spreadsheet to Table then Query
« Reply #5 on: November 24, 2020, 04:21:57 pm »
I don't think EA is the best tool to be storing random tabular data.

If the data somehow represent things we usually model like requirements, applications, functions, processes, etc... you might want to consider creating a stereotype for your thing.
Then add properties to that stereotype for each of the columns in your sheet.

Then you can import each row of your data as an element with your stereotype. Each of the columns can then be imported in a tagged value.

You can import/export the data using the regular csv import/export, or write your own import script.

The will be much easier to query than the data in a custom table artifact (which is stored as an xml string)

Geert