read text file line by line and insert/update values in table

Question

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?

Answer 1

I'm assuming here that your text file is well formatted / delimited. As such, try recording a macro, then use the import wizard. [The import wizard can be activated by command line and have all necessary values passed into it by the function call.] Then copy / move the code that is generated by the macro into your method, providing for whatever variables you like (this is a method I've used in the past).

Answer 2

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. import the text file
2. append or update records in a table based on the imported data

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

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.

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.