逐行读取文本文件并在表中插入/更新值 [英] read text file line by line and insert/update values in table

问题描述

我正在探索 DoCmd.TransferText 是否会做我需要的选项，但它似乎不会.如果数据不存在，我需要插入数据，如果存在则更新它

i am exploring the option of whether DoCmd.TransferText will do what i need, and it seems like it wont. i need to insert data if it does not exist and update it if it does exist

我打算像这样一行一行地读取文本文件:

i am planning to read a text file line by line like this:

Dim intFile As Integer
Dim strLine As String

intFile = FreeFile()
Open myFile For Input As #intFile
Line Input #intFile, strLine
Close #intFile

我想每一行都是一条记录.它可能会以逗号分隔，并且某些字段会有一个 " 文本限定符，因为在字段本身内我会有逗号

i guess each individual line will be a record. it will probably be comma separated and some fields will have a " text qualifier because within the field itself i will have commas

我的问题是，我如何将具有 双引号 有时作为文本限定符的逗号分隔文本文件读取到访问表中?

my question is how would i read a comma delimited text file that has double quotes sometimes as text qualifiers into a table in access?

推荐答案

我一直在关注您的相关问题，但不确定您在哪里遇到了问题.您似乎正在尝试通过此文本文件导入完成两件事:

I've been following your related questions, but am unsure where you're running into trouble. It seems like you're trying to accomplish two things with this text file import:

1. 导入文本文件
2. 根据导入的数据追加或更新表中的记录

也许将这些步骤作为 2 个单独的操作来处理会更有效率，而不是尝试将它们组合在一个操作中.

Perhaps it would be more productive to tackle those steps as 2 separate operations, rather than attempting to combine them in one operation.

我以为您已经完成了第 1 步.如果是这样，请将文本导入单独的表 tblImport，然后使用该表来驱动对主表的更改.如果您担心由于重复导入然后删除 tblImport 中的记录而导致数据库膨胀，您可以将 tblImport 设置为指向外部数据库的链接......以便将膨胀隔离到那个数据库.

I thought you had step #1 working. If so, import the text into a separate table, tblImport, then use that table to drive the changes to your master table. If you're worried about database bloat due to repeatedly importing then deleting records in tblImport, you could make tblImport a link to an external database ... so as to isolate the bloat to that one.

如果您需要第 2 步的更详细帮助，我想您必须告诉我们如何识别主表中不存在哪些导入的记录，以及如何匹配导入和主记录以进行更新.也许您可以删除在 tblImport 中有对应项的现有主记录，然后将所有 tblImport 附加到您的主表中.

If you want more detailed help for step #2, I think you will have to tell us how to identify which imported records don't exist in the master table, and how to match up imported and master records for the updates. Perhaps you could delete existing master records which have counterparts in tblImport, then append all of tblImport to your master table.

这篇关于逐行读取文本文件并在表中插入/更新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！