import excel data table into database with only phpMyAdmin access

In a specific table in a MySQL database a column needed to be populated with thousands of different values depending on a row-ID. The row-ID and value pairs could be found in an excel file / table. No direct access to MySQL was possible, only the import function of phpMyAdmin could be used.

Taggings:

Submitted by Marian Berendes on Sat, 11/17/2018 - 19:12
1 answer

First the excel file was converted to a CSV file using LibreOffice Calc.

Then using the web generator https://codebeautify.org/csv-to-sql-converter and entering some database specific details (tablename, columnname identifier, columnname targetvalue) and choosing "CSV to SQL(INSERT)" an SQL script was generated that inserts into each row with the identifier the corresponding value from the CSV file.

The output was saved as an .sql file.
A a database backup created.
Afterwards the sql-file was uploaded into phpMyAdmin as an import script and executed perfectly.

Submitted by Marian Berendes on Sat, 11/17/2018 - 19:23

Taggings:

Comments

The task becomes a problem when there is no excel parsing support.
This solution looks good and is described very well step by step with source of the tools used. +1

 Boian Velitchkov - Sun, 12/02/2018 - 21:09 :::

Nice and detailed solution. Very useful and important. Thank you for sharing.

 Malbora Sinaj - Sun, 12/09/2018 - 16:21 :::

This is really useful! I was wondering if there was such an option, thanks for sharing!

 Angelos Arampatzis - Mon, 12/10/2018 - 14:06 :::