excel导入到已存在的DBF里, DBF里的所有字段都不允许为空,如下DBF_test.DBF 字段属性
DBF_Field1 Char(200) not NULL
DBF_Field2 Char(200) not NULL
DBF_Field3 Char(200) not NULLXLS_test.xls 字段属性
xls_Field1 文本
xls_Field2 文本代码如下
with DM.ADOtmpQry do
begin
Close;
sql.Clear; tmpStr := 'Insert into [DBF_test] (DBF_Field1, DBF_Field2)'+
'select xls_Field1, xls_Field2 '+
'from [excel 8.0;database = XLS_test.xls].[DataTable$]'; sql.Add(tmpStr);
ExecSQL;
open; end;
错误提示: DBF_Field3 不接受空值 DBF数据库不能修改,有什么办法解决吗?
或者用下面语句试试
tmpStr := 'Insert into [DBF_test] (DBF_Field1, DBF_Field2, DBF_Field3)'+
'select xls_Field1, xls_Field2, "-" '+
'from [excel 8.0;database = XLS_test.xls].[DataTable$]';
'select xls_Field1, xls_Field2 '+
'from [excel 8.0;database = XLS_test.xls].[DataTable$]';假設上述語句正確,你只要改成如下就可:
tmpStr := 'Insert into [DBF_Test] select xls_Field1,xls_Field2,''ABC'' from [excel 8.0;database=XLS_Test.xls].[DataTable$]';
EXECL中沒有的空值你用一個字符串常數(如'ABC')加上就可。
上面那几个字段只是举个列子,实际表中有五十多个字段
Excel表有标准的,只能是那几个字段,所以不能修改,
你那个sql语句可能数据类型不匹配
with DM.ADOtmpQry do
begin
Close;
sql.Clear; tmpStr := 'Insert into [DBF_test] (DBF_Field1, DBF_Field2)'+
'select ISNULL(xls_Field1,''), ISNULL(xls_Field2,'') '+
'from [excel 8.0;database = XLS_test.xls].[DataTable$]'; sql.Add(tmpStr);
ExecSQL;
open; end;
begin
Close;
sql.Clear; tmpStr := 'Insert into [DBF_test] (DBF_Field1, DBF_Field2)'+
'select ISNULL(xls_Field1,''''), ISNULL(xls_Field2,''''),'''','''' '+
'from [excel 8.0;database = XLS_test.xls].[DataTable$]'; sql.Add(tmpStr);
ExecSQL;
open; end;