Page 1 of 1

Loading Excel Data Into A Database

Posted: Fri Aug 10, 2007 10:06 am
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?:

Posted: Fri Aug 10, 2007 10:13 am
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; 

Posted: Fri Aug 10, 2007 10:14 am
by VladSun
Also you have to run mysql deamon with "--local-infile=1" option

Posted: Fri Aug 10, 2007 11:11 am
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?

Posted: Fri Aug 10, 2007 5:24 pm
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