Open Text File and Change Data

[Tim]

I am trying to figure out the best way in VBA for Access to open a text file,
look for specific records and change the data in specific columns.

Does anyone know what the proper functions would be to use here? I looked
at OpenTextFile, however, using that I would have to read the entire file and
then re-write an entire new one. I can only read or append with that
function.

Any ideas are greatly appreciated!!

Tim

 

[Douglas J. Steele]

Unfortunately, I don't believe there's anyway other than reading the
existing file into memory, deleting it, then creating a new file with the
new data.

The Text IISAM is read-only.

 

[Robert Morley]

Unfortunately, you'll probably have to do this entirely through file
manipulation. The ODBC Microsoft Text Driver is great for reading and
sorting data, but only supports the SELECT and INSERT statements, not
UPDATEs, IIRC.

You definitely want to avoid the OpenTextFile method, as that relies on the
FileSystemObject, which is bloated and sluggish. The exact code you would
want will vary depending on file format, but would probably look something
like this for a tab-delimited file. You'll have to adjust the code based on
whatever format you're using (hint: tab-delimited is MUCH easier to handle
than fixed-column if you have a choice in the matter). It reads the initial
file in line-by-line, writes it out to a new file, then kills the old file
and renames the new one. It's probably not the fastest possible method, but
should do the trick for anything short of a 100MB file or so. This is typed
off the top of my head, so may contain errors...watch out for line-wrapping:

Public Sub UpdateFile(ByVal strFileName As String)
Dim arrData As Variant
Dim strLineIn As String
Dim intFileNumIn As Integer
Dim intFileNumOut As Integer

intFileNumIn = FreeFile()
Open strFileName For Input As #intFileNumIn

intFileNumOut = FreeFile()
Open strFileName & ".tmp" For Output As #intFileNumOut

If Not EOF(intFileNumIn)
'Assumes column names for first line; simply re-outputs them.
Line Input #intFileNumIn, strLineIn
Print #intFileNumOut, strLineIn
End If

Do Until EOF(intFileNumIn)
Line Input #intFileNumIn, strLineIn
arrData = Split(strLineIn, vbTab)

'Sample: update data in column 4 (0-based array) to add "NewText" to
the end.
arrData(3) = arrData(3) & "NewText"

Print #intFileNumOut, Join(arrData)
Loop
Close #intFileNumOut
Close #intFileNumIn

Kill strFileName
Name strFileName & ".tmp" As strFileName
End Sub


Rob

 

[Jamie Collins]

Unfortunately, you'll probably have to do this entirely through file
manipulation. The ODBC Microsoft Text Driver is great for reading and
sorting data, but only supports the SELECT and INSERT statements, not
UPDATEs, IIRC.

You do recall correctly but you've not considered (out loud <g>) the
ability to CREATE and DROP tables.

For example, consider this three-row table:

CREATE TABLE db#txt (
key_col INTEGER,
data_col INTEGER
)
;
INSERT INTO db#txt (key_col, data_col)
VALUES (1, 1)
;
INSERT INTO db#txt (key_col, data_col)
VALUES (2, 2)
;
INSERT INTO db#txt (key_col, data_col)
VALUES (3, 3)
;

Trying to UPDATE directly (as you point out) does not work e.g. this:

UPDATE db#txt
SET data_col = 22
WHERE key_col = 2
;

generates the failure message, "Deleting data in a linked table is not
supported by this ISAM."

Why did the message mention "Deleting"? Well, an update is logically
equivalent to a delete and an insert (which is why, incidentally, in
SQL Server a trigger has logical tables named 'updated' and 'inserted'
but not 'deleted'). As the above message suggests, a direct DELETE
will not work either.

However, we do have to ability to CREATE and DROP new tables,
therefore a few extra steps will give the desired results: create a
new copy table and populate it with only the data to be retained plus
the new (updated) data; drop the original table; recreate the original
table and populate the newly-created original table with data from the
copy table; drop the copy table. In SQL:

SELECT DT1.key_col, DT1.data_col
INTO db_copy#txt
FROM
(
SELECT key_col, data_col
FROM db#txt
WHERE key_col <> 2
UNION ALL
SELECT key_col, 22
FROM db#txt
WHERE key_col = 2
) AS DT1
;
DROP TABLE db#txt
;
SELECT key_col, data_col
INTO db#txt
FROM db_copy#txt
;
DROP TABLE db_copy#txt
;

Jamie.

--

 

[Jamie Collins]

Typo! Should have read,

incidentally, in SQL Server a trigger has logical tables named
'deleted' and 'inserted' but not 'updated'

Jamie.

--