是否可以在 CSV 文件和工作表之间创建 VBA QueryTable 外连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4972019/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to create a VBA QueryTable outer join between a CSV file and a worksheet?
提问by Matt
I'm creating an excel workbook to manage my personal finances. My banks provide transaction data in CSV format and I found a way to import that data into excel using a QueryTable (using a "TEXT" connection.)
我正在创建一个 Excel 工作簿来管理我的个人财务。我的银行以 CSV 格式提供交易数据，我找到了一种使用 QueryTable（使用“TEXT”连接）将该数据导入 excel 的方法。
I'd like to automatically apply transaction category rules to each imported transaction. I have a worksheet with two columns - a string to match against the transaction "details" provided in my bank's CSV file and the category to apply to the matching transactions.
我想自动将交易类别规则应用于每个导入的交易。我有一个包含两列的工作表 - 一个字符串以匹配我银行的 CSV 文件中提供的交易“详细信息”以及适用于匹配交易的类别。
Is it possible to create an outer join between the CSV data and the categories worksheet and dump the resulting table into another worksheet?
是否可以在 CSV 数据和类别工作表之间创建外部联接并将结果表转储到另一个工作表中？
For example (SQL pseudocodeish): SELECT csv.date, csv.details, csv.debit, csv.credit, ws.category FROM [csvfile] csv LEFT OUTER JOIN [worksheet] ws ON csv.details ~= ws.details
例如（SQL 伪代码）：SELECT csv.date, csv.details, csv.debit, csv.credit, ws.category FROM [csvfile] csv LEFT OUTER JOIN [worksheet] ws ON csv.details ~= ws.details
~= above would be some kind of string match. I can figure out the SQL, my question is really how to combine the CSV file and worksheet in the same QueryTable.
~= 上面将是某种字符串匹配。我可以找出 SQL，我的问题实际上是如何在同一个 QueryTable 中组合 CSV 文件和工作表。
回答by Fionnuala
Excel will open CSV files without blinking, but you can use a connection string, if you prefer. It is even possible to write a query that compares an existing worksheet or named range with a text file using an Excel connection. All you need is a little VBA.
Excel 将在不闪烁的情况下打开 CSV 文件，但您可以根据需要使用连接字符串。甚至可以编写一个查询，将现有工作表或命名区域与使用 Excel 连接的文本文件进行比较。您只需要一点 VBA。
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
'Note HDR=Yes, that is, first row contains field names '
'and FMT delimted, ie CSV '
strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Docs\;" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
cn.open strcon
'You would not need delimiters ('') if last field is numeric: '
strSQL="SELECT FieldName1, FieldName2 FROM The.csv " _
& " WHERE LastFieldName='SomeTextValue'"
rs.Open strSQL, cn
Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs
You can use any suitable Jet SQL queries against the connection, just be careful about case sensitivity. For example, working with a connection to the current workbook:
您可以对连接使用任何合适的 Jet SQL 查询，只是要注意区分大小写。例如，使用与当前工作簿的连接：
Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer
''This is not the best way to refer to the workbook
''you want, but it is very convenient for notes
''It is probably best to use the name of the workbook.
strFile = ActiveWorkbook.FullName
''Note that if HDR=No, F1,F2 etc are used for column names,
''if HDR=Yes, the names in the first row of the range
''can be used.
''This is the Jet 4 connection string, you can get more
''here : http://www.connectionstrings.com/excel
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
''Late binding, so no reference is needed
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
strSQL = "SELECT * " _
& "FROM [Sheet1$] a " _
& "LEFT JOIN [Text;FMT=Delimited;HDR=Yes;" _
& "DATABASE=C:\Docs].Import.txt b " _
& "ON a.[Id]=b.[Id] "
rs.Open strSQL, cn, 3, 3
''Pick a suitable empty worksheet for the results
Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs
''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
回答by Greg Harward
It is possible to create an OUTER JOIN referencing disparate data sources (csv, Excel, Access, txt, SQL, Oracle, etc) using ISAM Names in an ADO query. The results are held in a recordset that can be published back to Excel or another datasource as desired. Google "SQL ISAM Names" to find my other posts on the topic.
可以在 ADO 查询中使用 ISAM 名称创建引用不同数据源（csv、Excel、Access、txt、SQL、Oracle 等）的 OUTER JOIN。结果保存在记录集中，可以根据需要将其发布回 Excel 或其他数据源。谷歌“SQL ISAM 名称”以找到我关于该主题的其他帖子。
回答by Patrick A
I am sure a little more info would help clear up my confusion but I don't believe it is possible to set up a SQL query against a CSV as Excel will not recognise it as a Data Source.
我相信更多的信息将有助于消除我的困惑，但我不相信可以针对 CSV 设置 SQL 查询，因为 Excel 不会将其识别为数据源。
Have you thought about simply loading the csv into Excel and generating a pivot table/lookups on the data?
您是否想过简单地将 csv 加载到 Excel 中并生成数据透视表/查找？
