Auro Auro - 2 months ago 14
MySQL Question

How to create a table in MySQL from a given data file

I have a zip containing 3

.dat
files. They represent 3 different tables. I was wondering if someone could help me out with copying all the data from the
.dat
files into MySQL.

For example, one file named movies.dat contains the following columns:


  1. MOVIE_ID

  2. TTILE

  3. GENRES



I opened the file with note pad and saw thousands of records like

1::Toy Story (1995)::Animation|Children's|Comedy


2::Jumanji (1995)::Adventure|Children's|Fantasy


I know the basics of MySQL, but I'm guessing that won't be enough. I was wondering if there was any command in MySQL that would read the file and create a Table automatically with those column names.

Or do I have to create a table with those column names myself and then import?

I'm using WAMP by the way, and I have already copied the .dat files into the data directory of the mysql folder

Thank you for your time!!

Answer

I was wondering if there was any command in MySQL that would read the file and create a Table automatically with those column names.

No you will have to create the tables yourself. This is because it's very hard for mysql (or any other database) to figure out what your column data types ought to be. Table creation is easy. For example the movies table might be something like:

CREATE TABLE movies(
   movie_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   title VARCHAR(255),
   genre VARCHAR(255))

The current approach of saving multiple geners in one column is sub optimal. You ought to read up on database normalization as well.

Once you have created the tables, you can load the data with LOAD DATA