将 .sql 插入表文件转换为 excel [英] Converting .sql insert table file to excel

问题描述

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

I have several files with the extension .sql that are all formatted in the same way. I'd like to convert them to a usable format in excel (csv, a table or anything really). I do have a very big hurdle though, I cannot set up a MySQL server as I'm not a programmer and have IT restrictions. Similarly I can't install any applications as workarounds here.

除此之外，我所有的 SQL 代码都采用相同的格式:

With that out of the way, all my SQL code is in the same format:

--
-- 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 行，因此它们无法正常工作.此外，由于数据需要格式化(为每个部分插入表语句)以及时间限制，拆分数据并不是真正的选择.

I've tried using online converters (CodeBeautify) and another but I have over 12,000 lines so they are not going to work. Additionally, splitting up the data isn't really an option due to the formatting they require (insert table statements for each piece) and due to time constraints.

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

I've written a macro where you paste the entire SQL file into excel. It has issues because when I copy and paste the SQL into excel it's very brittle because it uses the text-to-columns settings so I have to be extremely careful with them. Additionally because the entire 12,000 line SQL insert table statement is on one line, excel just drops entire sections of the data. Most of my macro is about manipulating what does get copied so that it gets into table form. It definitely works with smaller data samples, but it doesn't scale well for the reasons just stated. Also, I'm doing some pretty sketchy hookups to this specific data dump, this wouldn't work for a generic create table file.

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

推荐答案

你可以尝试这样的事情

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 ( 的每一行，然后尝试清理该行.

This is something I've knocked together very quickly. It will open your sql file and read each line that contains VALUES ( and then try to clean the line.

这可能是特定于 sql 文件的，可能需要进行一些修改才能在您的情况下工作.让我知道你是怎么做的

This could be particular to the sql file and could need some modification to work in your case. Let me know how you get on with it

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

I used the example world.sql world database from here: MySQL World Database to write this.

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

This will populate all the cells in your excel sheet as strings so you may have to add in some handling for different data types (e.g. integers)

这篇关于将 .sql 插入表文件转换为 excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！