Importing complext text that contains an array

[bill]

I am trying to import a tab and special character
delimited text file created by a program writen in 3D
Pick. I don't have any trouble importing a normal text
file and this one imports fine with the exception that I
need to create a second table to include the data that is
delimited by the special character chr(0253) ý. This is
what the raw text file looks like. The header GL is the
general ledger account number and the LTOT is the dollar
amount

DATE1 INVOICE DATE2 BILL# GL
LTOT
11/26/2003 125859 11/24/2003 532966 4011ý4027ý
251.55ý10.07ý0.00
11/25/2003 125826 11/20/2003 672045
4011ý4027ý4028 90.95ý2.73ý10.91
11/25/2003 125807 11/14/2003 49441
4011ýý4027ý4023 175.00ý0.00ý5.25ý14.82

Note: sometime the GL field ends with ý and sometimes it
does not.
This is what I need to convert the data to:

strInv strGL curAmt
125859 4011 251.55
125859 4027 10.07
125859 9999 0.00
125826 4011 90.95
125826 4027 2.73
125826 4028 10.91
125807 4011 175.00
125807 9999 0.00
125807 4027 5.25
125807 4023 14.82

The number of entries in the GL and LTOT field can be as
few as 1 and as great as 50; however, GL and LTOT will
have the same number of entries. Please note that there
may not be a value between two ýý; therefore, I need to
convert the value to 9999 for the GL field and 0.00 for
the LTOT field.

I am thinking the only way to import this data is to do a
simple text import that contains the GL and LTOT data in
one two data fields and then create a second table to
divided the fields GL and LTOT into separate records. the
internal array. The invoice number is the key field.

The first table contains each of the fields that the text
file has. The second table has the following fields,
strInv, strGL, curAmt

I think the code should work something like this:

Read recordset1 table1
create recordset2 table2

read first record in recordset1

Read last character in field strGL
if last character is = ý
then count = 0
else count = 1
end if

do while count2 <= count

insert INVOICE INTO strInv

Read first character in field strGL

if character is = ý
then count = count+1
end if
read next character



Read first character in field strGL
do while character is <> ý
add character to strGL_temp
read next character

move strGL_tem to strGL

Read first character in field curAmt
do while character is <> ý
add character to curAmt_temp
read next character

move curAmt_tem to curAmt

count2 = count2 + 1


I think this covers the basic logic with the exception of
inserting 9999 for a null account and 0.00 for a null
amount. What I need help with is what commands would be
best to use. This is especially true for stepping through
the character strings in the fields GL and LTOT and moving
it to the second record set. An example of the code for
one of these fields would be greatly appreciated.

Please answer through the news group, but if you need to
contact me directly e-mail me at b_samples(nospam)
@refdelserv.com

Thanks
Bill

 

[John Nurick]

Hi Bill,

You're on the right lines, I think. But don't bother trying to step
through the strings in the first table. Instead, use the Split()
function, which is included in recent versions of Access VBA (and in
earlier versions can be used if you set a reference to the VBScript
library). Somethign a bit like this pseudocode:

Dim arGL As Variant
Dim arLTOT As Variant
Dim j as Long
Dim strSQL As String
Dim db As DAO.Database

Set db = CurrentDB()
...
Do
arGL = Split(recordset1.Fields("GL").Value, "ý")
arLTOT = Split(recordset1.Fields("LTOT").Value, "ý")
For j = 0 to UBound(arGL)
strSQL = "INSERT INTO table2 (strINV, strGL, curAMT) VALUES (" _
& recordset1.Fields("INVOICE").Value, arGL(j), arLTOT(j) & ");"
'NB: quotes and ampersands needed on previous line in order to get a
'valid SQL single-record append query
db.Execute(strSQL, dbFailOnError)
Next
recordset1.MoveNext
Loop Until recordset1.EOF

 

[John Nurick]

Forgot to say: to handle the exceptional cases, use something like this


In the For..Next loop, instead of referring directly to the field values
while building the SQL statement, first assign them to variables:

Dim strGL As String
Dim strLTOT as String
...
arGL = Split(...)
For j = ...
If Len(arGL(j)) = 0 Then
strGL = "9999"
Else
strGl = arGL(j)
End If
If Len(arLTOT(j)=0...

 

[Bill]

Thanks John, I will give it a try this weekend when I have
some quite time.