0

I hava text file full of values like this:

The first line is a list of column names like this:

col_name_1, col_name_2, col_name_3 ......(600 columns)

and all the following columns have values like this: 

1101,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1101,1,3.86,65,0.46418,65,0.57151...

What is the best way to import this into mysql?

Specifically how to come up with the proper CREATE TABLE command so that the data will load itself properly? What is the best generic data type which would take in all the above values like 1101 or 3.86 or 0.57151. I am not worried about the table being inefficient in terms of storage as I need this for a one time usage.

I have tried some of the suggestions in other related questions like using Phpmyadmin (it crashes I am guessing due to the large amount of data)

Please help!

jason
  • 3,471
  • 6
  • 30
  • 43
  • you can increase max_execution, max_input time from php.ini. Uncheck "Allow the interruption of an import in case the script detects it is close to the PHP timeout limit." in phpmyadmin under import tab. I hope this will make you to upload your csv file in db through phpmyadmin. – swapnesh May 14 '12 at 04:12

3 Answers3

0

Data in CSV files is not normalized; those 600 columns may be spread across a couple of related tables. This is the recommended way of treating those data. You can then use fgetcsv() to read CSV files line-by-line in PHP.

To make MySQL process the CSV, you can create a 600 column table (I think) and issue a LOAD DATA LOCAL INFILE statement (or perhaps use mysqlimport, not sure about that).

The most generic data type would have to be VARCHAR or TEXT for bigger values, but of course you would lose semantics when used on numbers, dates, etc.

Ja͢ck
  • 170,779
  • 38
  • 263
  • 309
  • What is the most general numeric data type, do we have one like that? For eg. in C, double can hold anything (int or float or double). Do we have something like that for mysql. – jason May 14 '12 at 04:36
  • Probably `FLOAT` or `DOUBLE`. See here: http://dev.mysql.com/doc/refman/5.5/en/numeric-types.html – Ja͢ck May 14 '12 at 04:47
0

I noticed that you included the phpmyadmin tag.

PHPMyAdmin can handle this out of box. It will decide "magically" which types to make each column, and will CREATE the table for you, as well as INSERT all the data. There is no need to worry about LOAD DATA FROM INFILE, though that method can be more safe if you want to know exactly what's going on without relying on PHPMyAdmin's magic tooling.

Michael Kopinsky
  • 884
  • 1
  • 7
  • 14
0

Try convertcsvtomysql, just upload your csv file and then you can download and/or copy the mysql statement to create the table and insert rows.

digitai
  • 1,870
  • 2
  • 20
  • 37