Create table automatically according to input file in mysql

Question

I have a file with large number of columns and I want to input this file in mysql table.

The thing is if we have file with, say, 8 columns then we will first create table by -

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 input the file by -

LOAD DATA INFILE "FILE" INTO TABLE input;

But the thing is, I have file with 150 columns and I want to insert this file in mysql table automatically (so that I should not have to create table first). The first row of my file is header and it should be as column names in table and also each column and each row has different datatype.

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

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

isgarci · Accepted Answer · 2014-01-22 14:54:27Z

2

You can try using SequelPro mysql client.

With this tool you can use the option "File->Import", and in the window "CSV Import Field Mapping", instead of selecting to import into an existing table, you can choose the button "New".

It's better if your CSV have a header line describing the column names, so it gives the right column names. The tool also is good at guessing the types of the columns according to the content.

You can eventually experience problems if VARCHAR(255) is setted as default type for fields of text type. If it is the case, change the type of those fields to TEXT type.

edited Jan 22, 2014 at 14:54

answered Jan 22, 2014 at 13:52

isgarci

513 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2648008 Over a year ago

mysqlworkbench also has this import feature.

Ben Lin · Accepted Answer · 2015-10-27 23:13:02Z

2

use phpmyadmin. It has the ability to create table base on the first line of the file and guess the table structure. Click "Import" link and select your file. Don't forget to select the Format to fit your file format, mostly CSV.

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

It makes my life easier.

edited Oct 27, 2015 at 23:13

answered Jul 17, 2013 at 21:30

Ben Lin

84310 silver badges15 bronze badges

Comments

ethrbunny · Accepted Answer · 2012-02-06 13:21:14Z

1

I don't think this is possible using straight-up MySQL. Somehow the column definitions would have to be guessed. You'll prob have to go with a 2ndary language to read out the first row, make the table and then import.

You can do this using mysqldump though.

answered Feb 6, 2012 at 13:21

ethrbunny

10.5k9 gold badges77 silver badges145 bronze badges

2 Comments

Vikas Over a year ago

Thanks for your reply. But I am new to MySQL, can you please elaborate your answer.

ethrbunny Over a year ago

MySQL can't guess the datatype for an arbitrary column in a file. You could use a language like Perl to read the file, grab the first row and build the table for you. Or you could dump the original data using mysqldump and it will add the 'create table' statement for you.

Light Bringer · Accepted Answer · 2017-05-25 01:30:50Z

0

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

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

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

all command lines which I also use. (if someone has a handy video that describes every step of how to use one of those MySQL Developer Environments though, that might be kinda of nice, one that it doesn't take 20 steps to load a table, though always probably be faster to type one in by hand and use one step or edit a dump.).

edited May 25, 2017 at 1:30

answered May 25, 2017 at 1:24

Light Bringer

8598 silver badges12 bronze badges

Collectives™ on Stack Overflow

Create table automatically according to input file in mysql

4 Answers 4

1 Comment

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related