Import txt file - records on multiple lines

[Kate P]

Hi,

I have a txt file (originally .dat file actually) I need to import into a
table in Access.

It has multiple records and each record is over several lines. Fields are
delimited by '|' and the end of each record is indicated by '@@'. Apart from
that there are carriage returns & spaces in some of the middle fields (which
would be Memo fields) which need to be kept.

Eg:

1|xxx|a: cat
b:dog
c: mouse rat |date@@

The above is 1 record with 4 fields. In a file there could be any number of
records like this - not always the same number of lines per record.

Is there any way to import the records?? give an end of record delimiter???

Thanks,

 

[Scott Lichtenberg]

Kate,

There are two ways to approach this:

1. If you have a good text editor with a hex mode, you should be able to
reformat the file. The hex code for character return / line feed (CRLF) is
0d 0a. Use the find/replace function to replace the 0d 0a's with Null.
This gives you a streamed file. Then replace your @@'s with 0d 0a. This
will give you a file where the physical records match the logical records.
You can now import this into Access as a delimited text file.

2. The second way is to use the Open and Input statements to read the file
character by character. The function below will read through an input file
and unwrap it, writing the logical records to a new file. As I have written
it, the function checks for the tilde as the record separator. You should
be able to modify this to fit your needs.

Public Function fnUnwrap(InFile as String, OutTable as String)

Dim strChar As String * 1
Dim strRecord As String

Open InFile For Binary As #1
Open OutFile For Output As #2

Do While Not EOF(1)

'Read input file one character at a time.
strChar = Input(1, #1)

Select Case strChar

Case vbcrlf, vbCr, vbLf
'Do Nothing

Case "~" 'Rec Sep found. Output record.
Print #2, strRecord
strRecord = ""

Case " " 'Do not append spaces to the beginning of a segment.
If strRecord = "" Then
'We have a new record. Do Nothing
Else
strRecord = strRecord & strChar
End If

Case Else
strRecord = strRecord & strChar

End Select

Loop