I/O .txt files into MS-Access

[anonymous]

[Originally posted by msprb]

The task at hand is using an Access Module, to input a .txt file saved in a local dir, and then to put that information on a table. I also need to Parse the columns, and see if a particular record exists in a table, if not then append the record, otherwise update the existing record.ÿ If someone knows a way around this please lemme know

[anonymous]

[Originally posted by Ravi]

hi,
yes, this is possible. but b4 can u tell me like:
is ur text fileÿ contains fixed numberÿ ofÿ record length, field lenght. then u can read column by column for each row, u can search ur table for duplicacy also and then u can append the record also.
let me know, if u hv queries.
bye
ravi

[anonymous]

[Originally posted by msprb]

The text file doesn't contain a fixed number of rows, but it does have a fixed number of fields, 6 to be exact, but im only dealing with tables, no queries involved, thanks.

[anonymous]

[Originally posted by Jim]

try something like this.

dim x as integer
dim y as integer
dim flds(6) as string
dim db as database
dim str as string
dim rsRef as recordset


x = freefile
set db = currentdb
set rsref = "db.openrecordset("select * from Mytable")


open "c:\Myfile.txt" for input as #x
do while not eof(x)
line input #x, str
for y = 0 to 5
flds(y) = left(str, instr(1,str,",")-1)
str = mid(str,instr(1,str,",")+1)
next
rsref.findfirst("myfield = " & flds(0))
if rsref.nomatch then
str = "Insert Into mytable myfield1, myfield2...."
str = str & "values " & chr(39) & flds(0) & chr(39)
str = str & ", " & chr(39) & flds(1) & chr(39)
.
.
.
else
str = "update mytable set "
str = str & "Myfield2 = " & chr(39) & flds(1) & chr(39)
str = str & " Myfield3 = " & chr(39) & flds(2) & chr(39)
.
.
.
str = str & " where myfield1 = " & chr(39) & flds(0) & chr(39)
end if
db.execute str
str = ""
loop
close #x
rsref.close
set rsref = nothing
set db = nothing