当Access中不存在字段时，如何插入空值？

Question

我在Microsoft Access中使用vba代码一次导入多个csv文件，不幸的是，并非所有csv文件都具有相同的格式；有些文件只有SKU-1，但有些文件有第二个字段SKU-2。

对于csv文件中同时具有sku-1和sku-2字段的文件，我的代码如下所示，但是，如果文件只有SKU-1，则导入失败，并显示“参数太少。预期为1”。

如果字段本身不存在，有没有办法使用某种case语句来删除SKU-2的空值？

CurrentDb.Execute "INSERT INTO MumbyCSV (OrderID, Email, FirstName, LastName, Company, Phone, SecondaryPhone," & _
        "Fax, ApproveStatus, OrderDate, [SKU-1], [SKU-2], Total, ShiptoName, ShipToAddress1, ShipToAddress2," & _
        "ShipToCity, ShipToState, ShipToZip, ShipToCountry, ShippingMethod, Comments)" & _
        "SELECT OrderID, Email, FirstName, LastName, Company, Phone, SecondaryPhone," & _
        "Fax, ApproveStatus, OrderDate, [SKU-1], [SKU-2], Total, ShiptoName, ShipToAddress1, ShipToAddress2," & _
        "ShipToCity, ShipToState, ShipToZip, ShipToCountry, ShippingMethod, Comments " & _
        "FROM [Text;FMT=Delimited;HDR=YES;DATABASE=C:\ImportPath].[test.csv]"

Answer 1

首先检查文件中的字段数：

dim fso : Set fso = CreateObject("Scripting.FileSystemObject")
dim file : Set file = fso.OpenTextFile("C:\ImportPath\test.csv")
firstLine = file.ReadLine ' read first line
fieldArray = Split(firstLine, "whateverfielddelimiteryouhave")
numFields = UBound(fieldArray) + 1  'index on arrays from zero

这将告诉您有多少个字段。如果您已经定义了使用和不使用[SKU-2]的SQL，那么可以使用一个简单的IF来选择要执行的语句：

If numFields = 22 Then
sql = "INSERT INTO MumbyCSV (OrderID, Email, FirstName, LastName, Company, Phone, SecondaryPhone," & _
        "Fax, ApproveStatus, OrderDate, [SKU-1], [SKU-2], Total, ShiptoName, ShipToAddress1, ShipToAddress2," & _
        "ShipToCity, ShipToState, ShipToZip, ShipToCountry, ShippingMethod, Comments)" & _
        "SELECT OrderID, Email, FirstName, LastName, Company, Phone, SecondaryPhone," & _
        "Fax, ApproveStatus, OrderDate, [SKU-1], [SKU-2], Total, ShiptoName, ShipToAddress1, ShipToAddress2," & _
        "ShipToCity, ShipToState, ShipToZip, ShipToCountry, ShippingMethod, Comments " & _
        "FROM [Text;FMT=Delimited;HDR=YES;DATABASE=C:\ImportPath].[test.csv]" 
Else
sql = "INSERT INTO MumbyCSV (OrderID, Email, FirstName, LastName, Company, Phone, SecondaryPhone," & _
        "Fax, ApproveStatus, OrderDate, [SKU-1], Total, ShiptoName, ShipToAddress1, ShipToAddress2," & _
        "ShipToCity, ShipToState, ShipToZip, ShipToCountry, ShippingMethod, Comments)" & _
        "SELECT OrderID, Email, FirstName, LastName, Company, Phone, SecondaryPhone," & _
        "Fax, ApproveStatus, OrderDate, [SKU-1], Total, ShiptoName, ShipToAddress1, ShipToAddress2," & _
        "ShipToCity, ShipToState, ShipToZip, ShipToCountry, ShippingMethod, Comments " & _
        "FROM [Text;FMT=Delimited;HDR=YES;DATABASE=C:\ImportPath].[test.csv]"
End If

CurrentDB.Execute sql

第二种选择是读取要导入到数组中的值，如果值是空字符串，则将其替换为NULL，这样在插入步骤中就不会设置该字段。但是，如果您必须按照您的代码直接从文件导入，我不认为您可以这样做。

Answer 2

考虑使用TransferText，它不需要任何列的顺序并忽略缺少的列，只要要导入的当前文件在表中的某处维护列：

DoCmd.TransferText acImportDelim, , "MumbyCSV", "C:\ImportPath\test.csv", True