import .sql file to mysql (to finally import to excel)

Question

So I've been trying to import a crunchbase 2013 snapshot (a mysql dump file) to excel. I've been trying to first import it into a db either through mysql command line or mysql worbench 6.3, but I've been having no luck so far. (Working on Windows)

My first step was to combine all the files into a single dump file. Then I tried importing but got this error:

ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: ''.

Then I tried converting it from ANSI to UTF8 and reopened. I got errors related to initial garbage values. I deleted them and then tried importing again. Here's the new error:

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"cb_acquisitions" ( "id" bigint(20) NOT NULL, "acquisition_id" bigint(20) NO' at line 1

This is what the regular .sql file looks like:

   Mac OS X                2   „      ¶                                      ATTR       ¶   ˜                     ˜     com.apple.quarantine q/0001;52aa111a;Transmit.app;     Mac OS X                2   „      ¶                                      ATTR       ¶   ˜                     ˜     com.apple.quarantine q/0001;52aa111a;Transmit.app;     Mac OS X                2   „      ¶                                      ATTR       ¶   ˜                     ˜     com.apple.quarantine q/0001;52aa111a;Transmit.app;     Mac OS X                2   „      ¶                                      ATTR       ¶   ˜                     ˜     com.apple.quarantine q/0001;52aa111a;Transmit.app;     Mac OS X                2   „      ¶                                      ATTR       ¶   ˜                     ˜     com.apple.quarantine q/0001;52aa111a;Transmit.app;     Mac OS X                2   „      ¶                                      ATTR       ¶   ˜                     ˜     com.apple.quarantine q/0001;52aa111a;Transmit.app;     Mac OS X                2   „      ¶                                      ATTR       ¶   ˜                     ˜     com.apple.quarantine q/0001;52aa111a;Transmit.app;     Mac OS X                2   „      ¶                                      ATTR       ¶   ˜                     ˜     com.apple.quarantine q/0001;52aa111a;Transmit.app;     Mac OS X                2   „      ¶                                      ATTR       ¶   ˜                     ˜     com.apple.quarantine q/0001;52aa111a;Transmit.app;     Mac OS X                2   „      ¶                                      ATTR       ¶   ˜                     ˜     com.apple.quarantine q/0001;52aa111a;Transmit.app;     Mac OS X                2   „      ¶                                      ATTR       ¶   ˜                     ˜     com.apple.quarantine q/0001;52aa111a;Transmit.app; -- MySQL dump 10.13  Distrib 5.5.31, for debian-linux-gnu (x86_64)
--
-- Host: 127.0.0.1    Database: analytics_2
-- ------------------------------------------------------
-- Server version   5.0.51-log
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO,POSTGRESQL' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Not dumping tablespaces as no INFORMATION_SCHEMA.FILES table on this server
--

--
-- Table structure for table "cb_acquisitions"
--

DROP TABLE IF EXISTS "cb_acquisitions";
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE "cb_acquisitions" (
  "id" bigint(20) NOT NULL,
  "acquisition_id" bigint(20) NOT NULL,
  "acquiring_object_id" varchar(64) NOT NULL,
  "acquired_object_id" varchar(64) NOT NULL,
  "term_code" varchar(16) default NULL,
  "price_amount" decimal(15,0) default NULL,
  "price_currency_code" varchar(16) default NULL,
  "acquired_at" date default NULL,
  "source_url" varchar(255) default NULL,
  "source_description" varchar(255) default NULL,
  "created_at" datetime default NULL,
  "updated_at" datetime default NULL,
  PRIMARY KEY  ("id"),
  KEY "acquiring_object_id" ("acquiring_object_id"),
  KEY "acquired_object_id" ("acquired_object_id"),
  KEY "acquisition_id" ("acquisition_id")
);
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table "cb_acquisitions"
--

LOCK TABLES "cb_acquisitions" WRITE;
/*!40000 ALTER TABLE "cb_acquisitions" DISABLE KEYS */;
INSERT INTO "cb_acquisitions" VALUES (1,1,'c:11','c:10',NULL,20000000,'USD','2007-05-30','http://venturebeat.com/2007/05/30/fox-interactive-confirms-purchase-of-photobucket-and-flektor/','Fox Interactive confirms purchase of Photobucket and Flektor','2007-05-31 22:19:54','2008-05-21 19:23:44'),(2,7,'c:59','c:72','cash',60000000,'USD','2007-07-01','http://www.techcrunch.com/2007/07/02/dea

Answer 1

I managed to get it to run with mysql command line using:

    mysql -u root -p --binary-mode=1
    mysql> use dbname;
    mysql> source /path/of/file/name.sql

Answer 2

First import the .sql dump file into MySQL to generate a cb_acquisitions table. Then use SELECT ... INTO OUTFILE to write your table to regular CSV, and then import that into Excel:

SELECT *
FROM cb_acquisitions
INTO OUTFILE '/path/to/output.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

With regard to your dump file, I think the double quotes around the table and field names might be a problem. I got the create table and insert to work, q.v. the demo below, but I had to play around with the script slightly.

Rextester