Search code examples
mysqlsqlformatworkbench

What kind of file formats can I import into MySQLWorkbench?

The Sakila database comes with a schema.sql file, a data.sql file and a sakila.mwb file. To load the Sakila dataset in Workbench I first load the schema, then the data, and then open the .mwb file.

Is this the only way you can query in Workbench? Do you always have to import a schema, import data and then open a .mwb file?

For example, I want to be able to go on data.gov, download an XLS, HTML or CSV file (not that I really know how to work with all of these, but ultimately I'd like to) and create a database out of them. Do I have to do my own exploration on their format, create my own schema/model (do these mean the same thing?) and then somehow tell Workbench how to fill in each table in my schema with entries form the CSV/CLS/HTML file?

Solution

  • You can do something like:

    1- Create table

    CREATE TABLE test(id INT, value1 VARCHAR(255), value2 DECIMAL(8,4));

    2- Maybe you have text or other file that have format like : 

    1   tareq     1.2
2   sarah     2.3
3   Hany      4.5

    3- Use the load data command, to import that file into MySql table:

    LOAD DATA LOCAL INFILE 'E:/data.txt' 
INTO TABLE test COLUMNS TERMINATED BY '\t';

    Note that here, values in data file should be delimited by a TAB, and new line after each row.