Solved

ERD csv format example

Forum|Forum|9 years ago
April 18, 2017
26 replies
758 views

I could not find this on other posts.

I have a set of tables spread across several different platforms (Google Sheets Smartsheet and others). Not in stand-alone database.

I'll like to build an ERD for the system but will need to build the csv outside of a single query.

What are the columns expected by the ERD csv import?

Is there an example online somewhere?

Thanks.

Craig

Best answer by Karsten B

Zhang

Unfortunately there is not a way to delete a table once imported into Lucidchart. Once the relations ships are drawn you can always change them by selecting the line and changing its endpoint within Lucidchart.

We're always looking for ways to improve Lucidchart and your feedback is the best way for us to do that. Would you mind adding your idea to our product feedback section of the community?

This form allows our team to categorize like requests and bring them to our product team.

Thanks again for your request!

Sara M102

Lucidite

Hi Craig! Have you checked out our Help Center article about ERD import?

Craig W101Author

I have. More than once.

I can create an ERD but I don't have an example of the last 4 columns of the import.

I assume they are related to the in//out relationship types and foreign/primary keys ... but what are valid entries that I do not know.

Craig

Scott M

I'm interested in doing the same thing. I am not able to run a SQL against an actual database but I have a spreadsheet detailing all the tables I use. I'm looking to format that in a way that allows me to import them into Lucid for purposes of building an ERD.

I can not find any information on what format the import needs to be in. I attempted doing an export from Lucid to see the format that it exports in but it does not accept that as an import so it's obviously expecting something different in the import.

Craig W101Author

This is what I know (or believe) so far:

csv has 12 columns

The first three contain this data for my export from an SQL server

sqlserver

master

dbo

Field 4 is the table name

Field 5 is the column name

Field 6 is a counter of the column (if the table has 10 columns there will be 10 records with counter 1 to 10)

Field 7 is the column type (varchar smallint etc..)

Field 8 is the type size (for varchar char nchar and nvarchar)

Columns 9-12 are the ones I have no data for (yet)

Craig

Scott M

I made some similar progress too: Here are my findings similar to yours but with some info for the remaining columns (ie the link of Primary/Foreign relationship):

DBMS Type;"DB Name";"Schema";"Table";"Column Name";"Column #";"Column Type";"Column Length";"Reference Key Type";"Reference Key's Schema";"Key's Table";"Key's Column"

Scott M

So the additional things you were missing would be the arrows/lines that get drawn to connect the ERD's tables on Lucidchart. I don't know all the valid types but using PRIMARY and FOREIGN were acceptable values resulting in PK / FK on the diagram.

Craig W101Author

Thanks Scott. That gets me closer.

I may have time this weekend to experiment some more.

Craig

Scott M

With this definition:

DBMS Type;"DB";"Schema";"Table";"Row Name";"Row #";"Row Type";"Row Length";"Key Type";"Key's Schema";"Key's Table";"Key's Row"

This example text can be copied into the importer:

postgresql;"DB";"public";"Parent Table 1";"Name";"1";"Text";50;"PRIMARY KEY";;;

postgresql;"DB";"public";"Child Table 2";"Name";"1";"Text";50;"PRIMARY KEY";;;

postgresql;"DB";"public";"Child Table 2";"Parent Link";"2";"Text";50;"FOREIGN KEY";"public";"Parent Table 1";"Name"

Results look like below when you drag the two objects onto the page:

Yu(Steven) Z

Hi Scott Craig and Sara

Thanks very much for sharing the insights and workable examples. It has been 9 months since you started the thread hope you may still interested in the topic or would not mind to comment on my questions and observations.

1. the 12 columns are is ture if the DBMS is set to postgreSQL as the template Query for the database is as below:

"SELECT 'postgresql' AS dbmst.table_catalogt.table_schemat.table_namec.column_namec.ordinal_positionc.data_typec.character_maximum_lengthn.constraint_typek2.table_schemak2.table_namek2.column_name"

coresponding to the 12 columns you shared.

For mysql there will be no table catalog field so the format will be different as the template query of mysql is as below:

"SELECT 'mysql' dbmst.TABLE_SCHEMAt.TABLE_NAMEc.COLUMN_NAMEc.ORDINAL_POSITIONc.DATA_TYPEc.CHARACTER_MAXIMUM_LENGTHn.CONSTRAINT_TYPEk.REFERENCED_TABLE_SCHEMAk.REFERENCED_TABLE_NAMEk.REFERENCED_COLUMN_NAME"

but this makes no differnce for the major component of the csv file. namely Sechma will be created as a group of template under entity relation library table name and table definition will be as an object that can be used as normal template. If the file is loaded with csv or text it doesn't matter which dbms was selected in the first step.

My question is it seems not able to delete the imported table although whether to show it or not can be selected. Currently the example table given be Scott sit in my library which is not bad. But some other tables I created for trial I hope to be able to remove them. Do you have any idea on how to remove an imported template table?

2. Currently there seems no option to describe the one to one or one to many relationship of the keys in the csv file/text input. Lucidchart is linking Primary key and foreign key with one to many relationship by default. Just wondering if there might be some hidden parameter that can be set in the csv file? It seems to be able to set different linkage relationships as demonstrated in the diagram in the ERD section in the following link.

https://lucidchart.zendesk.com/hc/en-us/articles/207299756-Entity-Relationship-Diagrams

Orput it another way: Besides the 4 database is there any hidden definitions of dbms so that more parameters can be set?

It would be very helpful to know more about how this works to make life easier with lucidchart :)

Steven

Karsten BAnswer

Lucid support team

Zhang

Unfortunately there is not a way to delete a table once imported into Lucidchart. Once the relations ships are drawn you can always change them by selecting the line and changing its endpoint within Lucidchart.

We're always looking for ways to improve Lucidchart and your feedback is the best way for us to do that. Would you mind adding your idea to our product feedback section of the community?

This form allows our team to categorize like requests and bring them to our product team.

Thanks again for your request!

Show more replies

Create an account in the community

Log in to the community