import txt file to access 2007 (1 Viewer)

[ielamrani]

Hi,
I am trying to import a text file to a database but I am getting 3 rows for each record because the txt file is setup the same way. How do I fix the issue?

txt file stores the data like this:

line 1 name ssn dob
line 2 add city zip
line 3 pcp nurse

those 3 lines belong to the same patient and they're imported to access table the same way.

Thanks in advance

 

[mdlueck]

How good are you at coding VBA? I have developed VBA code to read a text file and import to a table. I manually interpret the file one character at a time, and perform the database operations based on the data the VBA code reads.

If you are not included to custom code a VBA importer, then perhaps someone else will have an alternate non-VBA suggestion.

 

[ielamrani]

Not good in vba. I can do the import using

DoCmd.TransferText acImportDelim, "PXED2006 Import Specification", "table1", "C:\file\PXED2006.txt"

which works but in different lines. I can't fix the txt file because it's huge.

Here is some of the data and the way it's formatted. The xxxxxx represetn SSN or ID# etc..

If I can import all the data then I can delete the columns I don't need, else I need only the following columns:

XXXXXX, xxxxxxx, Name (Baker...), Date (8/30/1957), xxxxx, Date (07/02/2006), Gender (M) # (1554)

Sometimes there is a third # (1 and 4 lines) but (2 and 3 lines do not have a third #.

Also I don't know if this can help but all the records ends with a 0 ,
Thanks in avance


xxxxxx ,xxxxxx ,BARKER ,RICHARD ,1780 FIRST AVENUE ,NEW YORK ,NY,10012 ,08/30/1957,M,
xxxxxx ,E,EMR,EO,07/02/2006,1554 ,0 ,DOCTOR UNASSIGNED ,0 ,DOCTOR UNASSIGNED ,M, , , , ,
0 ,


xxxxxx ,xxxxxx ,VANDERPLOEG ,JAN ,61 SOUTH THIRD ,BROOKLYN ,NY,11225 ,08/19/1959,M,
0 ,E,EMR,EO,07/02/2006,1837 ,0 ,DOCTOR UNASSIGNED ,0 ,DOCTOR UNASSIGNED ,C, , , , ,
0 ,


xxxxxx ,xxxxxx ,BENDA ,ALI ,2554 17 STREET ,BROOKLYN ,NY,11242 ,01/01/1941,M,
0 ,E,EMR,EO,07/03/2006,1413 ,0 ,DOCTOR UNASSIGNED ,0 ,DOCTOR UNASSIGNED ,S, , , , ,
0 ,

xxxxxx ,xxxxxx ,SEDERBAUM ,REBECCA ,30 WEST 63 STREET ,NEW YORK ,NY,10023 ,06/02/1974,F,
xxxxxx ,E,EMR,EO,07/03/2006,1437 ,0 ,DOCTOR UNASSIGNED ,0 ,DOCTOR UNASSIGNED ,H, , , , ,
0 ,