Data import into access

[Zark3]

Hey all,
Just out of curiosity, hoping someone might have a good starter for me
on this, I need to import data into Access from a text file that
unfortunately isn't in the standard layouts of Access. Question now is
of course, how do I import?

File layout:
---
Field1 Value1
Field2 Value2
; Value2 (contd.)
Field3 Value3
$
Field1 Value1
Field3 Value3
---
etc. etc.

In other words, I have a grand total of 422058 lines, in which records
are separated by a dollar sign, values continued on a next line (line
break in input) have line 2 (and/or 3 etc.) preceded by a semicolon,
and field names (single words) and their respective values are
separated by a space, separating the fields by a line break. Records
will have ten to fifteen fields, fields not filled in don't appear in
the file (so no dummy/empty data).

The big question of course, who can provide me with a starter on how to
approach this import? Should I manipulate the file externally to some
format that Access can read, or can I trick Access into reading this
somehow?

Any help is appreciated,
Thanks,
Chris

 

[Douglas J Steele]

You're going to have to use the VBA File Input routines to read that file
line by line, building the SQL for an INSERT INTO statement for each record.

 

[Zark3]

Ouch! Sounds like an nested jumble of if and while-statements waiting
to happen?
something like this pseudocode?
---
while not found dollarsign
if(first part of inputline matches field name)
fieldstring += fieldname
valuestring += remainder
else if(first char of inputline == ";")
valuestring += inputline
sqlstring = "insert into table1 (" + fieldstring + ") values (" +
valuestring + ")"
[somehow execute sqlstring (?)]
loop
---

Am I on the right path here do you reckon?

Thanks for the tip,

Chris

 

[Douglas J. Steele]

Yup, that's about it.

The "somehow execute sqlstring" would be CurrentDb.Execute sqlstring,
dbFailOnError (assuming you've got a reference set to DAO)