Database Schema Generator [closed]

Closed. This question need开发者_如何学Cs to be more focused. It is not currently accepting answers.

---

Want to improve this question? Update the question so it focuses on one problem only by editing this post.

Closed 3 years ago.

Improve this question

i have an excel file which describe all the table structure of a database table

I would like to generate database schema diagram and database schema structure for sqlite3 database from this excel file.

Any idea how to do that ?

Thanks.

---

Assuming each tab is named for a table and has cells as such:

Name    DataType
ID    integer
Name    varchar(255)

Use either Python or Java to read the workbook using python-excel or Apache POI for Excel, respectively. I'm sure other languages, particularly .NET have libraries for this purpose as well. Both languages also have drivers for sqlite3.

Iterate through each tab - use the tab name for the table CREATE statement. Iterate through each row on the tab and use that information to define the columns for the table. Defining things like primary/foreign keys and auto incrementing will require some additional cells or a naming convention + some clever algorithms.

Most likely each CREATE statement will need to passed separately over the database connection.

---

There are usually better tools to do this than Excel. Start with your SQLite3 database and analyse it with a db manager that has a schema editor like WinSQL.

---

Assuming that Excel contains fieldname and type columns I would write tiny VB script which will translate those pairs into Java SQL strings like:

private static final String DATA_CREATE="create table if not exists\n"
        + "MYTABLE\n"
        + "DATA_ID integer primary key autoincrement,\n"
        + "RAW_ID integer not null,\n"
        + "ORDER_ID integer not null,\n"
        + "CHUNK blob);";

then just add this-alike strings into your Java source. Or as option you could SQL scripts write into file place them somewhere in assets on run them from SQLite

---

If I were to write such a program I would make the following assumptions:

• The sheet would become the table name.
• The column headers would become the column name.
• The formatting rule of the column would control the data type.

With these constraints in mind, you could create an import and create tool.

To do this in Java, I would use a library that can read excel files, for instance Apache POI: (Here is the maven dependncy I use for it)

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.6</version>
    </dependency>

Read the excel file using POI, analyze the columns and dynamically create the table in the sqllite database. (drop the table if it exists). Then based on the type generate a prepared statement and insert the data based on each of the rows in the sheet.

This would be the basic setup. From there you must decide how to do foregin keys if you need it and so on.

---

I don't think that what you requesting is possible.

A solution for your question, will be to post an example table from excel, and request help to transform it into SQL