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

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Zark3" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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

Yup, that's about it.

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Zark3" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>