How to Merge two CSV file based on matching value in VB6

Question

I have two csv file with columns of Date, Open, High, Low and Close. Date column of both csv file may starts from different dates and any of the csv may not have the date value of another csv.

Here, I would like to combine these two csv file into single csv with matching date value and close value of both csv. And any of the csv may not have the date value of another csv, then those missing value for that particular date should be assigned as 0. Refer below image.

Source 1

Source 2

Expected Output

Is this a homework assignment? What part exactly are you having trouble with? Sounds like a very simple straighforward thing to me. — Bill Hileman, Sep 20 '17 at 17:10
I want to be implemented without using excel application object. I can able to do it using excel application object. — Balagurunathan Marimuthu, Sep 20 '17 at 17:14

score 4 · Accepted Answer · answered Sep 20 '17 at 18:26

See Implementing the Equivalent of a FULL OUTER JOIN in Microsoft Access.

Here is a brief demo:

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

Note that I have done this twice, once outputting c4steps.txt and then c.txt from my a.txt and b.txt sample input files. A and B are aliases for the input files, though you could probably spell out the actual file names too.

The column names F1, F2, A_F1, B_F2, etc. are default names generated by the Jet Text IISAM. With a little more effort an a properly formed schema.ini before the run more "meaningful" column names could have been used.

Browsing the generated schema.ini might help understand what is going on.

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

Thanks @Bob77. Is this no need of install Microsoft Excel and same time it needs to be install Access database engine. Right? — Balagurunathan Marimuthu, Sep 21 '17 at 02:15
It does not use Excel, and the Jet 4.0 engine is part of Windows so Access is not required either. — Bob77, Sep 21 '17 at 02:59
Oh.. Thanks for your quick response. I will let you know later once I get in through your implementation — Balagurunathan Marimuthu, Sep 21 '17 at 03:01
I have an error when union all.. since, my table close values may contains decimal places. — Balagurunathan Marimuthu, Sep 21 '17 at 06:54
Thanks!!! Your code works perfect with little modifications. Cheers! Happy coding! — Balagurunathan Marimuthu, Sep 21 '17 at 13:53

How to Merge two CSV file based on matching value in VB6

1 Answers1