Excel比较两个csv文件并显示差异 [英] Excel comparing two csv files and showing the difference
问题描述
我希望比较两组 csv 文件和/或一个 csv 文件和一个 .txt 文件.为了简单起见，我认为" .txt 文件可能需要转换为 csv 文件，但这可能需要也可能不需要.我要么想使用 excel、c++ 或 python.我需要将一个已接受"值列表与一个已测量的列表进行比较，并找出它们之间的差异(如果有的话).Excel 可能是最简单的方法，但 python 或 c++ 也可以.这不是家庭作业，所以不要担心那种事情.非常感谢代码建议和/或模板.或网站链接
I'm looking to compare two big sets of csv files and/or a csv file and a .txt file. I "think" the .txt file may need to be converted to a csv file just for simplicity sake but that may or may not be needed. I either want to use excel, c++, or python. I need to compare one "accepted" value list to a list that is measured and find the difference between them if there is one. Excel may be the easiest way to do this but python or c++ may work just as well. This is not homework so don't worry about that sort of thing. Code advice and/or templates is greatly appreciated. or links to websites
编辑 1
我读过 Python 的 difflib 或不同的类，但不熟悉如何使用它，可能比我想要的要多.
I've read about Python's difflib or differ class but unfamiliar how to use it and may be more than I want.
编辑 2
文件都有一系列列(它们之间没有画线或任何东西)，在这些命名"列下方会有数字.我需要将文件一中第 1 列中的数字与文件 2 中第 1 列中的数字进行比较，如果有差异，请显示另一个 csv 文件中的差异
The Files both will have a series of columns(not with lines drawn between them or anything) and below those "named" columns there will be numbers. I need to compare the number in column 1 spot one in file one to column 1 spot one of file 2 and if there is a difference show the difference in another csv file
推荐答案
您可以使用 ADO(ODBC/JET/OLEDB 文本驱动程序)将体面"的 .txt/.csv/.tab/.flr 文件视为来自每种支持 COM 的语言的 SQL 数据库.然后可以使用 SQL 的强大功能进行比较(DISTINCT、GROUP、(LEFT) JOINS、...).
You can use ADO (ODBC/JET/OLEDB Text Driver) to treat 'decent' .txt/.csv/.tab/.flr files as tables in a SQL Database from every COM-enabled language. Then the comparisons could be done using the power of SQL (DISTINCT, GROUP, (LEFT) JOINS, ...).
添加了关于您的评论:
这是你的问题，我不想把你推到你不想去的地方.但是如果您需要比较表格数据，SQL 是一个很好的(最好的?)工具.作为发现两个 .txt 文件中差异的脚本输出的证据:
It's your problem and I don't want to push you where you don't want to go. But SQL is a good (the best?) tool, if you need to compare tabular data. As evidence the output of a script that spots the differences in two .txt files:
======= The .txt files to play with
------- file1.txt
"AC";"AM"
40000;-19083,00
40100;20000,00
40200;350004,00
40300;3498,99
------- file2.txt
"AC";"AM"
40000;-19083,00
40300;3498,99
40105;-234567,00
40200;350,00
======= Some diagnostic SQL
------- <NULL> indicates: In F1 but not in F2 (LEFT JOIN)
SELECT T1.AC, T1.AM, T2.AM FROM [file1.txt] AS T1 LEFT JOIN [file2.txt] AS T2 ON (T1.AC =
T2.AC)
------- Result
AC File1 File2
40000 -19083 -19083
40100 20000 <NULL>
40200 350004 350
40300 3498,99 3498,99
------- <NULL> indicates: Not in the other file (LEFT JOIN, UNION)
SELECT T1.AC, T1.AM, T2.AM FROM [file1.txt] AS T1 LEFT JOIN [file2.txt] AS T2 ON (T1.AC =
T2.AC) UNION SELECT T2.AC, T1.AM, T2.AM FROM [file2.txt] AS T2 LEFT JOIN [file1.txt] AS T1
ON (T1.AC = T2.AC)
------- Result
AC File1 File2
40000 -19083 -19083
40100 20000 <NULL>
40105 <NULL> -234567
40200 350004 350
40300 3498,99 3498,99
------- the problems: missing, different values
SELECT T1.AC, T1.AM, T2.AM FROM [file1.txt] AS T1 LEFT JOIN [file2.txt] AS T2 ON (T1.AC =
T2.AC) WHERE T2.AM IS NULL OR T1.AM <> T2.AM UNION SELECT T2.AC, T1.AM, T2.AM FROM [file2.
txt] AS T2 LEFT JOIN [file1.txt] AS T1 ON (T1.AC = T2.AC) WHERE T1.AM IS NULL OR T1.AM <>
T2.AM
------- Result
AC File1 File2
40100 20000 <NULL>
40105 <NULL> -234567
40200 350004 350
进一步补充:
这篇文章涉及ADO和文本文件；查找文件 adoNNN.chm(NNN=版本号，例如 210)在您的计算机上；这是一本关于好书ADO.
This article deals with ADO and text files; look for a file adoNNN.chm (NNN=Version number, e.g. 210) on your computer; this is a good book about ADO.
您可以使用 Access 或 OpenOffice Base 来试验 SQL 语句应用于链接/引用(未导入！)文本数据库.
You can use Access or OpenOffice Base to experiment with SQL statements applied to a linked/referenced (not imported!) text database.
在您掌握了最初的障碍后，脚本/程序将变得很容易:连接到数据库，即到包含文件和 schema.ini 的文件夹文件来定义 files=tables 的结构.
A script/program will be easy after you mastered the initial hurdle: connecting to the the database, i.e. to a folder containing the files and a schema.ini file to define the structure of the files=tables.
上面的输出是由以下生成的:
The output above was generated by:
Const adClipString = 2
Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" )
Dim sDir : sDir = oFS.GetAbsolutePathName( ". xt" )
Dim oDB : Set oDb = CreateObject( "ADODB.Connection" )
oDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDir & ";Extended Properties=""text"""
Dim sSQL
Dim sFiNa
WScript.Echo "=======", "The .txt files to play with"
For Each sFiNa In Array( "file1.txt", "file2.txt" )
WScript.Echo "-------", sFiNa
WScript.Echo oFS.OpenTextFile( "txt" & sFiNa ).ReadAll()
Next
WScript.Echo "=======", "Some diagnostic SQL"
Dim aSQL
For Each aSQL In Array( _
Array( "<NULL> indicates: In F1 but not in F2 (LEFT JOIN)" _
, Join( Array( _
"SELECT T1.AC, T1.AM, T2.AM FROM" _
, "[file1.txt] AS T1" _
, "LEFT JOIN [file2.txt] AS T2 ON (T1.AC = T2.AC)" _
), " " ) ) _
, Array( "<NULL> indicates: Not in the other file (LEFT JOIN, UNION)" _
, Join( Array( _
"SELECT T1.AC, T1.AM, T2.AM FROM" _
, "[file1.txt] AS T1" _
, "LEFT JOIN [file2.txt] AS T2 ON (T1.AC = T2.AC)" _
, "UNION" _
, "SELECT T2.AC, T1.AM, T2.AM FROM" _
, "[file2.txt] AS T2" _
, "LEFT JOIN [file1.txt] AS T1 ON (T1.AC = T2.AC)" _
), " " ) ) _
, Array( "the problems: missing, different value" _
, Join( Array( _
"SELECT T1.AC, T1.AM, T2.AM FROM" _
, "[file1.txt] AS T1" _
, "LEFT JOIN [file2.txt] AS T2 ON (T1.AC = T2.AC)" _
, "WHERE T2.AM IS NULL OR T1.AM <> T2.AM" _
, "UNION" _
, "SELECT T2.AC, T1.AM, T2.AM FROM" _
, "[file2.txt] AS T2" _
, "LEFT JOIN [file1.txt] AS T1 ON (T1.AC = T2.AC)" _
, "WHERE T1.AM IS NULL OR T1.AM <> T2.AM" _
), " " ) ) _
)
sSQL = aSQL( 1 )
WScript.Echo "-------", aSQL( 0 )
WScript.Echo sSQL
Dim oRS : Set oRS = oDB.Execute( sSQL )
WScript.Echo "------- Result"
WScript.Echo Join( Array( "AC", "File1", "File2" ), vbTab )
WScript.Echo oRS.GetString( adClipString, , vbTab, vbCrLf, "<NULL>" )
Next
oDB.Close
如果你删除/忽略胖子(创建 SQL 语句、诊断输出)，它就会沸腾最多 6 行
If you delete/ignore the fat (create SQL statements, diagnostics output), it boils down to 6 lines
Dim oDB : Set oDb = CreateObject( "ADODB.Connection" )
oDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDir & ";Extended Properties=""text"""
sSQL = "..."
Dim oRS : Set oRS = oDB.Execute( sSQL )
WScript.Echo oRS.GetString( adClipString, , vbTab, vbCrLf, "<NULL>" )
oDB.Close
它可以很容易地移植"到每一种支持 COM 的语言，因为 ADO物体完成所有繁重的工作..GetString 方法很方便，当你想要保存结果集:只需旋转分隔符/分隔符/Null 参数并将其转储到文件中
which can be 'ported' easily to every COM-enabled language, because the ADO objects do all the heavy lifting. The .GetString method comes handy, when you want to save a resultset: just twiddle the separator/delimiter/Null arguments and dump it to file
oFS.CreateTextFile( ... ).WriteLine oRS.GetString( _
adClipString, , ",", vbCrLf, ""
)
(不要忘记将该表的定义添加到您的 schema.ini).的当然你也可以使用SELECT/INSERT INTO"，但这样的语句可能不会易于正确/通过 ADO 文本驱动程序的解析器.
(don't forget to add a definition for that table to your schema.ini). Of course you also can use a "SELECT/INSERT INTO", but such statements may not be easy to get right/passed the ADO Text Driver's parser.
加法 wrt 计算:
从一个 5 x 2 主/批准文件开始，其中包含:
Start with a 5 x 2 master/approved file containing:
Num0 Num1 Num2 Num3 Num4
7,6 6,1 3,8 0,9 8,9
0,9 9,4 4,7 8,8 9,9
将其转换为expected.txt
transform it to expected.txt
Num0 Num1 Num2 Num3 Num4 Spot
7,6 6,1 3,8 0,9 8,9 1
0,9 9,4 4,7 8,8 9,9 2
通过附加 Spot 列使其符合
by appending the Spot column so it conforms to
[expected.txt]
ColNameHeader=True
CharacterSet=1252
Format=Delimited(;)
Col1=Num0 Float
Col2=Num1 Float
Col3=Num2 Float
Col4=Num3 Float
Col5=Num4 Float
Col6=Spot Integer
在您的 schema.ini 文件中.同样，转换一个测量文件，如:
in your schema.ini file. Similarly, transform a measure file like:
Num0 Num1 Num2 Num3 Num4
7,1 1,1 3,8 0,9 8,9
0,9 9,4 4,7 8,8 9,9
到measured.txt
to measured.txt
Num0 Num1 Num2 Num3 Num4 Spot
7,1 1,1 3,8 0,9 8,9 1
0,9 9,4 4,7 8,8 9,9 2
申请
sSQL = Join( Array( _
"SELECT E.Num0 - M.Num0 AS Dif0" _
, ", E.Num1 - M.Num1 AS Dif1" _
, ", E.Num2 - M.Num2 AS Dif2" _
, ", E.Num3 - M.Num3 AS Dif3" _
, ", E.Num4 - M.Num4 AS Dif4" _
, ", E.Spot AS Spot" _
, "FROM [expected.txt] AS E" _
, "INNER JOIN [measured.txt] AS M" _
, "ON E.Spot = M.Spot" _
), " " )
将结果集写入differences.txt
Write the resultset to differences.txt
aFNames = Array("Num0", ... "Spot" )oFS.CreateTextFile(sFSpec).Write _加入(aFNames，sFSep)&sRSep&oRS.GetString(adClipString, , sFSep, sRSep, "")
aFNames = Array( "Num0", ... "Spot" ) oFS.CreateTextFile( sFSpec ).Write _ Join( aFNames, sFSep ) & sRSep & oRS.GetString( adClipString, , sFSep, sRSep, "" )
你会得到:
Num0 Num1 Num2 Num3 Num4 Spot
0,5 5 0 0 0 1
0 0 0 0 0 2
这篇关于Excel比较两个csv文件并显示差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
