Is there a way to load an Xlsx LOAD DATA INFILE file (import) into a MySQL database table

Question

I know that this is being discussed a lot, but I cannot find a solution on how to do this.

I need to import an excel file (xls / xlsx) into my database table. This is the button that does this, and the command that runs is as follows:

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

It works great. But I need to import the excel file, not the CSV. As far as I know, the LOAD DATA command does not support binaries, which are xls. So what is the solution for this? Please, help

thanks a lot

Pepis

Answer 1

.xls will never be imported directly into MySQL. it is a complex OLE file, which means that its internal layout is not understood by mere mortals (or even Bill Gates) .. xlsx is just a .zip file that contains several .xml / xslt / etc. files. You can probably extract the corresponding .xml containing the actual data of the spreadsheets, but again - it is unlikely that it will be in the format that is directly imported when loading MySQL.

The easiest solution is to export .xls / xlsx to .csv.

Answer 2

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

Answer 3

How to import xlsx file into MySQL:

1 / Open the Office Excel “.xlsx” file and click “Save As” on the menu and select

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

from the Save As Type list. Finally click the "Save" button.

2 / Copy or upload the CSV file to your installed MySQL server (directory path, for example: '/ root / someDirectory /' on Linux servers)

3 / Log in to your database:

 mysql -u root -pSomePassword 

4 / Create and use the destination database:

 use db1 

5 / Create a MySQL table in your target database (for example, "db1") with columns such as those in the .csv file.

6 / Run 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; 

Note that the "IGNORE 1 LINES" option says that MySQL ignores the first line of the .csv file. Thus, it is easy for .xlsx files with 1 header column. You can remove this option.

Answer 4

Take a look at sqlizer.io . You can load XLSX files, give them a sheet name and a range of cells, and it will generate a CREATE TABLE statement and a group of INSERT statements to import all your data.