从 CSV 导入更新现有访问记录，本机为 MS Access 或在 VB.NET 中 [英] Update Existing Access Records from CSV Import , native to MS Access or in VB.NET

问题描述

我是我所在组织的票务系统的应用程序管理员.我们正在添加一个新客户，需要将他们的客户记录导入我们的系统.

I am the application administrator for a ticketing system at my organization. We're adding a new client, and need to import their customer records into our system.

但是，我们被拒绝访问以简单地从直接数据库连接中获取这些记录.

However, we have been denied access to simply grab these records from a direct database connection.

我们仅限于进入他们的票务系统，并将现有记录导出到 CSV 文件.

We are limited to entering their ticketing system, and running an export of the existing records to a CSV file.

为了使这项工作与我们的系统配合使用，我将这些 CSV 文件输入到 MS Access 数据库中，我们的系统将从该数据库读取和导入/更新票务系统上的记录.

In order to make this work with our system, I'm taking these CSV's and entering them into an MS Access database, which our system will read and import/update records on our ticketing system from.

但是，我找不到从 CSV 中的记录更新 MS Access 数据库中的记录的方法.

However, I cannot find a way to update records in the MS Access db from records in the CSV.

我可以将记录导入表中，但它会跳过任何已经存在的记录(客户数据中包含一个字段，用作唯一标识符/主键以区分新记录/现有记录).

I can import records into a table, but it skips any records that already exist (There is a field included in the customer data that is being used as a unique identifier / primary key to distinguish new / existing records).

任何已经包含此主键的记录都将被跳过，它不会更新包含该键的 ms 访问数据库中的记录.

Any records which already contain this primary key are simply skipped, it does not update the records in the ms access db containing that key.

除了创建基本表格和表单外，我没有太多使用 MS Access 的经验；这超出了我的正常工作范围.

I do not have much experience with MS Access other than creating basic tables and forms; it's outside my normal scope of work.

我需要找到一种方法来获取包含可能已在此 ms 访问数据库中或尚未在此 ms 访问数据库中的记录的 CSV 文件，如果 CSV 中不包含这些记录，则创建新记录，或者如果它们包含在 CSV 中则更新记录.

I need to find a way to take a CSV file containing records that may or may not already be in this ms access db, and create new records if not contained within the CSV, or update records if they are contained in the CSV.

这并不一定重要，我可以使用 vb.net 或宏来实现它，如果你能提供一种方法来做到这一点.

It doesn't necessarily matter how this is done, I could implement this using vb.net or a macro, if you could provide a way to do this with either.

我知道这有点偏离指导方针，我试图自己想办法做到这一点，但无法想出任何代码来测试并作为起点发布(我很抱歉).

I understand that this is a bit off-guidelines, I tried to find ways to do this on my own and haven't been able to come up with any code to test out and post as a starting point (my apologies).

推荐答案

基本上，您将不得不执行一系列操作查询:使用过滤器和连接的追加、更新和删除.您可以将查询保存为存储查询并通过 DoCmd.OpenQuery 调用它们，或者您可以在 VBA 中将它们编写为要传递到 DoCmd.RunSQL sqlStatement 或 的字符串CurrentDb.Excecute sqlStatement

Essentially, you will have to do a series of action queries: append, update, and delete that use filters and joins. You can save queries as stored queries and call them by DoCmd.OpenQuery, or you can write them in VBA as strings to be passed into DoCmd.RunSQL sqlStatement or CurrentDb.Excecute sqlStatement

导入

首先，使用 DoCmd.TransferText acImportDelim 将 CSV 导入临时表.请务必在导入之前从临时表中清除以前的 csv 数据.

First, import the CSV using DoCmd.TransferText acImportDelim into a temporary table. Be sure to clean out previous csv data from temp table prior to import.

DELETE FROM tempTable;

更新

然后，更新 temp 和 live table 之间的现有记录，但为 live table 中的现有客户提供条件.在 Access SQL 中，联接可用于更新查询，但您可能会遇到 不可更新 查询:

Then, update existing records between temp and live table but condition for existing customers in live table. In Access SQL, joins can be used in update queries but you may run into not updateable queries:

UPDATE tempTable INNER JOIN liveTable 
ON tempTable.CustomerID = liveTable.CustomerID
SET liveTable.Col1 = tempTable.Col1,
    liveTable.Col2 = tempTable.Col2,
    liveTable.Col3 = tempTable.Col3, 
    ... ;

或者，绕过不可更新的查询:

Alternatively, to bypass non-updateable query:

UPDATE liveTable
SET liveTable.Col1 = DLookUp("Col1", "tempTable", "CustomerID=" & liveTable.CustomerID),
    liveTable.Col2 = DLookUp("Col2", "tempTable", "CustomerID=" & liveTable.CustomerID),
    liveTable.Col3 = DLookUp("Col3", "tempTable", "CustomerID=" & liveTable.CustomerID), 
    ... 
WHERE CustomerID IN 
    (SELECT CustomerID FROM tempTable);

完成后，清除临时表中刚刚更新的记录(以免与追加步骤和重复条目冲突):

Once done, clean out records just updated from temp table (to not conflict with append step and duplicate entries):

DELETE FROM tempTable 
WHERE CustomerID IN 
      (SELECT CustomerID FROM liveTable);

追加

最后，将临时表中的新记录附加到实时表中，但客户不在实时表中的条件，您可以使用 LEFT JOIN ... NULL:

Finally, append new records from temp table into live table but condition for customers NOT in live table which you can do with the LEFT JOIN ... NULL:

INSERT INTO (Col1, Col2, Col3 ...) 
SELECT Col1, Col2, Col3 ... 
FROM tempTable LEFT JOIN liveTable 
ON tempTable.CustomerID = liveTable.CustomerID
WHERE liveTable.CustomerID Is Null;

这篇关于从 CSV 导入更新现有访问记录，本机为 MS Access 或在 VB.NET 中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！