如何将xls文件转换为mysql格式？

Question

I have tried the code below:

SELECT * INTO NewTablenNmeHere
FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet1$]')

I'm getting error:

[Err] 1327 - Undeclared variable: NewTablenNmeHere

Answer 1

OPENROWSET seems to be a MS SQL command. I don't think it has an equivalent in mySQL.

The easiest method that comes to mind is

• Save the XLS file as CSV
• Use LOAD DATA INFILE to import the file into mySQL.

You can also use a graphical mySQL client like HeidiSQL to use a point-and-click dialog to determine which column goes where.