mysql - 将 .sql 插入表文件转换为 Excel

Question

我有几个扩展名为 .sql 的文件，它们的格式都相同。我想将它们转换为 excel 中可用的格式(csv、表格或任何其他格式)。但我确实有一个很大的障碍，我无法设置 MySQL 服务器，因为我不是程序员并且有 IT 限制。同样，我无法在此处安装任何应用程序作为解决方法。

排除了这一点，我所有的 SQL 代码都采用相同的格式:

--
-- Table structure for table `cruises`
--

DROP TABLE IF EXISTS `cruises`;
CREATE TABLE `cruises` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `project` varchar(32) DEFAULT NULL,
  `itinerary_id` varchar(255) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `ship_name` varchar(64) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `departure_port` varchar(64) DEFAULT NULL,
  `departure_time` timestamp NULL DEFAULT NULL,
  `inside` int(11) DEFAULT '0',
  `oceanview` int(11) DEFAULT '0',
  `balcony` int(11) DEFAULT '0',
  `suite` int(11) DEFAULT '0',
  `studio` int(11) NOT NULL DEFAULT '0',
  `spa` int(11) NOT NULL DEFAULT '0',
  `haven` int(11) NOT NULL DEFAULT '0',
  `is_lowest_price` tinyint(1) DEFAULT '0',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique` (`project`,`itinerary_id`,`title`,`ship_name`,`duration`,`departure_port`,`departure_time`)
) ENGINE=InnoDB AUTO_INCREMENT=28076 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `cruises`


LOCK TABLES `cruises` WRITE;
INSERT INTO `cruises` VALUES (22625,'disneycruise','10_europe_copenhagen_dover','10-Night Northern Europe Cruise from Copenhagen to Dover','Disney Magic',10,'Copenhagen','2017-06-18 00:00:00',6805,7585,10585,30585,0,0,0,0,'2016-05-14 10:15:19'),(22634,'disneycruise','10_mediteranean_barcelona','10-Night Mediterranean Cruise from Barcelona','Disney Magic',10,'Barcelona','2017-07-28 00:00:00',6556,7856,10456,31456,0,0,0,0,'2016-05-14 10:15:19')

我尝试过使用在线转换器 (CodeBeautify) 和另一个，但我有超过 12,000 行，所以它们无法工作。此外，由于数据所需的格式(为每部分插入表语句)和时间限制，拆分数据并不是真正的选择。

我编写了一个宏，您可以将整个 SQL 文件粘贴到 Excel 中。它有问题，因为当我将 SQL 复制并粘贴到 Excel 中时，它非常脆弱，因为它使用文本到列设置，所​​以我必须非常小心地对待它们。此外，由于整个 12,000 行 SQL 插入表语句都在一行上，因此 Excel 只会删除整个数据部分。我的大部分宏都是关于操纵复制的内容，使其变成表格形式。它确实适用于较小的数据样本，但由于刚才所述的原因，它不能很好地扩展。另外，我正在对这个特定的数据转储进行一些相当粗略的连接，这对于通用创建表文件不起作用。

Sub SQLtoExcelConverter()
'Paste your SQL from Apple's Cruise Center into A1 of the Input sheet.

Dim OutputData As Worksheet
Dim SQLData As Worksheet
Set OutputData = ThisWorkbook.Sheets("Output")
Set SQLData = ThisWorkbook.Sheets("Input")

'Find out the rows in which the headers for the SQL data are residing
titleRowStart = SQLData.Columns.Find("CREATE TABLE").Row + 1
titleRowEnd = SQLData.Columns.Find("PRIMARY KEY").Row - 1

'Places headers on the Output Page
For i = titleRowStart To titleRowEnd
    OutputData.Cells(1, i + 1 - titleRowStart).Formula = "=MID(Input!A" & i & ", FIND(""`"",Input!A" & i & ")+1, FIND(""`"", Input!A" & i & ", FIND(""`"", Input!A" & i & ")+1)-FIND(""`"",Input!A" & i & ")-1)"
    OutputData.Cells(1, i + 1 - titleRowStart).Value = OutputData.Cells(1, i + 1 - titleRowStart).Value
Next i

'Finds the Rows where thetable data is sorted. Extremely sketch code here
dataRowstart = SQLData.Columns.Find("INSERT INTO", SearchOrder:=xlByColumns).Row
dataRowEnd = SQLData.Columns.Find("/*!40000 ALTER TABLE `cruises` ENABLE KEYS */;").Row - 1

'Cleans the data
SQLData.Range("A" & dataRowstart & ":A" & dataRowEnd).Select
'Removes the initial table setting
Selection.Replace What:="INSERT INTO `cruises` VALUES (", Replacement:="", _
    LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat _
    :=False, ReplaceFormat:=False
'Replaces the delimiter between rows by a strange character char(19) since excel needs a 1 character delimiter
ch1 = Chr(19)
Selection.Replace What:="),(", Replacement:=ch1, LookAt:=xlPart, _
    SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
'Starts the separation process
Selection.TextToColumns Destination:=Range("A" & dataRowstart), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=ch1, TrailingMinusNumbers:=True
'Heals data that was chopped in the inital copy into the spreadsheet
For i = dataRowstart + 1 To dataRowEnd
    SQLData.Cells(i, 1).Value = Chr(39) & SQLData.Cells(i, 1)
    SQLData.Cells(i - 1, FindNextEmpty(SQLData.Cells(i - 1, 1)).Column - 1).Value = SQLData.Cells(i - 1, FindNextEmpty(SQLData.Cells(i - 1, 1)).Column - 1).Value & SQLData.Cells(i, 1).Value
    SQLData.Cells(i, 1).Delete Shift:=xlShiftToLeft
Next i

'Copies all the data over to the Output page
SQLData.Rows(dataRowstart & ":" & dataRowEnd).Select
i = 2
For Each c In Selection
    If Not IsEmpty(c) Then
        OutputData.Cells(i, 1).Value = c.Value
        i = i + 1
    End If
Next c

'Splits all the data again by commas
OutputData.Range("A2:A" & ColumnLength("A", OutputData)).TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
    TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, OtherChar:= _
    ch2, TrailingMinusNumbers:=True

'Resets TextToColumns settings so that the next copy-paste isn't messed up
SQLData.Range("I1").Value = 1
SQLData.Range("I1").TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
        :="~", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
y = FindNextEmpty(OutputData.Cells(1, 1)).Column - 1
OutputData.Cells(OutputData.Cells(Rows.Count, y).End(xlUp).Row, y).Replace What:=");", Replacement:="", _
    LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat _
    :=False, ReplaceFormat:=False

MsgBox "If your data came out all wrong it's possible the text to columns settings were tampered with. Try recopying your data and running the macro again."
End Sub

Public Function FindNextEmpty(ByVal rCell As Range) As Range
'Finds the first empty cell downwards in a column.



With rCell
   'If the start cell is empty it is the first empty cell.
   If Len(.Formula) = 0 Then
      Set FindNextEmpty = rCell
      'If the cell just below is empty
   ElseIf Len(.Offset(0, 1).Formula) = 0 Then
      Set FindNextEmpty = .Offset(0, 1)
   Else
      'Finds the last cell with content.
      '.End(xlDown) is like pressing CTRL + arrow down.
      Set FindNextEmpty = .End(xlToRight).Offset(0, 1)
   End If
End With
End Function
Public Function ColumnLength(Column As String, ByVal WS As Worksheet) As Long

ColumnLength = WS.Cells(Rows.Count, Column).End(xlUp).Row


End Function

Answer 1

你可以尝试这样的事情

Sub ImportMySQLDumptoExcel()
    Dim intF As Integer
    Dim i As Long
    Dim vSql() As String, strSql As String, temp As String
    Dim vSqls As Variant, tempArr As Variant

    intF = FreeFile()
    ' Set this to your .sql file path
    Open "SQLDUMPFILEPATH" For Input As #intF
    strSql = Input(LOF(intF), #intF)
    Close intF
    vSql = Split(strSql, ";")

    ' Row Counter
    i = 1

    For Each vSqls In vSql
        If InStr(1, vSqls, "Values (", vbTextCompare) > 0 Then
            temp = Trim(Right(vSqls, Len(vSqls) - (InStr(1, vSqls, "Values (", vbTextCompare) + 7)))
            temp = Trim(Left(temp, Len(temp) - 1))
            temp = Replace(temp, "'", "")
            tempArr = Split(temp, ",")

            ' Change this to where you want your results to go
            With Sheet1
                Range(.Cells(i, 1), .Cells(i, UBound(tempArr))) = tempArr
                i = i + 1
            End With
        End If
    Next
End Sub

这是我很快就想到的东西。它将打开您的 sql 文件并读取包含 VALUES ( 的每一行，然后尝试清理该行。

这可能是 sql 文件特有的，可能需要进行一些修改才能适用于您的情况。让我知道你的进展如何

我使用了这里的示例world.sql世界数据库:MySQL World Database写这个。

这会将 Excel 工作表中的所有单元格填充为字符串，因此您可能需要添加针对不同数据类型(例如整数)的一些处理