Ingest multiple excel files to MySQL using query

Question

I am trying to load data from excel files into a table in MySql. There are 400 excel files in .xlsx format.

I have successfully ingested one file into the table but the problem is that involves manually converting excel file into a csv file, saving it on a location and then running a query to load using LOAD LOCAL INFILE. How to do it for rest of the files.

How to load all the 400 .xlsx files in a folder without converting them manually to .csv files and then running the ingestion query one by one on them.Is there a way in MySql to do that. For example, any FOR Loop that goes through all the files and ingest them in the table.

Δ O · Accepted Answer · 2020-06-23 15:45:10Z

0

Try bulk converting your XLSXs into CSVs using in2csv as found in csvkit.

## single file
in2csv file.xlsx > file.csv

## multiple files
for file in *.xlsx; do in2csv $file > $file.csv; done

Then import data into MySQL using LOAD LOCAL INFILE...

From loading multiple CSVs, use for file in *.csv; do... or see How to Import Multiple csv files into a MySQL Database.

edited Jun 23, 2020 at 15:45

answered Jun 23, 2020 at 15:38

Δ O

3,7382 gold badges22 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Ingest multiple excel files to MySQL using query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related