European Experts Exchange (EEE.org) :: Free Experts PHP MySql Oracle Microsoft Web SQL Delphi


Databases :: MySql :: MySQL - Upload text file from Access
By: altnoise	Date: 05/06/2003 00:00:00	Points: 300	Status: Answered Quality : Excellent
Hi, I would like to be able to create a text file from MS Access and upload it into a table in MySQL on my server. I don't have a clue how to start uploading. Anyway helpwould be great! Thanks, James
By: VGR	Date: 05/06/2003 08:41:00		Type : Comment
1) I read that Access could be used as a MySql front-end GUI, so you wouldn't need a textfile 2) if you've no communication means between Access "DB" and MySql DB, then exporting-importing your data is one good solution. proceed as follows : 2a export data from Access in CSV format (comma-separated values ; this is usually one of the options in "Save As..." on ZinDoze), like this : column1,column2,column3,etc 2b copy this file to the %datadir% repository of MySql (for convenience) 2c create a TABLE in MySql suitable for receiving those data 2d import the data : LOAD DATA INFILE 'thefile.csv' INTO thetable; (default options for DELIMITED BY, ENCLOSED BY, TERMINATED BY etc should be OK)

By: sdguy	Date: 05/06/2003 08:42:00		Type : Comment
I haven't worked with Access, but you might see if it will allow you to export the contents of the table to a tab deliminated text file. If so, copy that file to the machine with mysql on it. Create a table with the same layout as the table in Access. Then try this command: mysql> LOAD DATA LOCAL INFILE "tab_deliminated_text.txt" INTO TABLE table_name; This should load the data into the MySQL database/table. Here's some more info: <A HREF="http://www.mysql.com/doc/en/Loading_tables.html">http://www.mysql.com/doc/en/Loading_tables.html</a> -sdguy

By: sdguy	Date: 05/06/2003 08:43:00		Type : Comment
Or do what VGR suggested (sorry hadn't refreshed the page before posting) sdguy

By: VGR	Date: 05/06/2003 08:48:00		Type : Comment
yes but no. If you use a TAB-delimited format, you have to specify DELIMITED BY '\t'

By: VGR	Date: 05/06/2003 08:50:00		Type : Comment
I retire. You're right. Those are the default settings : FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' <A HREF="http://www.mysql.com/doc/en/LOAD_DATA.html">http://www.mysql.com/doc/en/LOAD_DATA.html</a>

By: VGR	Date: 05/06/2003 08:51:00		Type : Assist
a fully qualified command for CSV would be : LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'; the minimum would be : LOAD DATA INFILE 'data.txt' INTO TABLE table2 FIELDS TERMINATED BY ',';

By: altnoise	Date: 05/06/2003 09:03:00		Type : Comment
Hi, thanks for the info, I've been trying todo it singyour advice but I'm not sure my webspace provider will let me, I keep getting the message: MySQL said: Access denied for user: 'altnoise@localhost' (Using password: YES) Any ideas? Thanks, james

By: VGR	Date: 05/06/2003 09:08:00		Type : Comment
bad password... or bad hostname...

By: paullamhkg	Date: 05/06/2003 12:43:00		Type : Answer
Hey VGR, I think James have used password for altnoise@localhost during the upload, and mysql havn't set to accept password for login for altnoise right? James just follow the procedure of VGR, only you need to set the password for user altnoise in mysql, or use no password during the upload. Paul

By: DullsVillager	Date: 05/06/2003 12:51:00		Type : Comment
If exporting tables with definitions/etc is what you're trying to do, check out <A HREF="http://www.cynergi.net/exportsql/">http://www.cynergi.net/exportsql/</a> It will create tables and load the data automatically, assuming you can load the SQL it generates. If you're just trying to open a CSV or TSV file, you probably have an incorrect name or password and just need to follow VGR's advice once you get your login corrected.

By: VGR	Date: 05/06/2003 15:50:00		Type : Comment
well, mysqldump (a standard command) does it also in the .SQL file generated you've the CREATE TABLE syntax and the INSERTs INTO ready

By: mquiroz	Date: 09/06/2003 14:44:00		Type : Comment
Hi altnoise! It's very, very easy: You need to make a DSN to your MySQL database through MyODBC if you don't already have it please get it. Then open your access db on Microsoft Access and on the "tables" window select the desired table eith the right button of your mouse. Then Select "export" In the "file type" section of the new window select "ODBC" (it's the last one) Then select the DSN you just made Voilá: your access table is now a MySQL table. Hope you try this, I guarantee you it works!!!

By: paullamhkg	Date: 17/08/2003 15:53:00		Type : Comment
James....please spare a moment and come back to this Q to tell us either our responses helped you or not. If one took the time to answer you, and did it correctly or helped you, I think deserves 1 minute from you to accept his answer. If not, your further details might give clues. If you found the solution some details are useful, cause others that might find this Q with search engines can use it as a reference.

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

Contact

page generated in 711.123940 milliseconds

Why Google AdSense ads ?

New Improved Search!