How can I perform excel insert MySQL?

Asked By Kathryn Masud 100 points N/A Posted on - 11/29/2012

 

Hi techyv,

How can I perform excel insert mysql? I made a database draft in Microsoft excel application. I want automatically transfer my excel files in to mysql database server. Would it be possible for me to perform this kind of tasks? The company needs the database as soon as possible and I have to find the best solution.

Hoping for your help and kind consideration.

• Status: Open
• Question Views: 739
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Mclean Buono

Go To Solution

Answered By Ramonag jenniferf 0 points N/A #184091

How can I perform excel insert MySQL?

Hi Kathryn,

It is possible to insert excel file into MySQL.  Save your excel file into CSV file using the ‘Save as’ command under ‘File’. Open you CSV file with notepad see what the look of the file is and what delimiter is used. Open MySQL application then type the following command:

LOAD DATA LOCAL INFILE ‘C:\temp\yourCSVfile’ INTO TABLE database.table FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘rn’ (field1, field2);

There you are done with the process. Hope this will help you.

Regards

About Ramonag jenniferf

Questions
1

Answers
109

Best Answers
29

• Vote Up 0 Vote Down

• Posted on - 11/29/2012
• Question Category: MS Excel

Best Answer

Best Answer

Answered By Mclean Buono 10 points N/A #184092

How can I perform excel insert MySQL?

Hello Kathryn,

You can as well try using mysqlimport utility, which is actually a command line alternative to LOAD DATA INFILE statement.

So your process would be –

1. Create corresponding tables in MySQL (this is a must).
2. Convert Excel to CSV using steps mentioned above.
3. Execute this command – mysqlimport -u [username] -p -d [database] [/path-to-csvfile/filename] FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘"‘ LINES TERMINATED BY 'rn'
   1. [username] should be replaced by MySQL username
   2. [database] should be replaced with existing database into which tables were created and where data has to be inserted.
   3. [/path-to-csvfile/filename] is the path to the CSV file.
4. Execute step for 3 for as many CSV files as you have.

Once again, it is important to create the tables otherwise mysqlimport statements would result in error.

Regards

Mclean

About Mclean Buono

Questions
6

Answers
99

Best Answers
33

• Vote Up 0 Vote Down

• Posted on - 06/25/2013
• Question Category: MS Excel