CODEWITHSUNDEEP
mysqlexcelphpmyadmin
Geoff
Geoff

Reputation: 1007

import excel file into mysql database

This question is almost what I want:

How to import an excel file in to a MySQL database

but there is an extra part which I will ask here.

From this last question, the answer said to use LOAD DATA INFILE. But doesn't this assume that I already have my table structure created within the database? In my case I have an excel file with about 100 columns and I don't want to have to create a table with all these columns first.

So my question: Is there a way to use the first row of the excel file to be the names of the columns in the database table?

Upvotes: 0

Views: 15489

Answers (1)

Isaac Bennetch
Isaac Bennetch

Reputation: 12412

Since you're using phpMyAdmin, here's what I would do:

  1. Save your Excel file as a CSV file (File -> Save As...)
  2. From within phpMyAdmin, go to the database you want to import to (create it first if needed).
  3. You have two choices; you can set up the table structure yourself or let phpMyAdmin make a lot of guesses about it based on your CSV file. Sounds like you want the second choice, so you can do the import and then change the table data types if you need to. So click the Import tab, find your CSV file on disk, and double-check the import options. If you have put the column name in the first row/header of the CSV, check the box for "The first line of the file contains the table column names", otherwise phpMyAdmin will make something up. For my test, the default settings about delimiters was fine; your version of Excel may be different but it's easy to tweak the values here. Press Go.
  4. phpMyAdmin does the import and tries to use a sane column type for your data. You'll get a resulting table with a rather generic name (which you can rename); you can then Browse the data or view the Structure to adjust anything that doesn't look right. In particular, you should look at any non-text fields (dates, numbers, etc) to make sure they are stored in the proper data type.

If your file is huge, you may have trouble because PHP limits the amount of time and memory phpMyAdmin is permitted to use. You can work around that by importing the file in stages.

Good luck.

By the way, 100 columns sounds excessive; you may want to check to make sure you're storing your data in the most optimal way.

Upvotes: 4

Related Questions