Create MySQL table from csv, txt, or xls file that has over 300 columns

Question

I need to create a MySQL table that has over 300 columns. I have an excel file that has each field name in the first row and the field length in the second row. All datatypes are VARCHAR. Is there a way or tool available that will take this file and create a MySQL table with the specified field lengths?

I tried MySQL for Excel but I would still have to manually define each field's length which is tedious. All other methods I have researched seem to require the same thing. If I can't find something, obviously I will have to create this table by hand, but I figured I would ask before investing that much time.

Here is an example of what the first few columns look like:

field1    field2    field3    field4
4         2         255       8

There is no data to import yet. I just need to create the table for now.

Thanks!

300 columns means that you are doing something wrong in your database design. Normalize. to export http://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format — Sam, Mar 18 '14 at 17:55
I wish it were that easy. This file is designed by the government and needs to be imported as is. Not much I can do about that. — PatC, Mar 18 '14 at 17:56
Any scripting language (like PHP) would make short work of this. — Digital Chris, Mar 18 '14 at 18:03

Orelsanpls · Answer 1 · 2014-03-18T18:24:24.653

You can parse the txt file with some langague C++, PHP... and then build a request and execute it.

PHP and PDO:: module will make this simpliest.

I don't know if some program already do this job. This is a solution.

If you choose to do this and have some trouble, you are welcome. Every stackoverflow members will help you.

Edit :

There is an example. How to create table with PDO and PHP Link

I guess you could make something like this :

<?php

    $dns = 'mysql:host=localhost;dbname=w3cyberlearning';
    $user = 'user2000';
    $pass = 'password2000';

    $db = new PDO($dns, $user, $pass);


    $sql = "CREATE TABLE IF NOT EXISTS" +
           $table_name + "(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,";

    $i = 0;
    foreach ($fields as &$value)
    {
      if (i++ != 0)
        $sql += ",";
      $sql += $value + " VARCHAR(200) NOT NULL";
    }       
    $sql += ")";

    $sq = $db->query($sql);

    if ($sq) {
        echo 'created';
    }
    ?>

With $fields an array who contains all fields name and $table_name, the name of the table.

Thank. I figured there would be a way to do this using PHP, but I am not familiar enough with it to make it an easy task. I will do some more research. I guess if I learned how to write something like this I could use it in the future with other csv files. — PatC, Mar 18 '14 at 18:11

score 0 · Answer 2 · answered Mar 18 '14 at 18:07

0

I don't know of any tool to do this, but it seems pretty straight forward to read the Excel file as a CSV file using your programming language of choice, and create the table from the input. For instance using c++ or java. see i.e. this link

answered Mar 18 '14 at 18:07

rakke

5,299
2
24
28

Create MySQL table from csv, txt, or xls file that has over 300 columns

2 Answers2