G
gymmy
Unregistered / Unconfirmed
GUEST, unregistred user!
这两天碰到一个非常非常奇怪的问题,我有一个2000多条数据的excel表,用sql server的
DTS导入库后发现数据顺序乱掉了,但导入Access后确是好的,然后再从Access导入到sql server
后顺序也不对,这到底是怎么回事?难道DTS有bug? 无奈之下,我只能用程序的方法导入了
语句如下:
try
iRow:=BeginRow;
iCol:=BeginCol;
while trim(Excel.Worksheets[1].cells[iRow,iCol].value)<>'' do
begin
with ADOQuery5 do
begin
ADOQuery5.edit;
Append;
Fields[0].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol].value);
Fields[1].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+1].value);
Fields[2].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+2].value);
Fields[3].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+3].value);
Fields[4].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+4].value);
Fields[5].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+5].value);
Fields[6].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+6].value);
Fields[7].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+7].value);
Fields[8].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+8].value);
Fields[9].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+9].value);
Fields[10].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+10].value);
Fields[11].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+11].value);
Fields[12].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+12].value);
Fields[13].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+13].value);
Fields[14].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+14].value);
Fields[15].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+15].value);
Fields[16].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+16].value);
Fields[17].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+17].value);
Fields[18].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+18].value);
Fields[19].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+19].value);
Fields[20].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+20].value);
Fields[21].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+21].value);
Fields[22].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+23].value);
Fields[23].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+24].value);
Fields[24].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+25].value);
Fields[25].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+25].value);
Fields[26].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+26].value);
Fields[27].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+27].value);
Fields[28].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+28].value);
Fields[29].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+29].value);
Fields[30].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+30].value);
Fields[31].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+31].value);
Fields[32].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+32].value);
Fields[33].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+33].value);
Fields[34].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+34].value);
Fields[35].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+35].value);
Fields[36].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+36].value);
Fields[37].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+37].value);
Fields[38].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+38].value);
ADOQuery5.Post;
end;
iRow:=iRow+1;
iCol:=BeginCol;
end;
Excel.Quit;
ADOConnection1.CommitTrans;
except
Application.MessageBox('导入数据出错!请检查文件的格式是否正确!','提示信息', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);
Excel.Quit;
ADOConnection1.RollbackTrans;
raise;
end;
ADOQuery5.Open;
MessageDlg('Finish Importing!',mtInformation,[mbOK],0);
end;
也就是一条一条记录的加,发现还是有问题,当加100条记录的时候,还正常,但如果一起加
的话,就出现死机,我的内存是128M,听人说加完一条记录后要释放内存,我这样ADOQuery5.Post;
算是释放了吗?请各位指点。我是在win98下sqlserver7+D6+ADO的,另外我在一个正版的
sql server2000里用DTS试过也不行,我困惑!! 还有什么好方法用ADO实现大批量的数据
导入库的方法??
DTS导入库后发现数据顺序乱掉了,但导入Access后确是好的,然后再从Access导入到sql server
后顺序也不对,这到底是怎么回事?难道DTS有bug? 无奈之下,我只能用程序的方法导入了
语句如下:
try
iRow:=BeginRow;
iCol:=BeginCol;
while trim(Excel.Worksheets[1].cells[iRow,iCol].value)<>'' do
begin
with ADOQuery5 do
begin
ADOQuery5.edit;
Append;
Fields[0].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol].value);
Fields[1].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+1].value);
Fields[2].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+2].value);
Fields[3].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+3].value);
Fields[4].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+4].value);
Fields[5].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+5].value);
Fields[6].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+6].value);
Fields[7].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+7].value);
Fields[8].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+8].value);
Fields[9].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+9].value);
Fields[10].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+10].value);
Fields[11].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+11].value);
Fields[12].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+12].value);
Fields[13].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+13].value);
Fields[14].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+14].value);
Fields[15].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+15].value);
Fields[16].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+16].value);
Fields[17].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+17].value);
Fields[18].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+18].value);
Fields[19].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+19].value);
Fields[20].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+20].value);
Fields[21].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+21].value);
Fields[22].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+23].value);
Fields[23].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+24].value);
Fields[24].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+25].value);
Fields[25].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+25].value);
Fields[26].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+26].value);
Fields[27].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+27].value);
Fields[28].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+28].value);
Fields[29].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+29].value);
Fields[30].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+30].value);
Fields[31].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+31].value);
Fields[32].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+32].value);
Fields[33].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+33].value);
Fields[34].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+34].value);
Fields[35].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+35].value);
Fields[36].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+36].value);
Fields[37].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+37].value);
Fields[38].AsString:=trim(Excel.WorkSheets[1].Cells[iRow,iCol+38].value);
ADOQuery5.Post;
end;
iRow:=iRow+1;
iCol:=BeginCol;
end;
Excel.Quit;
ADOConnection1.CommitTrans;
except
Application.MessageBox('导入数据出错!请检查文件的格式是否正确!','提示信息', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);
Excel.Quit;
ADOConnection1.RollbackTrans;
raise;
end;
ADOQuery5.Open;
MessageDlg('Finish Importing!',mtInformation,[mbOK],0);
end;
也就是一条一条记录的加,发现还是有问题,当加100条记录的时候,还正常,但如果一起加
的话,就出现死机,我的内存是128M,听人说加完一条记录后要释放内存,我这样ADOQuery5.Post;
算是释放了吗?请各位指点。我是在win98下sqlserver7+D6+ADO的,另外我在一个正版的
sql server2000里用DTS试过也不行,我困惑!! 还有什么好方法用ADO实现大批量的数据
导入库的方法??