代码之家 › 专栏 › 技术社区 › Balagurunathan Marimuthu Mandeep Singh

如何在VB6中基于匹配值合并两个CSV文件

vb6 csv

Balagurunathan Marimuthu Mandeep Singh · 技术社区 · 8 年前

我有两个csv文件与日期列,开放,高,低和关闭。两个csv文件的日期列可能从不同的日期开始,并且任何csv都可能没有另一个csv的日期值。

在这里,我想将这两个csv文件组合成一个csv,并具有两个csv的匹配日期值和关闭值。任何csv都可能没有另一个csv的日期值,那么该特定日期缺少的值应分配为0。请参阅下图。

来源1

来源2

1 回复 | 直到 8 年前

Bob77 8 年前

看见 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

注意,我已经做了两次,一次输出 c4steps.txt c.txt 从我的 a.txt 和 b.txt 示例输入文件。A和B是输入文件的别名,不过您可能也可以拼写出实际的文件名。

列名F1、F2、A\u F1、B\u F2等是由Jet文本IISAM生成的默认名称。再努力一点,一个正确形成的 schema.ini

浏览生成的可能有助于了解发生了什么。

a、 txt文件

1901,1,1,1
1902,2,2,2
1904,4,4,4
1906,6,6,6
1908,8,8,8

1901,11,11,11
1902,12,12,12
1903,13,13,13
1904,14,14,14
1905,15,15,15
1906,16,16,16

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