vba - 如何创建循环以从 Excel 上传到 PostgresSQL 表
问题描述
我有一个完全相同的 excel 表和一个 Postgres 表。到目前为止,我编写的代码将成功地将 excel 表中的第一行插入 Postgres 表中。我需要帮助的是创建一个循环来执行此操作,直到它到达 excel 范围的最后一行。我已经尝试了一些东西,但似乎无法让它发挥作用。代码如下:
也开放其他想法以更有效地执行此操作。如果有不同的方法可以做到这一点,那就太好了,因为如果你有一个包含大量列的表,这个过程将非常低效。
Sub Upload_Records()
'Open Connection
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Set conn = New ADODB.Connection
conn.ConnectionString = "DSN=vsbslgprd01;DATABASE=postgres;SERVER=vsbslgprd01.zmr.zimmer.com;PORT=5432;UID=breedenz;PWD=110percent;"
conn.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = "insert into asset_management.expired_recalled values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
'Set Parameters (Columns)
Dim LRow As Long
Dim pm1 As ADODB.Parameter
Dim pm2 As ADODB.Parameter
Dim pm3 As ADODB.Parameter
Dim pm4 As ADODB.Parameter
Dim pm5 As ADODB.Parameter
Dim pm6 As ADODB.Parameter
Dim pm7 As ADODB.Parameter
Dim pm8 As ADODB.Parameter
Dim pm9 As ADODB.Parameter
Dim pm10 As ADODB.Parameter
Dim pm11 As ADODB.Parameter
Dim pm12 As ADODB.Parameter
Dim pm13 As ADODB.Parameter
Dim pm14 As ADODB.Parameter
Dim pm15 As ADODB.Parameter
Dim pm16 As ADODB.Parameter
Dim pm17 As ADODB.Parameter
Dim pm18 As ADODB.Parameter
Dim pm19 As ADODB.Parameter
Dim pm20 As ADODB.Parameter
Dim pm21 As ADODB.Parameter
Dim pm22 As ADODB.Parameter
Dim pm23 As ADODB.Parameter
Dim pm24 As ADODB.Parameter
Dim pm25 As ADODB.Parameter
Dim pm26 As ADODB.Parameter
Dim pm27 As ADODB.Parameter
Set pm1 = cmd.CreateParameter("run_date", adVarChar, adParamInput, 1000)
pm1.Value = Sheets("Load").Range("H3").Value
cmd.Parameters.Append pm1
Set pm2 = cmd.CreateParameter("legacy", adVarChar, adParamInput, 1000)
pm2.Value = Sheets("Load").Range("I3").Value
cmd.Parameters.Append pm2
Set pm3 = cmd.CreateParameter("bucket", adVarChar, adParamInput, 1000)
pm3.Value = Sheets("Load").Range("J3").Value
cmd.Parameters.Append pm3
Set pm4 = cmd.CreateParameter("terr", adVarChar, adParamInput, 1000)
pm4.Value = Sheets("Load").Range("K3").Value
cmd.Parameters.Append pm4
Set pm5 = cmd.CreateParameter("dist_name", adVarChar, adParamInput, 1000)
pm5.Value = Sheets("Load").Range("L3").Value
cmd.Parameters.Append pm5
Set pm6 = cmd.CreateParameter("site", adVarChar, adParamInput, 1000)
pm6.Value = Sheets("Load").Range("M3").Value
cmd.Parameters.Append pm6
Set pm7 = cmd.CreateParameter("team_name", adVarChar, adParamInput, 1000)
pm7.Value = Sheets("Load").Range("N3").Value
cmd.Parameters.Append pm7
Set pm8 = cmd.CreateParameter("location", adVarChar, adParamInput, 1000)
pm8.Value = Sheets("Load").Range("O3").Value
cmd.Parameters.Append pm8
Set pm9 = cmd.CreateParameter("contained_in", adVarChar, adParamInput, 1000)
pm9.Value = Sheets("Load").Range("P3").Value
cmd.Parameters.Append pm9
Set pm10 = cmd.CreateParameter("customer_number", adVarChar, adParamInput, 1000)
pm10.Value = Sheets("Load").Range("Q3").Value
cmd.Parameters.Append pm10
Set pm11 = cmd.CreateParameter("product_group", adVarChar, adParamInput, 1000)
pm11.Value = Sheets("Load").Range("R3").Value
cmd.Parameters.Append pm11
Set pm12 = cmd.CreateParameter("item", adVarChar, adParamInput, 1000)
pm12.Value = Sheets("Load").Range("S3").Value
cmd.Parameters.Append pm12
Set pm13 = cmd.CreateParameter("item_desc", adVarChar, adParamInput, 1000)
pm13.Value = Sheets("Load").Range("T3").Value
cmd.Parameters.Append pm13
Set pm14 = cmd.CreateParameter("lot", adVarChar, adParamInput, 1000)
pm14.Value = Sheets("Load").Range("U3").Value
cmd.Parameters.Append pm14
Set pm15 = cmd.CreateParameter("qty", adVarChar, adParamInput, 1000)
pm15.Value = Sheets("Load").Range("V3").Value
cmd.Parameters.Append pm15
Set pm16 = cmd.CreateParameter("expiration_date", adVarChar, adParamInput, 1000)
pm16.Value = Sheets("Load").Range("W3").Value
cmd.Parameters.Append pm16
Set pm17 = cmd.CreateParameter("ext_list", adVarChar, adParamInput, 1000)
pm17.Value = Sheets("Load").Range("X3").Value
cmd.Parameters.Append pm17
Set pm18 = cmd.CreateParameter("possible_financial_impact", adVarChar, adParamInput, 1000)
pm18.Value = Sheets("Load").Range("Y3").Value
cmd.Parameters.Append pm18
Set pm19 = cmd.CreateParameter("brand_code", adVarChar, adParamInput, 1000)
pm19.Value = Sheets("Load").Range("Z3").Value
cmd.Parameters.Append pm19
Set pm20 = cmd.CreateParameter("due_date", adVarChar, adParamInput, 1000)
pm20.Value = Sheets("Load").Range("AA3").Value
cmd.Parameters.Append pm20
Set pm21 = cmd.CreateParameter("scope", adVarChar, adParamInput, 1000)
pm21.Value = Sheets("Load").Range("AB3").Value
cmd.Parameters.Append pm21
Set pm22 = cmd.CreateParameter("charge", adVarChar, adParamInput, 1000)
pm22.Value = Sheets("Load").Range("AC3").Value
cmd.Parameters.Append pm22
Set pm23 = cmd.CreateParameter("eligible_item", adVarChar, adParamInput, 1000)
pm23.Value = Sheets("Load").Range("AD3").Value
cmd.Parameters.Append pm23
Set pm24 = cmd.CreateParameter("prod_release_dom_cde", adVarChar, adParamInput, 1000)
pm24.Value = Sheets("Load").Range("AE3").Value
cmd.Parameters.Append pm24
Set pm25 = cmd.CreateParameter("location_type", adVarChar, adParamInput, 1000)
pm25.Value = Sheets("Load").Range("AF3").Value
cmd.Parameters.Append pm25
Set pm26 = cmd.CreateParameter("container_type", adVarChar, adParamInput, 1000)
pm26.Value = Sheets("Load").Range("AG3").Value
cmd.Parameters.Append pm26
Set pm27 = cmd.CreateParameter("stock_type", adVarChar, adParamInput, 1000)
pm27.Value = Sheets("Load").Range("AH3").Value
cmd.Parameters.Append pm27
'Execute
cmd.Execute
conn.Close
End Sub
解决方案
在不重写所有代码的情况下,我可以告诉你一些让你走上正轨的技巧。
你的构造是错误的。它适用于一行,是的,当然,但它不可扩展。正确的构造如下所示:
Declare/Open Connection
Declare Command Object
Declare All Parameters
Loop rows
Assign parameter values
Execute command object
Close Connection
此外,参数将成为命令集合的一部分,因此您甚至不需要按名称访问它们;您可以将这些值作为命令集合 ( cmd.Parameters(1).Value =
) 的一部分来访问。
另一个提示——有很多行?使用事务并在最后执行一次提交。
这让我想到了另一点。按行、列而不是范围名称访问行值。这样,您可以简单地遍历列并通过行和列 id 分配值(您已经在循环行)。
最后,@Belayer 也有麦克风掉落的时刻......避免所有这些痛苦和使用copy
。它不仅更容易,而且在数据库端比逐行插入要快得多,效率更高。我从来没有在 VBA 中做过,但如果你使用的是 .NET,它是 PostgreSQL 精湛的 Npgsql 驱动程序的本机。
推荐阅读
- php - Symfony 4以表单类型获取当前用户?
- digital-ocean - rclone 将文件夹移动到 digitalocean 空间中的文件夹
- grep - SH grep 仅输出某些字符串
- javascript - 在 2 个不同的组件 Angular 之间显示和隐藏
- api - Basecamp 3 API:如何获取授权用户创建的评论?
- mysql - 根据与前一天数据的差异获得前 5 个结果
- javascript - 当按钮类发生变化时,jQuery调用不同的函数
- javascript - 通过 expo 构建 apk 未安装在 android 上
- android-studio - Android Studio:“设备当前处于未知状态”
- facebook - 如何使用 Facebook API 在 1 个请求中获取多个头像