Is there a way of LOAD DATA INFILE (import) xlsx file into MySQL database table

Question

I know that this is discussed a lot but I don't find solution of how to do that.

What I need is to import an excel file (xls/xlsx) to my database table. It is a button which does that and the command which is executed is like that:

 string cmdText = "LOAD DATA INFILE 'importTest4MoreMore.csv' INTO TABLE management FIELDS TERMINATED BY ',';";

It works great. But I need to import excel file not CSV. As far as I know LOAD DATA command does not support binary files which xls is. So what's the solution to that? Please help

Thanks a lot

pepys

Solution 1

.xls will never be importable directly into MySQL. it's a compound OLE file, which means its internal layout is not understandable by mere mortals (or even Bill Gates). .xlsx is basically just a .zip file which contains multiple .xml/xslt/etc. files. You can probably extract the relevant .xml that contains the actual spreadsheet data, but again - it's not likely to be in a format that's directly importable by MySQL's load infile.

The simplest solution is to export the .xls/xlsx to a .csv.

Solution 2

How to import 'xlsx' file into MySQL:

1/ Open your '.xlsx' file Office Excel and click on 'Save As' button from menu and select

 'CSV (MS-DOS) (*.csv)' 

from 'Save as type' list. Finally click 'Save' button.

2/ Copy or upload the .csv file into your installed MySQL server (a directory path like: '/root/someDirectory/' in Linux servers)

3/ Login to your database:

mysql -u root -pSomePassword

4/ Create and use destination database:

use db1

5/ Create a MySQL table in your destination database (e.g. 'db1') with columns like the ones of '.csv' file above.

6/ Execute the following command:

LOAD DATA INFILE '/root/someDirectory/file1.csv' INTO TABLE `Table1` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

Please note that the option 'IGNORE 1 LINES' says MySQL to ignore the first line of '.csv' file. So, it is just for '.xlsx' files with 1 header column. You can remove this option.

Solution 3

You can load xls or xlsx files with Data Import tool (MS Excel or MS Excel 2007 format) in dbForge Studio for MySQL. This tool opens Excel files directly, COM interface is not used; and command line is supported.