Question import text line by line

[geo.hatzel]

2JOHN SMITH 1234
3K1302201254350
3S1202201238467
2GEORGE MILLER 2947
3K2502201265306
2MARIA TOMPSON 5645
3K1302201254350
3S1202201238467
3E1202201238467
3X1202201238467

hi,
i want to import the above text file into access.
lines starting with "2" contain a persons details and the following lines that start with "3" are his records.
all lines should be imported as fixed width, different field breaks though for lines starting with "2" and lines starting with "3".

what came to my mind is to import in two different tables creating a common key for a persons details and records.
i am new at VB and not know how to do that.
if somedody could help, or have a better idea on how to import that data, please drop me a line.

thanks,
geroge.

 

[geo.hatzel]

bumped up...

 

[Kiwiman]

Howzit

I would import the text file into one staging table, and do all the work in the staging table to allocate common keys for the header and detail lines, as well as spliting the lines (use update queries) to its individual components based on the first character of each line. Once the lines have been split then I would append these to the main Header and Detail table.

If you have no indexes on the staging table they should import in the order that they are in the file, and then you can then loop through the staging table to determine the common key.

Something like (i have assumed that the common key is not part of either the header record or detail record):

Dim db as Dao.Database
Dim rs as DAO.Recordset
Dim strRS as string
Dim i as integer
Dim lngRec as long

' Set vartiable to hold relevant fields in recordset
strRS = "Select left(stagingtable.yourfield,1) AS REC, Stagingtable.KeyField From stagingtable"

set db = currentdb

set rs = db.openrecordset(strrs)

if rs.eof and rs.bof then   ' Check if recordset has records

else
' Required for accurate count
rs.movelast
rs.movefirst

lngRec = 0     ' You may want to generate this from the main table data

For i = 1 to rs.recordcount

    if rs!REC = "2" then lngrec = lngrec +1   ' As a new header record increment key field value by one, otherwise the value is the detail record therefore do not increment count

         rs.edit      ' Update recordset with key field
            rs!KeyField  = lngrec
         rs.update

         rs.movenext     ' move to next recordset record
 next i   ' next iteration

' Clean up
rs.close
set rs = nothing
set db = nothing

 

[Jasminewilson]

Hello friend........I am also new here............


I have a plain text file looking like this:
"some
text
containing
line
breaks"
Am I right pls ask just ask me

 

[geo.hatzel]

thanks Kiwiman, awesome stuff, go Blacks!

 

[Jasminewilson]

Hi friend thanks to reaply.........

 

[Kiwiman]

Hello Jasmine

Sorry I do not understand the problem you are having. Can you explain it more please

 

[Jasminewilson]

Hi friend........I have solved my problem. and sorry for disturbing........................