x

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 '12 at 06:40 AM in Default

Raj007 gravatar image

Raj007
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 '12 at 07:00 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(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 '12 at 12:58 PM

Dave_Green gravatar image

Dave_Green ♦
3.6k 4 7

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x66

asked: Apr 10 '12 at 06:40 AM

Seen: 907 times

Last Updated: Apr 10 '12 at 02:05 PM