php - MySql 中的批量导入查询 - joomla

Question

我有一个包含 100 多个 .txt 文件的文件夹，我想将每个文件导入到我的 joomla 网站中的一篇文章中(因此最终会生成 100 多篇文章)

似乎最简单的方法是使用以下命令将我的.txt导入到现有的mysql中:

LOAD DATA LOCAL INFILE 'example.txt' INTO TABLE example_table

但是如何一次导入所有文件(批量导入)？

Answer 1

我使用这个宏(在 Visual Basic 中)将所有 .txt 导入 Excel 工作表:

'~~> Change path here
Const sPath As String = "C:\Users\Desktop\file\"

Sub Sample()
    Dim wb As Workbook
    Dim ws As Worksheet

    Dim MyData As String, tmpData() As String, strData() As String
    Dim strFileName As String

    '~~> Your requirement is of 267 files of 1 line each but I created 
    '~~> an array big enough to to handle 1000 files
    Dim ResultArray(1000, 3) As String

    Dim i As Long, n As Long

    Debug.Print "Process Started At : " & Now

    n = 1

    Set wb = ThisWorkbook

    '~~> Change this to the relevant sheet
    Set ws = wb.Sheets("Sheet1")

    strFileName = Dir(sPath & "\*.txt")

    '~~> Loop through folder to get the text files
    Do While Len(strFileName) > 0

        '~~> open the file in one go and read it into an array
        Open sPath & "\" & strFileName For Binary As #1
        MyData = Space$(LOF(1))
        Get #1, , MyData
        Close #1
        strData() = Split(MyData, vbCrLf)

        '~~> Collect the info in result array
        For i = LBound(strData) To UBound(strData)
            If Len(Trim(strData(i))) <> 0 Then
                tmpData = Split(strData(i), ",")

                ResultArray(n, 0) = Replace(tmpData(0), Chr(34), "")
                ResultArray(n, 1) = Replace(tmpData(1), Chr(34), "")
                ResultArray(n, 2) = Replace(tmpData(2), Chr(34), "")
                ResultArray(n, 3) = Replace(tmpData(3), Chr(34), "")

                n = n + 1
            End If
        Next i

        '~~> Get next file
        strFileName = Dir
    Loop

    '~~> Write the array to the Excel Sheet
    ws.Range("A1").Resize(UBound(ResultArray), _
    UBound(Application.Transpose(ResultArray))) = ResultArray

    Debug.Print "Process ended At : " & Now
End Sub

现在每一行都对应一个.txt。

按照要将 .txt 导入到的表中的方式组织列。例如，如果您想在 joomla 中导入文章，您的列应该是:id 标题别名 title_alias introtext 全文状态sectionid 掩码catid 创建的created_bycreated_by_alias 修改modified_bychecked_outchecked_out_timepublish_uppublish_down 图像urls attribs 版本parentid 排序metkey metadesc 访问命中元数据

如果您希望 mysql 在上传过程中自动增加 id，请将 id 字段留空。

将表另存为 .csv 并使用 phpmyadmin 将其导入 mysql(选择要导入 .csv 的表，然后选择导入选项卡。

!!如果您的内容中包含西文字符(例如 é、à...)，请将表格保存为 .xlsm。然后在 Open Office Calc 中打开它，然后将其另存为 .csv。在弹出的选择中，保持与西文字符相同的格式。

你已经完成了!