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