• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

import an excel file to a mysqldb table

hello

i want to import the data in an excel file to a mysql table. i'm using mysql administrator tool. is there a way i can populate data through an excel file ?

thanks very much for your reply !

lilyyan
0
lilyyan
Asked:
lilyyan
  • 2
  • 2
  • 2
3 Solutions
 
Aleksandar BradarićSoftware DeveloperCommented:
I think your only options is to save your Excel file in the CSV format. This file can be then imported into the MySQL table using the LOAD DATA INFILE command (see details here: http://dev.mysql.com/doc/refman/5.0/en/load-data.html).

0
 
NelliosCommented:
You can save the data to a csv file from excell and then use load data infile.
Example:

LOAD DATA LOCAL INFILE   'PathToCSVFileName'  IGNORE INTO TABLE TableName
           FIELDS TERMINATED BY ';' LINES TERMINATED BY '\\n';

The statement may differ depending on the delimeter you use. I assume you use semicoln.
0
 
lilyyanAuthor Commented:
Hi, thanks for all replies.
well
1st question is, i dodn't have ftp permission for  the mysql production server i'm using. will  this be an issue?

2nd,  i don't have a home directory on the production server either. (same for other users)

i'm thinking...
first  import the excel file (  turn it into .csv file) into the mysql server on my pc. then do a back up, then restore on the production server. will this work ? please give me some hint. thanks so much.

0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
Aleksandar BradarićSoftware DeveloperCommented:
> 1st question is, i dodn't have ftp permission for  the mysql production server i'm using. will  this be an issue?

No - you'll just have to use phpMyAdmin to insert the data.

> 2nd,  i don't have a home directory on the production server either. (same for other users)

Same here - phpMyAdmin is your tool. No need to use FTP to upload the file - you have a screen in phpMyAdmin which allows you to upload the file.

Yes it will work. Not sure what kind of hint you need - it's more or less straight forward...
0
 
lilyyanAuthor Commented:
hi, thanks for the reply. i'm using Mysql Administrator tool. will this be the same as phpMyAdmin ?
0
 
NelliosCommented:
If you use LOCAL DATA INFILE there is no need to upload the files to the database server, as long as you connect to mysql directly from your pc, in the case of phpMyAdmin this is not applicable.

Short work around:
1) Install mysql on your local machine.
2) Run the process localy
3) Dump the database to an sql script.
4) Run it usinh phpMyAdmin or whatever tool you like.

Hope that helps
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now