How do we import or export data in MySql

Jeyaseelansarc · #1 (**permalink**) 08-14-2007, 01:37 AM

hi,
How do we import or export data in MySql from outside sources?

For example if i have DATA in XML file, here i want to import to DB with MySql server. How can i do that?

write2ashokkumar · #2 (**permalink**) 08-14-2007, 03:14 AM

Hi...

We can export the mysql table data to text file, and also possible to import from the text file to table.

Example:

Consider the table name is sample.

Query to Export data to text file :

SELECT id,name INTO OUTFILE '/home/www/htdocs/test/backup_test.xls' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' from sample;

Query to Import data from text file :

LOAD DATA INFILE '/home/www/htdocs/test/backup_test.txt' INTO TABLE sample FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Regards,
S.Ashokkumar

Jeyaseelansarc · #3 (**permalink**) 08-14-2007, 03:23 AM

Hi ashok,
thanks for you reply. I hope that it would help me lot. I have small doubt that is it possible to import or export from XML file?

Becaz i have used openxml in SQL server to get XML tag values. is there any chance to use like this?

Murali · #4 (**permalink**) 08-14-2007, 03:58 AM

Quote:

Originally Posted by Jeyaseelansarc

hi,
How do we import or export data in MySql from outside sources?

For example if i have DATA in XML file, here i want to import to DB with MySql server. How can i do that?

Refer this.

write2ashokkumar · #5 (**permalink**) 08-14-2007, 04:27 AM

hi...

We can export the table data as XML file.

Command to export table data as XML file:

Syntax:

mysqldump -u root -p --xml <database_name> [table_name] > file_path;

Ex:

mysqldump -u root -p --xml test sample > /home/www/htdocs/test/bktest.xml;

Regards,
S.Ashokkumar.

Murali · #6 (**permalink**) 08-14-2007, 04:40 AM

Quote:

Originally Posted by Jeyaseelansarc

Hi ashok,
thanks for you reply. I hope that it would help me lot. I have small doubt that is it possible to import or export from XML file?

Becaz i have used openxml in SQL server to get XML tag values. is there any chance to use like this?

Refer this.

Jeyaseelansarc · #7 (**permalink**) 08-14-2007, 04:42 AM

hi ashok,
this is ok for exporting data into XML file. is there any way to import XML data into DB?

Murali · #8 (**permalink**) 08-14-2007, 08:47 AM

Hi Jeyaseelan,

Import XML Data to MYSQL Database.

Available only from MYSQL 5.1 which is in beta atpresent.

Murali · #9 (**permalink**) 08-17-2007, 07:25 AM

Here is a sample for Export and Import XML in MYSQL

Code:

DROP TABLE IF EXISTS member;
CREATE TABLE member
(
	memberid 			INTEGER		PRIMARY KEY,
	member_fname 	VARCHAR(50),
	member_lname 	VARCHAR(50),
	member_email	VARCHAR(50)
);

INSERT INTO member VALUES (1,'Ram','Kumar','Hairam@hotmail.com');
INSERT INTO member VALUES (2,'Mukesh','Chandar','Mukesh@yahoo.co.in');
COMMIT;

Exporting table values to XML [Available in MYSQL5.0]

Code:

DB@Server# mysql -u <UserName> -p <Password> <DatabaseName> -X -e "SELECT * FROM members " > <File Destination Path .xml;

Check the file in the specified path

Code:


<?xml version="1.0"?>
<resultset statement="SELECT * FROM member">
  <row>
        <field name="memberid">1</field>
        <field name="member_fname">Ram</field>
        <field name="member_lname">Kumar</field>
        <field name="member_email">Hairam@hotmail.com</field>
  </row>

  <row>
        <field name="memberid">2</field>
        <field name="member_fname">Mukesh</field>
        <field name="member_lname">Chandar</field>
        <field name="member_email">Mukesh@yahoo.co.in</field>
  </row>
</resultset>

Import XML data to MYSQL Table[Available in MYSQL5.1]

Code:

mysql> CREATE TABLE members LIKE member; 
Query OK, 0 rows affected (0.00 sec)

Code:

DB@Server# mysql -u <UserName> -p <Password> <DatabaseName> -e "LOAD XML DATA INFILE '<XML FilePath' INTO TABLE members";

Check the Output

Code:


mysql> SELECT * FROM members;
+----------+--------------+--------------+--------------------+
| memberid | member_fname | member_lname | member_email       |
+----------+--------------+--------------+--------------------+
|        1 | Ram          | Kumar        | Hairam@hotmail.com |
|        2 | Mukesh       | Chandar      | Mukesh@yahoo.co.in |
+----------+--------------+--------------+--------------------+
2 rows in set (0.00 sec)

Hope it helps....

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
please tell me how to import excel data to sql server without datagrid	oxygen	C# Programming	2	12-16-2007 09:33 PM
Encrypting data in mysql	eric	PHP Programming	2	07-30-2007 06:08 AM
How can we import and export using BCP utility in SQL?	oxygen	Database Support	1	07-26-2007 04:33 AM
How to import MySQL Dump files?	kingmaker	Database Support	2	07-24-2007 04:47 AM
How to import foxpro tables into MySQL Databases	priyan	Database Support	2	05-21-2007 07:11 AM