Convert .mysql extension to xlsx-CodePudding

My supervisor was given a backup file from our company's cloud mysql database (adminstered by a 3rd party) The file has a .mysql extension. I can view some of the data using Notepad so I know it contains valid data. In my research I discovered this a deprecated extension. Due to some reporting requirements, I was asked to move this data into Excel. I know enough about databases of the five of us in the shop to be considered the "expert" (a scary thought)

Research I've done leads me to believe I would be required to do a LAMP install to convert the mysql file to PDO which I think I can then convert to Excel. That seems like overkill to me.

Is there a more direct route? Load a legacy version of MySQL and hope I can do some conversion in the workbench? The file is a little over 500MB.

I typically develop industrial controls in Python or C#.


    -- MySQL dump 10.13  Distrib 5.7.33, for Linux (x86_64)
    --
    -- Host: localhost    Database: company_name
    -- ------------------------------------------------------
    -- Server version   5.7.33-0ubuntu0.18.04.1


DROP TABLE IF EXISTS `ACTIVEMQ_MSGS`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
.
.
.
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ACTIVEMQ_MSGS` (
  `ID` bigint(20) NOT NULL,
  `CONTAINER` varchar(250) DEFAULT NULL,
  `MSGID_PROD` varchar(250) DEFAULT NULL,
  `MSGID_SEQ` bigint(20) DEFAULT NULL,
  `EXPIRATION` bigint(20) DEFAULT NULL,
  `MSG` longblob,
  `PRIORITY` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `ACTIVEMQ_MSGS_MIDX` (`MSGID_PROD`,`MSGID_SEQ`),
  KEY `ACTIVEMQ_MSGS_CIDX` (`CONTAINER`),
  KEY `ACTIVEMQ_MSGS_EIDX` (`EXPIRATION`),
  KEY `ACTIVEMQ_MSGS_PIDX` (`PRIORITY`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
.
.
.
LOCK TABLES `rh_blobs` WRITE;
/*!40000 ALTER TABLE `rh_blobs` DISABLE KEYS */;
INSERT INTO `rh_blobs` VALUES (data....)
INSERT INTO `rh_blobs` VALUES (data....)

CodePudding user response：

Assuming your file contains the text of a bunch of SQL statements, here's what you need to do.

Stand up a MySQL server temporarily.
Run the SQL statements in your file, one after the other in order, against that server.
Issue a SQL statement to write out a comma-separated-value (.csv) file for each table that's populated by that file of yours.
Import those .csv files into Excel.
Get rid of the MySQL server.

The version of the MySQL server you use is almost certainly irrelevant.

MySQL Workbench or the HeidiSQL client program, among many others, give you the tools you need for steps 2-4.

In MySQL Workbench, right-click the table to export and choose Table Data Export Wizard. In the wizard, choose csv export. Choose comma for the field separator, CR for the line separator. Enclose strings in ", the double quote character. And for null and NULL, choose NO.

Several cloud providers offer quick and easy ways to stand up MySQL servers. For example, Digital Ocean offers MySQL servers for pennies an hour. And they charge by the hour. If this little project costs you as much as five US dollars, that will be a surprise.

CodePudding user response：

Thanks to all for recommendations. Using mysql command line helped me solve the problem.

Install mysql on my desktop (Windows)
start mysql with the following. Entered root password at the prompt


cd c:\program files\mysql\mysql server 5.7\bin
mysql -u root -p

Create and restored the backup/archive file to a new database


create database company-name-report
use company-name-report
source c:\users\user_name\Downloads\company_name.mysql

Following @O. Jones advice, downloaded HeidiSQL and was able to view the data.
Should be a simple task now to export to CSV for use with Excel