How can I perform excel insert MySQL?

Asked By 100 points N/A Posted on -
qa-featured

 

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.

SHARE
Best Answer by Mclean Buono
Answered By 0 points N/A #184091

How can I perform excel insert MySQL?

qa-featured

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

Best Answer
Best Answer
Answered By 10 points N/A #184092

How can I perform excel insert MySQL?

qa-featured

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

Login/Register to Answer

Related Questions