ERD csv format example

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

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

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.

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

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"

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.

Thanks Scott. That gets me closer.

I may have time this weekend to experiment some more.

Craig

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

Hi Karsten

Thanks for your quick reply. I see how to set up cardinality manually but the default of primary to foreign key have 1 to many relationship will be applicable for most cases. I have submitted the ideas via the feature request form. 

Currently the following field can be defined in the csv file (shared by Liccid chart community manager Megan) hope the list can grow. 

"Dbms (mysql postgres etc.)
TABLE_SCHEMA (database name)
TABLE_NAME (table name)
COLUMN_NAME (column name)
ORDINAL_POSITION (order of column)
DATA_TYPE (data type of column)
CHARACTER_MAXIMUM_LENGTH (character length for column)
CONSTRAINT_TYPE (unique primary key foreign key)
REFERENCED_TABLE_SCHEMA (foreign key’s database)
REFERENCED_TABLE_NAME (foreign key’s table)
REFERENCED_COLUMN_NAME (foreign key’s column)"

Finally I hope the visual query functionality can be integrated with other tools such as Alteryx's visual query builder (powered by Active Query Builder). And can support more database for example Exasol besides the current four. Hope this will be possible in the further.

I was able to delete a table from an import by re-importing the CSV file with the table and columns deleted from the file.

Elaine

Thanks for sharing that. Re-importing your CSV is the only way to effectively "reset" the data.  We are always looking to improve the functionality of our product and these interactions help us better understand what our users are looking for.  

Hi Elain and Karsten

Thanks very much for sharing the insights. Solves my current problem:)

I am looking for a sample csv that can import and create a UML diagram for Lucid Chart. I don't see it any where in this thread Any help would be greatly appreciated? Thanks in advance.

A sample would be great I am getting a parsing errors below any thoughts:

"emamdb""dbo""EMAM_Assets""face_version""7""int""""FOREIGN KEY""dbo""EMAM_Asset_Versions""asset_version" "emamdb""dbo""EMAM_Assets""Archive_Profile_Id""23""int""""FOREIGN KEY""dbo""EMAM_Archive_Profiles""ID" "emamdb""dbo""EMAM_Assets""date_created""22""datetime""""""""""" "emamdb""dbo""EMAM_Assets""Metadata_Set_Id""24""int""""""""""" "emamdb""dbo""EMAM_Assets""ModifiedBy""25""int""""""""""" "emamdb""dbo""EMAM_Assets""Sequence_Id""26""int""""""""""" "emamdb""dbo""EMAM_Assets""archive_storage_id""16""int""""""""""" "emamdb""dbo""EMAM_Assets""subject""17""nvarchar""100""""""""" "emamdb""dbo""EMAM_Assets""category""18""nvarchar""50""""""""" "emamdb""dbo""EMAM_Assets""copyright""19""nvarchar""1000""""""""" "emamdb""dbo""EMAM_Assets""keywords""20""nvarchar""500""""""""" "emamdb""dbo""EMAM_Assets""location""21""nvarchar""100""""""""" "emamdb""dbo""EMAM_Assets""rating""10""float""""""""""" "emamdb""dbo""EMAM_Assets""ingested_by""11""int""""""""""" "emamdb""dbo""EMAM_Assets""ingested_on""12""datetime""""""""""" "emamdb""dbo""EMAM_Assets""lastmodified_on""13""datetime""""""""""" "emamdb""dbo""EMAM_Assets""IsArchived""14""bit""""""""""" "emamdb""dbo""EMAM_Assets""rowguid""15""uniqueidentifier""""""""""" "emamdb""dbo""EMAM_Assets""title""3""nvarchar""500""""""""" "emamdb""dbo""EMAM_Assets""asset_type_id""4""smallint""""""""""" "emamdb""dbo""EMAM_Assets""asset_state_id""5""int""""""""""" "emamdb""dbo""EMAM_Assets""latest_version""6""int""""""""""" "emamdb""dbo""EMAM_Assets""asset_desc""8""nvarchar""1000""""""""" "emamdb""dbo""EMAM_Assets""author""9""nvarchar""100""""""""" "emamdb""dbo""EMAM_Assets""client_id""2""int""""""""""" "emamdb""dbo""EMAM_Assets""id""1""int""""PRIMARY KEY"""""""

Hi Matt

Thanks for reaching out on the community. If you are experiencing trouble importing your ERD please attempt this process in an incognito browsing session. Sometimes extensions or cache/cookies problems can interfere with Lucidchart's ERD import.

If this doesn’t help resolve the issue please send me the following information:

• A screenshot of the error message or the issue that you are describing.

• The name of the DBMS you are attempting to import from.

Thank you!

Hi Rich thanks for following up on this thread. I recommend checking out our ERD article. If you scroll to the bottom of this article you can find some sample files attached for PostgreSQL Oracle and SQL Server.

Unfortunately we don't currently have a sample file for MySQL. However I recommend checking out the headers in the PostgreSQL example file. The headers for MySQL are very similar and are listed as follows in the MySQL query accessible in the import modal: 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​

I hope this helps! Please let me know if you have any other questions.

Would SOMEONE please post a useable (entire) solution to this issue.  The whole working query would be greatly appreciated.

Please......

PostgreSQL please.....

Hi Dan thanks for posting! Are you referring to the query you need to run in your database in order to generate the output file to upload into Lucidchart? If so- this is provided in our ERD import process as described here. You will need to make sure you select your database type from the list then you can copy the query shown in the window and run it in your DB. The results of the query can be saved as a CSV TSV or TXT file or copied to your clipboard and you can upload them to Lucidchart in the next step of the import process. The query should include everything you need including table names and PK/FK relationships indicated between tables.

It's worth noting that this process imports your databases tables and schemas into the toolbox on the left side of your page but it does not automatically generate the whole ERD for you.  You will need to manually drag entities out to the canvas from the toolbox to build your ERD. Relationship lines indicated in the results of the query you ran should be automatically drawn between entities as you do this.

If this information doesn't help would you mind submitting a support ticket using this form so we can investigate further? Please include the output file and a detailed description/screenshot of the issue you are seeing.  Thanks!

I would also appreciate a CSV example template. I have data in a Quickbase database that I need to format and export as a CSV for import to Lucid. Can someone please provide an example of how to structure a CSV so that Lucid can convert to shapes on import? 

Hi Peter I've copied an example select statement and CSV input for a PostgreSQL database import below. While Lucidchart doesn't formally support a Quickbase import if you structure your data in this form and choose the PostgreSQL option Lucidchart should be able to successfully interpret the data and build an ERD.

postgresqlquizbowlpublicdatabase_migrationsapplied2timestamp with time zone
postgresqlquizbowlpublicdatabase_migrationsname1character varying
postgresqlquizbowlpublicload_data_versionsapplied3timestamp with time zone
postgresqlquizbowlpublicload_data_versionschecksum2character varying
postgresqlquizbowlpublicload_data_versionsname1character varying
postgresqlquizbowlpublicevent_teamcreate_date4timestamp with time zone
postgresqlquizbowlpublicevent_teamcreate_user_id3integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicevent_teamteam_id2integerPRIMARY KEY
postgresqlquizbowlpublicevent_teamteam_id2integerFOREIGN KEYpublicteamteam_id
postgresqlquizbowlpublicevent_teamevent_id1integerPRIMARY KEY
postgresqlquizbowlpublicevent_teamevent_id1integerFOREIGN KEYpubliceventevent_id
postgresqlquizbowlpublicevent_questioncreate_date9timestamp with time zone
postgresqlquizbowlpublicevent_questioncreate_user_id8integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicevent_questionclose_timestamp7timestamp with time zone
postgresqlquizbowlpublicevent_questionstart_timestamp6timestamp with time zone
postgresqlquizbowlpublicevent_questionsequence5integer
postgresqlquizbowlpublicevent_questionround_number4integer
postgresqlquizbowlpublicevent_questionquestion_id3integerFOREIGN KEYpublicquestionquestion_id
postgresqlquizbowlpublicevent_questionevent_id2integerFOREIGN KEYpubliceventevent_id
postgresqlquizbowlpublicevent_questionevent_question_id1integerPRIMARY KEY
postgresqlquizbowlpublicschoollast_mod_date10timestamp with time zone
postgresqlquizbowlpublicschoollast_mod_user_id9integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicschoolcreate_date8timestamp with time zone
postgresqlquizbowlpublicschoolcreate_user_id7integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicschoolstate_abbrev6character varying
postgresqlquizbowlpublicschoolcity5USER-DEFINED
postgresqlquizbowlpublicschoolnickname4USER-DEFINED
postgresqlquizbowlpublicschoolmascot3USER-DEFINED
postgresqlquizbowlpublicschoolname2USER-DEFINEDUNIQUE
postgresqlquizbowlpublicschoolschool_id1integerPRIMARY KEY
postgresqlquizbowlpublicsessionexpires3integer
postgresqlquizbowlpublicsessionsession_data2text
postgresqlquizbowlpublicsessionsession_id1character varyingPRIMARY KEY
postgresqlquizbowlpublicslidelast_mod_date7timestamp with time zone
postgresqlquizbowlpublicslidelast_mod_user_id6integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicslidecreate_date5timestamp with time zone
postgresqlquizbowlpublicslidecreate_user_id4integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicslideurl3USER-DEFINED
postgresqlquizbowlpublicslidename2USER-DEFINED
postgresqlquizbowlpublicslideslide_id1integerPRIMARY KEY
postgresqlquizbowlpublicevent_logcreate_date6timestamp with time zone
postgresqlquizbowlpublicevent_logcreate_user_id5integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicevent_logdata4text
postgresqlquizbowlpublicevent_logtype3USER-DEFINED
postgresqlquizbowlpublicevent_logevent_id2integerFOREIGN KEYpubliceventevent_id
postgresqlquizbowlpublicevent_logevent_log_id1integerPRIMARY KEY
postgresqlquizbowlpublicquestion_typedescription3text
postgresqlquizbowlpublicquestion_typename2USER-DEFINED
postgresqlquizbowlpublicquestion_typequestion_type_value1character varyingPRIMARY KEY
postgresqlquizbowlpublicuser_logincreate_date6timestamp with time zone
postgresqlquizbowlpublicuser_logincreate_user_id5integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicuser_loginuser_agent4text
postgresqlquizbowlpublicuser_loginsession_id3character varying
postgresqlquizbowlpublicuser_loginlogin_ip2cidr
postgresqlquizbowlpublicuser_loginuser_login_id1integerPRIMARY KEY
postgresqlquizbowlpublicversioned_schemasha13character varying40
postgresqlquizbowlpublicversioned_schemarevision2integer
postgresqlquizbowlpublicversioned_schemaname1character varying
postgresqlquizbowlpublicsubmissionlast_mod_date10timestamp with time zone
postgresqlquizbowlpublicsubmissionlast_mod_user_id9integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicsubmissioncreate_date8timestamp with time zone
postgresqlquizbowlpublicsubmissioncreate_user_id7integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicsubmissionpoints6integer
postgresqlquizbowlpublicsubmissionis_correct5boolean
postgresqlquizbowlpublicsubmissiontime_to_answer4numeric
postgresqlquizbowlpublicsubmissionanswer3USER-DEFINED
postgresqlquizbowlpublicsubmissionevent_question_id2integerFOREIGN KEYpublicevent_questionevent_question_id
postgresqlquizbowlpublicsubmissionsubmission_id1integerPRIMARY KEY
postgresqlquizbowlpubliceventlast_mod_date8timestamp with time zone
postgresqlquizbowlpubliceventlast_mod_user_id7integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpubliceventcreate_date6timestamp with time zone
postgresqlquizbowlpubliceventcreate_user_id5integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpubliceventend_time4timestamp with time zone
postgresqlquizbowlpubliceventstart_time3timestamp with time zone
postgresqlquizbowlpubliceventname2USER-DEFINED
postgresqlquizbowlpubliceventevent_id1integerPRIMARY KEY
postgresqlquizbowlpublicquestionlast_mod_date13timestamp with time zone
postgresqlquizbowlpublicquestionlast_mod_user_id12integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicquestioncreate_date11timestamp with time zone
postgresqlquizbowlpublicquestioncreate_user_id10integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicquestionlevel_id9character varying
postgresqlquizbowlpublicquestionpoints8integer
postgresqlquizbowlpublicquestionexplanation7USER-DEFINED
postgresqlquizbowlpublicquestionquestion_type_value6character varyingFOREIGN KEYpublicquestion_typequestion_type_value
postgresqlquizbowlpublicquestionanswer_value5USER-DEFINED
postgresqlquizbowlpublicquestionanswer4USER-DEFINED
postgresqlquizbowlpublicquestionoptions3text
postgresqlquizbowlpublicquestionquestion2USER-DEFINED
postgresqlquizbowlpublicquestionquestion_id1integerPRIMARY KEY
postgresqlquizbowlpublicteamlast_mod_date8timestamp with time zone
postgresqlquizbowlpublicteamlast_mod_user_id7integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicteamcreate_date6timestamp with time zone
postgresqlquizbowlpublicteamcreate_user_id5integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicteammascot4USER-DEFINED
postgresqlquizbowlpublicteamname3USER-DEFINEDUNIQUE
postgresqlquizbowlpublicteamschool_id2integerFOREIGN KEYpublicschoolschool_id
postgresqlquizbowlpublicteamteam_id1integerPRIMARY KEY
postgresqlquizbowlpublicuser_accountlast_mod_date12timestamp with time zone
postgresqlquizbowlpublicuser_accountlast_mod_user_id11integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicuser_accountcreate_date10timestamp with time zone
postgresqlquizbowlpublicuser_accountcreate_user_id9integerFOREIGN KEYpublicuser_accountuser_id
postgresqlquizbowlpublicuser_accountis_admin8boolean
postgresqlquizbowlpublicuser_accountpassword7character varying
postgresqlquizbowlpublicuser_accountlast_name6USER-DEFINED
postgresqlquizbowlpublicuser_accountfirst_name5USER-DEFINED
postgresqlquizbowlpublicuser_accountemail4USER-DEFINEDUNIQUE
postgresqlquizbowlpublicuser_accountschool_id3integerFOREIGN KEYpublicschoolschool_id
postgresqlquizbowlpublicuser_accountteam_id2integerFOREIGN KEYpublicteamteam_id
postgresqlquizbowlpublicuser_accountuser_id1integerPRIMARY KEY

I’m trying to generate the format as well, could someone share an example of how it looks the CVS using MySQL, or some .cvs or .txt file examples just to play with.