将数据从Excel导出到SQL Server

I am trying to push data from Excel to SQL Server using bcp command for multiple tables. Currently I have setup my code such as it creates the text files of the data(in a loop) which needs to be pushed and then run a bcp command mentioned below

我正在尝试使用bcp命令将数据从Excel推送到SQL Server。目前我已经设置了我的代码,例如它创建了需要被推送的数据的文本文件(在循环中),然后运行下面提到的bcp命令

bcp dbname.schema_name.table_name out C:\table_name.txt -n -S localhost -U username -P password -b 10000

However I would like to do away with the loop and want to run a single bcp command with names of multiple tables as first argument and different text files as second arguments. I tried finding the solution for this online but couldn't come up with anything.

但是,我想取消循环并希望运行单个bcp命令,其中多个表的名称作为第一个参数,不同的文本文件作为第二个参数。我尝试在网上找到这个解决方案,但无法想出任何东西。

Any help will be much appreciated.

任何帮助都感激不尽。

Just a bit of background: the reason I want to do away with the loop and run a single command is, I am observing strange behavior of my code which is when I step through my code it is able to push all the data to SQL Server, However when I run the entire process at once by clicking on the button to which macro is assigned, the code still runs but don't push any data to SQL Server.

只是一点背景:我想要取消循环并运行单个命令的原因是,我观察到我的代码的奇怪行为,当我单步执行我的代码时,它能够将所有数据推送到SQL Server ,但是当我通过单击分配宏的按钮立即运行整个过程时,代码仍然运行但不会将任何数据推送到SQL Server。

To resolve this I tried putting doevents just after the bcp command but all went in vain, then I setup a wait for 5 seconds just after bcp command and it seems to work, but I feel setting up wait is kinda risky business I will never be sure what should be the wait time.

为了解决这个问题,我尝试在bcp命令之后添加doevents但是一切都没有用,然后我在bcp命令之后设置等待5秒并且它似乎工作,但我觉得设置等待是有点冒险的业务我永远不会确定等待时间应该是多少。

2 个解决方案

#1

A single BCP command can only push data to a single table. There is no way to pass multiple table names to a single BCP command.

单个BCP命令只能将数据推送到单个表。无法将多个表名传递给单个BCP命令。

#2

There's nothing wrong with a good old fashioned loop to handle the task.

一个好的老式循环来处理任务没有错。

Sub Button1_Click()

    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim sCustomerId, sFirstName, sLastName As String

    With Sheets("Sheet1")

        'Open a connection to SQL Server
        conn.Open "Provider=SQLOLEDB;Data Source=ASUSBOOK\SQL2012;Initial Catalog=ExcelDemo;Integrated Security=SSPI;"

        'Skip the header row
        iRowNo = 2

        'Loop until empty cell in CustomerId
        Do Until .Cells(iRowNo, 1) = ""
            sCustomerId = .Cells(iRowNo, 1)
            sFirstName = .Cells(iRowNo, 2)
            sLastName = .Cells(iRowNo, 3)

            'Generate and execute sql statement to import the excel rows to SQL Server table
            conn.Execute "insert into dbo.Customers (CustomerId, FirstName, LastName) values ('" & sCustomerId & "', '" & sFirstName & "', '" & sLastName & "')"

            iRowNo = iRowNo + 1
        Loop

        MsgBox "Customers imported."

        conn.Close
        Set conn = Nothing

    End With

End Sub

Also, check out this link.

另外,请查看此链接。

https://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm

If the loop scenario is just too slow for you, consider saving your data sets as CSV files and do a bulk insert.

如果循环场景对您来说太慢,请考虑将数据集保存为CSV文件并执行批量插入。

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
DROP TABLE CSVTest
GO

https://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

#1