Importing Text File that is vertically oriented

[ghadley_00]

Hi,

I have a MS access database into which I need to import a text file
that is oriented vertically (fields are listed from top to bottom with
the value for each field appearing to the right of the field name (with
a "|" as a delimiter)). The text file will only have 1 record's data.
I would ideally like to setup a linked table to import this data - does
anyone know of a way I can setup this import?

Any help that can be provided would be greatly appreciated.

Best wishes,

George Hadley
(e-mail address removed)

 

[John Nurick]

Hi George,

There's no direct way of doing this.

If you just want to look up values of the fields in your one-record
file, set it up as a linked table with two |-separated fields (let's
call them FieldName and Value). You can then use DLookup() to look up
the Value corresponding to any FieldName.

If you have to get the fields arranged "horizontally" so you can join
this one-record "table" with other tables, the following will work:

1) Set up linked table as above. I'll call it tblVertical

2) Create a query on the table that returns both fields and a dummy
"Row" field:
SELECT 1 AS Row, FieldName, Value FROM tblVertical;

3) Use that query as the source for a crosstab query that uses Row for
row heading, FieldName for column headings, and First(Value) for the
tabulation (and no row totals).

4) This query returns the fields and values "horizontally", ready for
use in other queries.