Query regarding export and inport from excel

 INSERT INTO department SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
 'Excel 8.0;Database=D:\dept.xls;HDR=YES',
 'SELECT * FROM [dept$]')

Using this to import data from excel to tables of database.... But i am getting this error

(You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=D:\dept.xls;HDR=YES', 'SELECT * ' at line 1 ) I need some ones help
more ▼

asked Apr 10, 2012 at 06:40 AM in Default

avatar image

0 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

AFAIK, this is the syntax for MSSQL not for MySQL. For MySQL you would have to either export the excel file to text file and then import Or search it over the internet, you may find some better alternative or free tools.

more ▼

answered Apr 10, 2012 at 07:00 AM

avatar image

Usman Butt
14.6k 6 13 21

(comments are locked)
10|1200 characters needed characters left

Usman is right, there doesn't seem to be a direct native method for import from Excel format into MySQL, you need to save as CSV or text.

There is a worked example at http://blog.tjitjing.com/index.php/2008/02/import-excel-data-into-mysql-in-5-easy.html which runs through all the necessary steps using load data infile.

The Manual has some more detail at http://dev.mysql.com/doc/refman/5.6/en/load-data.html, including some potentially relevant comments at the bottom.

more ▼

answered Apr 10, 2012 at 12:58 PM

avatar image

Dave_Green ♦
5.6k 4 5 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 10, 2012 at 06:40 AM

Seen: 1251 times

Last Updated: Apr 10, 2012 at 02:05 PM

Copyright 2018 Redgate Software. Privacy Policy