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).

Answer 1

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.