Loading Excel Data Into A Database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
4Boredom
Forum Contributor
Posts: 176
Joined: Tue Nov 08, 2005 4:29 pm

Loading Excel Data Into A Database

Post by 4Boredom »

We want to create a user system where we can make a points list of volunteers in my office and we want to have all people in the office be able to update this via Excel or a similar program, and have it update into the MySQL database.

What is the most feasible way to do this, I dont know much about the formatting but didnt know if I should look into CSV or XML or something like that?:
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

Or in tab delimited txt format.

Then you can use:

Code: Select all

LOAD DATA INFILE 'path/file.txt' INTO TABLE your_table; 
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

Also you have to run mysql deamon with "--local-infile=1" option
There are 10 types of people in this world, those who understand binary and those who don't
4Boredom
Forum Contributor
Posts: 176
Joined: Tue Nov 08, 2005 4:29 pm

Post by 4Boredom »

This works great thanks

I am just wondering if Excel can be used? Some of the people we want to do this might find it harder to use notepad then excel so I want to make it a shared document that anyone of any user ability can update.

Any advice?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

Excel has a function "Export to tab delimited tex file" - no need to edit with Notepad.
Also, Excel has a function "Import from tab delimited tex file" ...
The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply