Import multi-line text file into one record using text keys

[dweis]

I have a flat file, 52 lines long, that needs imported into my database as a workable file. There will be multiple text files of varying length - this is an EDI 204 and we receive a couple hundred per day. I was able to create the outbound EDI using a file parsing program. The problem is the inbound. How can I get the multiple lines into one record? I need everything from the ISA* to the end and I don't really need the qualifiers.

What I want is to be able to say, ISA line, from 18 - 25 is field 1, 1st N9 line is field 2, 2nd N9 line is field 3, etc. But I can make it work if I can get the whole thing into one access record, say with one field per line break. If I can do that, then I would need everything from ISA to ISA (there will be multiple ISA's with 50 -52 lines per ISA, in one text item) with records based on line breaks. Any ideas?

Basically, is there any way to get the multiple lines into one record? Thanks.

 

[TomCologne]

Maybe this will give you an idea:

' References: 
' Microsoft DAO 3.6 Object Library, 
' Microsoft Scripting 'Runtime

Sub FileImport

Dim rst As DAO.Recordset
Dim fs As FileSystemObject, f As Object

Const ForReading = 1

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("C:\Folder\file.txt",ForReading )


Set rst = CurrentDb.OpenRecordset("tblXXXXX")

    With rst
                
     'Do While f.AtEndOfStream <> True
        
        'strLine = Trim(f.ReadLine)
        'intLineNo = f.Line

	  strLine = f.ReadAll	

		.AddNew

        	!FldMemo = strLine 'FldMemo = Memo
         
        	.Update

	 'Loop
        
    End With

rst.Close
f.Close

Set rst = Nothing
Set fs = Nothing
Set f = Nothing
'....
End Sub

This example would import the entire file into one memo field but I wouldn't recommend it.

Looping line by line gives you more options and is easier on the memory resources.

The design of the destination table depends on how much the files vary.

See "FileSystemObject Object" in VBA Help for more.


TomCologne

 

[dweis]

Tom, thanks for the suggestion and for the help. I stumbled upon a similar thread this morning,

http://www.tek-tips.com/viewthread.cfm?qid=765439

and was able to alter the code snippet to translate my EDI document. All I needed was the ability to create a new record when ISA is encountered and pull only certain info. I haven't completed the code for all the necessary fields, but here's what I've got for anyone who may be able to use it.

Dim MyRS As Recordset
Dim strLine As String
Dim strCol As String
Dim strValue As String
Dim vSplit As Variant
Dim blnRecord As Boolean

blnRecord = False
Open "c:\204.txt" For Input As #1

Set MyRS = CurrentDb.OpenRecordset("edi_imp", , adOpenDynamic)

Do
Line Input #1, strLine
vSplit = Split(strLine, "*")
If UBound(vSplit) > 1 Then

strCol = Trim(vSplit(0))

Select Case UCase(strCol)
Case "ISA"

strValue = Trim(vSplit(7))

If blnRecord Then
MyRS.Update
End If
MyRS.AddNew
blnRecord = True
MyRS.Fields("ISA").Value = strValue
Case "N9"
If Trim(vSplit(1)) = "BM" Then

strValue = Trim(vSplit(2))
MyRS.Fields("BM").Value = strValue
End If
If Trim(vSplit(1)) = "V3" Then

strValue = Trim(vSplit(2))
MyRS.Fields("V3").Value = strValue
End If
If Trim(vSplit(1)) = "SI" Then

strValue = Trim(vSplit(2))
MyRS.Fields("SI").Value = strValue
End If
If Trim(vSplit(1)) = "ZZ" Then
If Len(Trim(vSplit(2))) = 2 Then

strValue = Trim(vSplit(2))
MyRS.Fields("N9_code").Value = strValue
Else

strValue = Trim(vSplit(2))
MyRS.Fields("n9_note").Value = strValue
End If
End If

Case "N1"
If Trim(vSplit(1)) = "PU" Then
'strCol = Trim(vSplit(1))
strValue = Trim(vSplit(2))
MyRS.Fields("n1pu").Value = strValue
End If
If Trim(vSplit(1)) = "DZ" Then
'strCol = Trim(vSplit(1))
strValue = Trim(vSplit(2))
MyRS.Fields("n1DZ").Value = strValue
End If


End Select
End If
Loop Until EOF(1)
If blnRecord Then
MyRS.Update
End If

MyRS.Close
Close 1


End Sub

Thanks again for your assistance.