novice question - import text file w field ids on each row to Acce

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a tab delimited text file that is organized without column headers as
follows:

123456789 2006/10/16 09:37 PtSSN 123-45-6789
123456789 2006/10/16 09:37 PtAge 61
123456789 2006/10/16 09:37 PtDOB 01/01/1990
123456789 2006/10/16 09:37 PtSex M
123456789 2006/10/16 09:37 PtName SMITH, JOHN
987654321 2006/10/16 09:37 PtSSN 987-65-4321
987654321 2006/10/16 09:37 PtAge 53
987654321 2006/10/16 09:37 PtDOB 02/01/1990
987654321 2006/10/16 09:37 PtSex F
987654321 2006/10/16 09:37 PtName SMITH, JANE

(didn't paste in well - first tab appears to be missing)

....where the first column (ie. 123456789, 987654321) is a unique id from a
demographics table in the exporting application, second column is date the
data was entered, third column is the time entered, fourth column is the
field id for the data, and the fifth column is the value entered into the
field.

How do I import this into an Access table?

P. S. I am used to using files that look like this:

123456789 2006/10/16 09:37 123-45-6789 61 01/01/1990 M SMITH,JOHN
987654321 2006/10/16 09:37 987-65-4321 53 02/01/1990 F SMITH,JANE
 
Hi Marvin,

There are several possible approaches. Here's one that doesn't need any
programming.

1) Link or import the text file as it stands, with the following fields.
Let's call this tblTemp.

ID - text
EnteredDate - text
EnteredTime - text
FieldName - text
FieldValue - text

Then create another table with fields

ID - text
Entered - date
ptSSN - text[12]
ptAge - number (long)
ptDOB - date
ptSex - text[1]
ptName - text

Next, create a query that joins tblTemp to itself on ID, EnteredDate and
EnteredTime - five times over. Start with a new query, and add tblTemp
five times. Then right-click on the first copy of tblTemp, select
Properties, and give it an alias of A. Repeat with the remaining four
copies (B to E).

Then drag the three fields ID, EnteredDate and EnteredTime one at a time
from B to A. Repeat from C to A, D to A and E to A.

Next add the fields. For Entered, use a calculated field:
Entered: A.EnteredDate + A.EnteredTime

For the other values, it's calculated fields too:
ptSSN: A.FieldValue
ptAge: B.FieldValue

Finally, add the criteria. Drag "FieldName" from A into the query grid,
and give it a criterion "PtSSN". FieldName from B gets "PtAge" and so
on.

When you get the query working properly it will give you data arranged
in the way you're accustomed to. Convert it into an append query and use
it to move the data into your "real" table.

In SQL, the query will look like this (reformatted for clarity):

SELECT A.ID,
A.EnteredDate+A.EnteredTime AS Entered,
A.FieldValue AS ptSSN,
B.FieldValue AS ptAge,
C.FieldValue AS ptDOB,
D.FieldValue AS ptSex,
E.FieldValue AS ptName

FROM tblTemp AS E
INNER JOIN (tblTemp AS D
INNER JOIN (tblTemp AS C
INNER JOIN (tblTemp AS B
INNER JOIN tblTemp AS A
ON (B.EnteredTime = A.EnteredTime) AND (B.EnteredDate = A.EnteredDate)
AND (B.ID = A.ID))
ON (C.EnteredTime = A.EnteredTime) AND (C.EnteredDate =
A.EnteredDate) AND (C.ID = A.ID))
ON (D.EnteredTime = A.EnteredTime) AND (D.EnteredDate =
A.EnteredDate) AND (D.ID = A.ID))
ON (E.EnteredTime = A.EnteredTime) AND (E.EnteredDate =
A.EnteredDate) AND (E.ID = A.ID)

WHERE A.FieldName = "PtSSN"
AND B.FieldName = "PtAge"
AND C.FieldName = "PtDOB"
AND D.FieldName = "PtSex"
AND E.FieldName = "PtName";
 
Back
Top