如何根据VB6中的匹配值合并两个CSV文件
我有两个csv文件,包含日期,打开,高,低和关闭列。两个csv文件的日期列可以从不同的日期开始,并且任何一个csv可能没有另一个csv的日期值。如何根据VB6中的匹配值合并两个CSV文件
在这里,我想将这两个csv文件合并为单个csv,并且两个csv的日期值和关闭值都匹配。并且任何csv可能没有其他csv的日期值,那么该特定日期的那些缺失值应该被指定为0.请参阅下图。
参见Implementing the Equivalent of a FULL OUTER JOIN in Microsoft Access。
这里是一个简短的演示:
Option Explicit
'Implementing the Equivalent of a FULL OUTER JOIN in Microsoft Jet SQL.
Private Sub Main()
'We'll do our work in App.Path, where our input files are:
ChDir App.Path
ChDrive App.Path
'Clean up from any prior test run:
On Error Resume Next
Kill "inner.txt"
Kill "left.txt"
Kill "right.txt"
Kill "c4steps.txt"
Kill "c.txt"
Kill "schema.ini"
On Error GoTo 0
With New ADODB.Connection
.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source='.';" _
& "Extended Properties='Text;Hdr=No'"
'Do it in 4 steps for illustration:
.Execute "SELECT [A].*, [B].[F2], [B].[F3], [B].[F4] " _
& "INTO [inner.txt] FROM " _
& "[a.txt] [A] INNER JOIN [b.txt] [B] ON " _
& "[A].[F1] = [B].[F1]", _
, _
adCmdText Or adExecuteNoRecords
.Execute "SELECT [A].*, 0 AS [B_F2], 0 AS [B_F3], 0 AS [B_F4] " _
& "INTO [left.txt] FROM " _
& "[a.txt] [A] LEFT JOIN [b.txt] [B] ON " _
& "[A].[F1] = [B].[F1] " _
& "WHERE [B].[F1] IS NULL", _
, _
adCmdText Or adExecuteNoRecords
.Execute "SELECT [B].[F1], 0 AS [A_F2], 0 AS [A_F3], 0 AS [A_F4], " _
& "[B].[F2], [B].[F3], [B].[F4] " _
& "INTO [right.txt] FROM " _
& "[a.txt] [A] RIGHT JOIN [b.txt] [B] ON " _
& "[A].[F1] = [B].[F1] " _
& "WHERE [A].[F1] IS NULL", _
, _
adCmdText Or adExecuteNoRecords
.Execute "SELECT * " _
& "INTO [c4steps.txt] FROM (" _
& "SELECT * FROM [inner.txt] UNION ALL " _
& "SELECT * FROM [left.txt] UNION ALL " _
& "SELECT * FROM [right.txt]) " _
& "ORDER BY [F1]", _
, _
adCmdText Or adExecuteNoRecords
'Do it all in one go:
.Execute "SELECT * " _
& "INTO [c.txt] FROM (" _
& "SELECT [A].*, [B].[F2], [B].[F3], [B].[F4] " _
& "FROM [a.txt] [A] INNER JOIN [b.txt] [B] ON " _
& "[A].[F1] = [B].[F1] UNION ALL " _
& "SELECT [A].*, 0 AS [B_F2], 0 AS [B_F3], 0 AS [B_F4] " _
& "FROM [a.txt] [A] LEFT JOIN [b.txt] [B] ON " _
& "[A].[F1] = [B].[F1] " _
& "WHERE [B].[F1] IS NULL UNION ALL " _
& "SELECT [B].[F1], 0 AS [A_F2], 0 AS [A_F3], 0 AS [A_F4], " _
& "[B].[F2], [B].[F3], [B].[F4] " _
& "FROM [a.txt] [A] RIGHT JOIN [b.txt] [B] ON " _
& "[A].[F1] = [B].[F1] " _
& "WHERE [A].[F1] IS NULL) " _
& "ORDER BY [F1]", _
, _
adCmdText Or adExecuteNoRecords
.Close
End With
MsgBox "Done"
End Sub
请注意,我已经这样做了两次,一次输出从我a.txt
和b.txt
样本输入文件c4steps.txt
然后c.txt
。 A和B是输入文件的别名,尽管您也可以拼出实际的文件名。
列名称F1,F2,A_F1,B_F2等是由Jet Text IISAM生成的默认名称。在运行之前,可以使用一个合适的schema.ini
多一点努力,可以使用更多“有意义”的列名称。
浏览生成的schema.ini
可能有助于了解正在发生的事情。
a.txt
1901,1,1,1
1902,2,2,2
1904,4,4,4
1906,6,6,6
1908,8,8,8
b.txt
1901,11,11,11
1902,12,12,12
1903,13,13,13
1904,14,14,14
1905,15,15,15
1906,16,16,16
c.txt
1901,1,1,1,11,11,11
1902,2,2,2,12,12,12
1903,0,0,0,13,13,13
1904,4,4,4,14,14,14
1905,0,0,0,15,15,15
1906,6,6,6,16,16,16
1908,8,8,8,0,0,0
谢谢@ Bob77。这是否需要安装Microsoft Excel,同时需要安装Access数据库引擎。对? –
它不使用Excel,并且Jet 4.0引擎是Windows的一部分,因此Access也不是必需的。 – Bob77
哦..感谢您的快速响应。我会让你知道后来有一次我通过你实现 –
这是一个家庭作业?你有什么问题?听起来像对我来说非常简单直接的事情。 –
我想在不使用excel应用程序对象的情况下实现。我可以使用excel应用程序对象来完成它。 –