0

I have a csv file with about a Million rows and 1930 Columns , which I'm hoping to load into MySql . I was able to convert the headers to a create Table statement but I run into this

SQL Error (1005): Can't create table TRAIN.table (errno: 185 "Too many columns")

Any idea as to y I get this error even though I`m not near the hard limit of number of columns

ronroc
  • 33
  • 1
  • 5
  • I hope the below links will help http://stackoverflow.com/questions/9998596/create-mysql-table-directly-from-csv-file-using-the-csv-storage-engine http://stackoverflow.com/questions/6605765/importing-a-csv-into-mysql-via-command-line – Vincent Aug 23 '15 at 10:28
  • As I recall the question was originally 210 columns. My guess is that the header row is being incorrectly parsed. – e4c5 Aug 24 '15 at 03:00

2 Answers2

2

There's no way for mysql to guess the columns but that doesn't mean you need to type in all the fields manually either (assuming of course that the first line in your file is a header).

You need to write a simple script that will process the header and convert that into an SQL script but you will still have issues choosing the optimal data type for each column.

e4c5
  • 52,766
  • 11
  • 101
  • 134
  • Hi ! Tks for d answer , your suggestion did work but I ran into another error (see the edit above) ......any suggestions .... – ronroc Aug 24 '15 at 02:38
  • I was just going through my old questions and came across this, what did you manage in the end? – e4c5 May 02 '16 at 09:07
1

No there is no way to do this. you cant find out what kind of type you have for each column (integer, float, date, Timestamp,...).

If you can find out it on the Column Name you can write a short Skript to get the Feldnames from your file and output the Create Statement

Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
  • Tks for d answer , your suggestion did work but I ran into another error (see the edit above) ......any suggestions – ronroc Aug 24 '15 at 02:41
  • No, you can do it. There a to mutch rows for one table. if there is a column for a primary key you can split it easy with – Bernd Buffen Aug 24 '15 at 18:01
  • cut -d';' -f1-101 csvfile.csv >file_001; cut -d';' -f1,102-201 csvfile.csv >file_002; and so on – Bernd Buffen Aug 24 '15 at 18:04