CODEWITHSUNDEEP
csvvb6
Balagurunathan Marimuthu
Balagurunathan Marimuthu

Reputation: 2978

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

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 Source1

Source 2 Source2

Expected Output Expected Output

Upvotes: 3

Views: 238

Answers (1)

Bob77
Bob77

Reputation: 13267

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

Upvotes: 4

Related Questions