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

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

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