Query regarding export and inport from excel

Question

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

Answer 1

0

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.

answered Apr 10 '12 at 07:00 AM

Usman Butt
13.8k ● 6 ● 8 ● 14

(comments are locked)

Answer 2

0

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.

answered Apr 10 '12 at 12:58 PM

Dave_Green
1.7k ● 1 ● 4

(comments are locked)

Query regarding export and inport from excel

Follow this question

Related Questions