gpt4 book ai didi

csv - 如何在 VB6 中根据匹配值合并两个 CSV 文件

转载 作者:行者123 更新时间:2023-12-04 16:10:46 25 4
gpt4 key购买 nike

我有两个 csv 文件,其中包含日期、开盘价、最高价、最低价和收盘价列。两个 csv 文件的日期列可能从不同的日期开始,并且任何 csv 可能没有另一个 csv 的日期值。

在这里,我想将这两个 csv 文件组合成单个 csv,并匹配两个 csv 的日期值和收盘值。并且任何一个 csv 可能没有另一个 csv 的日期值,那么该特定日期的缺失值应指定为 0。请参阅下图。

来源 1 Source1

来源 2 Source2

预期输出 Expected Output

最佳答案

参见 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 输出 c4steps.txt ,然后是 c.txt >b.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

关于csv - 如何在 VB6 中根据匹配值合并两个 CSV 文件,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/46320924/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com