powerbat

文件那么大，不知道ADO能否胜任。结果保存在result.csv。

@set @n=0//&cscript.exe -e:jscript "%~f0"&pause&exit/b

file1 = "file1.xls";
file2 = "file2.xls";

strConnect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source="' + file1
    + '";Extended Properties="Excel 8.0;IMEX=1;HDR=yes;";';
AdoCnn = new ActiveXObject("ADODB.Connection");
AdoCnn.ConnectionString = strConnect;
AdoCnn.Open();
AdoRs = new ActiveXObject("ADODB.Recordset");
AdoRs.Open("SELECT a.* from ["+file1+"].[Sheet1$] as a inner join ["+file2
    +"].[Sheet1$] as b  ON (a.[ZINC_ID]=b.[ZINC_ID])", AdoCnn, 3);
s = AdoRs.GetString(2, AdoRs.RecordCount, ",", "\r\n");
AdoRs.Close();
AdoCnn.Close();

fso = new ActiveXObject("Scripting.FileSystemObject");
fso.OpenTextFile('result.csv', 2, true).write(s);
复制代码

powerbat

楼主都会玩数据库了，怎么还连xls文件与文本文件都分不清楚？
file1.xls只是文本文件；file2.xls才是正宗的xls文件，而且列名称不是ZINC_ID而是Molecule。

@set @n=0//&cscript.exe -e:jscript "%~f0"&pause&exit/b

f1 = "file1.xls";
f2 = "file2.xls";

fso = new ActiveXObject("Scripting.FileSystemObject");
csv = fso.GetBaseName(f1) + ".csv";
fso.MoveFile(f1, csv);
ts = fso.OpenTextFile("Schema.ini", 2, true);
ts.WriteLine("["+csv+"]\r\nFormat=TabDelimited\r\n"); ts.Close();

strConnect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source="' + '.'
    + '";Extended Properties="text;FMT=Delimited;HDR=yes;";';
strQuery = "SELECT a.* from ["+csv+"] as a "
    + " inner join (select Molecule from [查询1$]"
    + " in '' [Excel 8.0;IMEX=1;HDR=yes;DATABASE="+f2+";]) as b "
    + " ON (a.[ZINC_ID]=b.[Molecule])";
AdoCnn = new ActiveXObject("ADODB.Connection");
AdoCnn.Open(strConnect);
AdoRs = AdoCnn.Execute(strQuery);
s = AdoRs.GetString(2, -1, ",", "\r\n");
AdoCnn.Close();

fso.OpenTextFile('result.csv', 2, true).write(s);
fso.MoveFile(csv, f1);
fso.DeleteFile("Schema.ini");
复制代码

powerbat

原来楼主在这个问题上纠结了这么久，呵呵。
http://www.bathome.net/viewthread.php?tid=15247
http://www.bathome.net/viewthread.php?tid=14847
http://www.bathome.net/viewthread.php?tid=14986
可是你的求助态度有点让人不敢恭维，不与答复者回馈，没有一点认真的态度，有点像拿到答案就跑的人。

powerbat

把cmd窗口中的出错信息贴出来。

附：
如何在CMD中使用复制粘贴功能
http://www.bathome.net/thread-9963-1-1.html

powerbat

回复 12# sweet惜缘

我的win7上测试OK。
不行换这个试试

@set @n=0//&cscript.exe -e:jscript "%~f0"&pause&exit/b

f1 = "file1.xls";
f2 = "file2.xls";

fso = new ActiveXObject("Scripting.FileSystemObject");
csv = fso.GetBaseName(f1) + ".csv";
fso.MoveFile(f1, csv);
ts = fso.OpenTextFile("Schema.ini", 2, true);
ts.WriteLine("["+csv+"]\r\nFormat=TabDelimited\r\n"); ts.Close();

strConnect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source="' + '.'
    + '";Extended Properties="text;FMT=Delimited;HDR=yes;";';
strQuery = "SELECT a.* from ["+csv+"] as a "
    + " inner join (select Molecule from [查询1$]"
    + " in '' [Excel 8.0;IMEX=1;HDR=yes;DATABASE="+f2+";]) as b "
    + " ON (a.[ZINC_ID]=b.[Molecule])";
AdoCnn = new ActiveXObject("ADODB.Connection");
AdoCnn.Open(strConnect);
AdoRs = new ActiveXObject("ADODB.Recordset");
AdoRs.Open(strQuery, AdoCnn, 3);
s = AdoRs.GetString(2, -1, ",", "\r\n");
AdoRs.Close();
AdoCnn.Close();

fso.OpenTextFile('result.csv', 2, true).write(s);
fso.MoveFile(csv, f1);
fso.DeleteFile("Schema.ini");
复制代码

powerbat

将s = AdoRs.GetString(2, -1, ",", "\r\n");
改为
s = "";
if (!AdoRs.EOF) s = AdoRs.GetString(2, -1, ",", "\r\n");

可能是没有符合要求的行，结果为空。（反正你提供的样本中没有。）