是否可以在 CSV 文件和工作表之间创建 VBA QueryTable 外连接？

发布于 2024-10-17 04:10:53 字数 476 浏览 7 评论 0原文

我正在创建一个 Excel 工作簿来管理我的个人财务。我的银行提供 CSV 格式的交易数据，我找到了一种使用 QueryTable（使用“TEXT”连接）将该数据导入 Excel 的方法。

我想自动将交易类别规则应用于每个导入的交易。我有一个包含两列的工作表 - 一个与我的银行 CSV 文件中提供的交易“详细信息”相匹配的字符串，以及适用于匹配交易的类别。

是否可以在 CSV 数据和类别工作表之间创建外连接并将结果表转储到另一个工作表中？

例如（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

~=上面是某种字符串匹配。我可以弄清楚 SQL，我的问题实际上是如何将 CSV 文件和工作表合并到同一个查询表中。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小镇女孩 2024-10-24 04:10:53

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

您可以对连接使用任何合适的 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

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.

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:

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

回复收藏 0 原文