Extract data from Memo field

[Guest]

Hi all,
My table (from other app) has the primary key field SKU, and the Memo field
with the sample data like this:

+07/01/2003 20000000 2000000007/01/2004
L04/11/2003 1944000 20000000
L09/01/2004 976000 20000000
H12/01/2004 0 2000000007/10/2004
L11/10/2004 1200000 20000000
C22/10/2004 20000000 20000000
N31/12/2004 1672000
N12/01/2005 1672000
C20/05/2005 20000000 20000000
C10/08/2005 20000000 20000000
X30/09/2005 20000000 0

I need to extract the data into another (sub) table which have six fields :
- Field 1 is SKU
- Field 2 is the First character of each row,
- Field 3 is the next 10 character of each row,
- Field 4 is the next 13 " "
- Field 5 is the next 15 " "
- Field 6 is the next 10 " "

Could you help me? Thanks in advance.

 

[Dirk Goldgar]

Hi all,
My table (from other app) has the primary key field SKU, and the Memo
field with the sample data like this:

+07/01/2003 20000000 2000000007/01/2004
L04/11/2003 1944000 20000000
L09/01/2004 976000 20000000
H12/01/2004 0 2000000007/10/2004
L11/10/2004 1200000 20000000
C22/10/2004 20000000 20000000
N31/12/2004 1672000
N12/01/2005 1672000
C20/05/2005 20000000 20000000
C10/08/2005 20000000 20000000
X30/09/2005 20000000 0

I need to extract the data into another (sub) table which have six
fields :
- Field 1 is SKU
- Field 2 is the First character of each row,
- Field 3 is the next 10 character of each row,
- Field 4 is the next 13 " "
- Field 5 is the next 15 " "
- Field 6 is the next 10 " "

Could you help me? Thanks in advance.

I'd probably do this with nested recordsets, something like the
following untested air code:

'----- start of code -----
Dim rsIn As DAO.Recordset
Dim rsOut As DAO.Recordset
Dim db As DAO.Database
Dim astrLines() As String
Dim strLine As String
Dim I As Long

Set db = CurrentDb
Set rsIn = db.OpenRecordset("YourInputTable")
Set rsOut = db.OpenRecordset("YourOutputTable")

With rsIn
Do Until .EOF
astrLines = Split(!YourMemoField, vbCrLf)
For I = LBound(astrLines) To UBound(astrLines)
strLine = astrLines(I)
If strLine <> vbNullString Then
rsOut.AddNew
rsOut!Field1 = !SKU
rsOut!Field2 = Left(strLine, 1)
rsOut!Field3 = Mid(strLine, 2, 10)
rsOut!Field4 = Mid(strLine, 12, 13)
rsOut!Field5 = Mid(strLine, 25, 15)
rsOut!Field6 = Mid(strLine, 40, 10)
rsOut.Update
End If
Next I
.MoveNext
Loop
.Close
End With

rsOut.Close

Set rsIn = Nothing
Set rsOut = Nothing
Set db = Nothing
'----- end of code -----

 

[Guest]

Thanks, Dirk. It's great. Your code is very useful for me.
Thanks again.