0

I have imported a CSV file as SQL table using import option of PHPMyAdmin (because I found that to be easiest). Now the column names are "COL 1, COL 2,..." and the desired column names are the first row in the table.

How do I change the column names to the values in first row (for all the tables of a database)?

Here is a way to change one column name at a time but I have too many column names in each table of the database and I want to apply this to all tables at once.

Is there a moderately easy way to do this? Or should I try and incorporate this while importing the CSV file?

Community
  • 1
  • 1
dc95
  • 1,319
  • 1
  • 22
  • 44
  • 2
    You could write a script that can do that, but the easiest and best way to do it might be to import your data again and this time check the option "The first line of the file contains the table column names.", because your datatype might be wrong too right now (since you probably don't (want to) have only varchar columns) – Solarflare May 25 '16 at 03:50
  • @Solarflare I ended up doing exactly that. But I wonder if writing a script would be very hard? – dc95 May 25 '16 at 18:32
  • When I wrote that I was thinking about a php script, and it should be straight forward: `show tables` to get tables, for each table `show columns from table` to get datatypes, get and delete first row from the table (might be tricky to identify it sometimes), for each column do `alter table table change oldname newname datatype`. A scripted version of your link (but without correcting the datatype that was possible by reimport). In an sql stored procedure you could use information_schema, and have a harder time with the columns (php has them in an array with one query). – Solarflare May 25 '16 at 21:15

1 Answers1

2

When importing, there is a checkbox for "The first line of the file contains the table column names (if this is unchecked, the first line will become part of the data)".

The first line of the file contains the table column names (if this is unchecked, the first line will become part of the data)

Checking that box will use the first row as the column names, saving you from having to write any additional script or manually change anything.

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43