使用VB和宏在excel中创建查询

时间：2021-02-06 23:35:35

I have a lot of data in excel with different columns. My task is to create insert statement for each row. I have done using concatenate function of excel but I want to automate this method. The other task is to replace ' (single quotes) with ''(2 single quotes) so that characters can be adjusted in the sql statement. The main is once I get the excel, I run my script which will automatically make all the queries. And after that, I will run those queries manually in SQL developer or TOAD.

我在excel中有很多不同列的数据。我的任务是为每一行创建insert语句。我已经使用了excel的连接函数，但是我想自动化这个方法。另一个任务是用“(2个单引号)”替换'(单引号)，以便在sql语句中调整字符。主要是，一旦我获得excel，我就会运行我的脚本，它会自动执行所有查询。然后，我将在SQL developer或TOAD中手动运行这些查询。

1 个解决方案

#1

1  

Without any information about your table and your Excel data, here's an example supposing that your excel data is in columns A:D starting from row 2, with the same layout as your table. I also suppose that all your fields are text type.

没有关于表和Excel数据的任何信息，这里有一个例子，假设您的Excel数据位于A:D列，从第2行开始，布局与您的表相同。我还假设您的所有字段都是文本类型。

Sub createQueries()

    Dim r As Range, cel As Range, record As String

    'Get the range to export
    With Sheet1
        Set r = .Range("A2:D" & .Range("D999999").End(xlUp).Row)
    End With

    ' Create a text file for the query
    With CreateObject("Scripting.FileSystemObject").CreateTextFile("c:\SO\queries.txt")
        For Each r In r.Rows
            ' create an Insert line into the query
            record = "INSERT INTO MYTABLE (""FieldA"", ""FieldB"", ""FieldC"", ""FieldD"") Values ("
            For Each cel In r.Cells
                record = record & "'" & Replace(cel.Text, "'", "''") & "',"
            Next
            record = Left(record, Len(record) - 1) & ");"
            .WriteLine record
        Next
        .Close
    End With
End Sub

#1

1  

Without any information about your table and your Excel data, here's an example supposing that your excel data is in columns A:D starting from row 2, with the same layout as your table. I also suppose that all your fields are text type.

没有关于表和Excel数据的任何信息，这里有一个例子，假设您的Excel数据位于A:D列，从第2行开始，布局与您的表相同。我还假设您的所有字段都是文本类型。

Sub createQueries()

    Dim r As Range, cel As Range, record As String

    'Get the range to export
    With Sheet1
        Set r = .Range("A2:D" & .Range("D999999").End(xlUp).Row)
    End With

    ' Create a text file for the query
    With CreateObject("Scripting.FileSystemObject").CreateTextFile("c:\SO\queries.txt")
        For Each r In r.Rows
            ' create an Insert line into the query
            record = "INSERT INTO MYTABLE (""FieldA"", ""FieldB"", ""FieldC"", ""FieldD"") Values ("
            For Each cel In r.Cells
                record = record & "'" & Replace(cel.Text, "'", "''") & "',"
            Next
            record = Left(record, Len(record) - 1) & ");"
            .WriteLine record
        Next
        .Close
    End With
End Sub
标签：

相关文章