导入访问csv，不重复

Question

我有一个以.csv格式提供给我的股票列表。我无法更改csv，需要通过Access 2007-2013进行所有验证。

我创建了一个追加查询，将我的数据从CSV导入到表中。

INSERT INTO Table1 ( tblfield1, tblfield2, tblfield3, tblfield4 )
SELECT [File#csv].[columnname1], [File#csv].[columnname2], [File#csv].[columnname3], [File#csv].[columnname4]
FROM [File#csv] IN '' [Text;FMT=Delimited;HDR=YES;CharacterSet=437
;DATABASE=c:yourfilepathhere];

我需要它做的是导入而不重复，似乎一个解决方案是将数据导入到临时表，然后从那里复制而不重复。

INSERT INTO destTable
SELECT Field1,Field2,Field3,... 
FROM srcTable
WHERE NOT EXISTS(SELECT * 
FROM destTable 
WHERE (srcTable.Field1=destTable.Field1 and
SrcTable.Field2=DestTable.Field2...etc.)
)

(SQL取自：将行从一个表复制到另一个表，忽略重复项)

然而，这似乎是一个糟糕的解决方案。有没有一种方法可以在查询中使用左联接来同时完成这两项任务？

编辑：我已经取得了一些进步。

INSERT INTO tblDestination ( destfield1, destfield2, destfield3, destfield4 )
SELECT filename1.[field1], filename1.[(field2], filename1.[field3], filename1.[field4]
FROM [Text;FMT=Delimited;HDR=YES;CharacterSet=437)
;DATABASE=D:yourfilepath].filename.csv AS filename1 LEFT JOIN tblDestination ON filename1.[fieldX] = tblDestination.destfieldX
WHERE (((filename1.[FieldX])= Is Null));

编辑：解决方案是

INSERT INTO tblDestination ( destfield1, destfield2, destfield3, destfield4 )
SELECT filename1.[field1], filename1.[(field2], filename1.[field3], filename1.[field4]
FROM [Text;FMT=Delimited;HDR=YES;CharacterSet=437)
;DATABASE=D:yourfilepath].filename.csv AS filename1 LEFT JOIN tblDestination ON filename1.[fieldX] = tblDestination.destfieldX
WHERE (((tblDestination.Field1) Is Null) AND ((tblDestination.Field2) Is Null));

Answer 1

"不过，我需要的是一个AND语句，只在所有字段相等的情况下排除该行">

你似乎已经有了正确的想法？

我在WHERE子句中使用OR，因为单个不匹配意味着它是不同的记录

INSERT INTO tblDestination 
(destfield1, destfield2, destfield3, destfield4 )
SELECT filename1.[field1], filename1.[(field2], filename1.[field3], filename1.[field4]
FROM [Text;FMT=Delimited;HDR=YES;CharacterSet=437;
DATABASE=D:yourfilepath].filename.csv] AS filename1 
LEFT JOIN tblDestination 
ON  filename1.[field1] = tblDestination.destfield1
AND filename1.[field2] = tblDestination.destfield2
AND filename1.[field3] = tblDestination.destfield3
AND filename1.[field4] = tblDestination.destfield4
WHERE (
filename1.[Field1] IS NULL OR
filename1.[Field2] IS NULL OR 
filename1.[Field3] IS NULL OR 
filename1.[Field4] IS NULL
);