英文:
How to take multiple inputs in SQL from an excel sheet
问题
我正在尝试找到一种方法，可以从Excel表中获取多个客户名称，然后在WHERE子句中使用这些名称，以便我可以跟踪这些客户的订单！例如：
Customer_Name | Quantity | Total purchase price
ABC | 100 | 500
XYZ | 200 | 1000
DEF | 300 | 1500
现在，我正在使用一个Excel文件，从中获取数据并编写SQL查询以筛选我需要的数据（原始数据太大了无法加载）。现在我想从同一工作簿中的不同工作表中的一列中获取多个客户名称，这可能会根据用户输入的客户名称而改变！
SELECT Customer_Name, Quantity, Total Purchase Price
FROM TABLE1
WHERE Customer_Name='ABC'
OR Customer_Name='XYZ'
OR (以此类推);
当我手动输入每个客户时，它可以正常工作，但我希望它能够动态地处理。如果有人输入了1000个客户名称，它可以从查询中获取这些列的输入。我了解一些VBA（曾经在小型项目上工作过），所以如果能够轻松地通过简单的VBA代码实现，我愿意尝试！
谢谢，我是新手，请告诉我如果我需要更好地解释。
最好的问候
英文:
I am trying to find a way to take multiple customer names from an excel sheet to be used in WHERE clause so that I can track the order for those customers!
eg:
Customer_Name | Quantity | Total purchase price
ABC | 100 | 500
XYZ | 200 | 1000
DEF | 300 | 1500
Now, I am using an excel file, from which I am getting data from SQL Server and writing a SQL query to filter out the data I need(original data is very big to load). Now I want to take multiple customer name from a col which is in a different worksheet in the same workbook in excel, which can change depending upon the customers name entered by the user!
SELECT Customer_Name, Quantity, Total Purchase Price
FROM TABLE1
WHERE Customer_Name='ABC'
OR Customer_Name='XYZ'
OR (and so on);
It works for each Customer when I put it manually but I want it to be dynamic. If someone puts 1000 customer names for example, it takes the input from those columns in the query. I know a bit of VBA(have worked on small projects), so if I can do it easily from a simple VBA code, I am all ears!
Thank you, I am new, please let me know if I can explain it better.
Best regards
答案1
得分: 2
我建议构建一个以逗号分隔的字符串，其中包含客户名称，并使用<code>WHERE IN ()</code>。以下代码假定客户名称位于列A中。此外，假定有一个列标题。从<code>i = 1</code>开始<code>For</code>循环，以包括第一行。
Sub GetCustomerNames()
Dim lastRow As Long
Dim i As Long
Dim s As String
Dim sSql As String
lastRow = Range("A1").End(xlDown).Row
For i = 2 To lastRow
s = s & "'" & Cells(i, "A") & "',"
Next i
If Len(s) > 0 Then s = Left$(s, Len(s) - 1) 'remove last '
sSql = "SELECT Customer_Name, Quantity, Total_Purchase_Price FROM TABLE1 WHERE Customer_Name IN (" & s & ")"
Debug.Print sSql
End Sub
输出：
SELECT Customer_Name, Quantity, Total_Purchase_Price FROM TABLE1 WHERE Customer_Name IN ('ABC','XYZ','DEF')
英文:
I recommend building a comma separated string with the customer names and using a <code>WHERE IN ()</code>.
The following code assumes the customer names are in column A. Furthermore a column header is assumed. Start the <code>For</code> loop at <code>i = 1</code> to include the first row.
Sub GetCustomerNames()
Dim lastRow As Long
Dim i As Long
Dim s As String
Dim sSql As String
lastRow = Range("A1").End(xlDown).Row
For i = 2 To lastRow
s = s & "'" & Cells(i, "A") & "',"
Next i
If Len(s) > 0 Then s = Left$(s, Len(s) - 1) 'remove last '
sSql = "SELECT Customer_Name, Quantity, Total_Purchase_Price FROM TABLE1 WHERE Customer_Name IN (" & s & ")"
Debug.Print sSql
End Sub
Output:
SELECT Customer_Name, Quantity, Total_Purchase_Price FROM TABLE1 WHERE Customer_Name IN ('ABC','XYZ','DEF')
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论