Create table automatically according to input file in mysql

Question

I have a file with lots of columns and I want to enter this file into mysql table.

The fact is that if we have a file with, say, 8 columns, then we will first create a table with

CREATE TABLE `input` ( `idInput` varchar(45) DEFAULT NULL, `row2` varchar(45) DEFAULT NULL, `col3` varchar(45) DEFAULT NULL, `col4` varchar(45) DEFAULT NULL, `col5` varchar(45) DEFAULT NULL, `col6` varchar(45) DEFAULT NULL, `col7` varchar(45) DEFAULT NULL, `col8` varchar(45) DEFAULT NULL ); 

then we will enter the file using

 LOAD DATA INFILE "FILE" INTO TABLE input; 

But the fact is, I have a file with 150 columns, and I want to automatically insert this file into the mysql table (so that I do not have to create the table first). The first line of my file is the header, and it should be both the column names in the table and each column, and each row has a different data type.

So, is there an easy way to do this so that after that I can do different things with this table?

I am using the mysql client command line version 5.5.20 (Windows 7).

Answer 1

use phpmyadmin. He has the ability to create a table in the first line of the file and guess the structure of the table. Click the Import link and select a file. Remember to select the format that matches your file format, mainly CSV.

If the file is too large to fit into phpmyadmin, sometimes I “head” the file and use the head file in phpmyadmin to create a table, and then import the file using the LOAD DATA command.

It makes my life easier.

Answer 2

You can try using the SequelPro mysql client.

Using this tool, you can use the "File-> Import" option, and in the "Map CSV import fields" window, instead of choosing to import into an existing table, you can select the "Create" button.

It is better if your CSV has a header line describing the column names, so it gives the correct column names. The tool also correctly guesses the types of columns according to the content.

You may end up with problems if VARCHAR (255) is set as the default type for text type fields. If so, change the type of these fields to TEXT.

Answer 3

I do not think this is possible using direct MySQL. Somehow, the definition of the columns should be guessed. You may have to go with a second language to read the first row, make a table, and then import.

You can do this using mysqldump though.

Answer 4

As I understand it, you have a generated text file with different types of data, ready to be downloaded from the command line. Here are the instructions from MySQL.

create: https://dev.mysql.com/doc/refman/5.7/en/mysql-batch-commands.html

edit: https://dev.mysql.com/doc/refman/5.7/en/alter-table.html

all the command lines that I also use. (if someone has a handy video that describes each step of using one of these MySQL Developer development environments, although this may seem nice, that is, it doesn’t take 20 steps to load the table, although you probably always enter one of them manually and use one step or edit the dump.).