error "Record too large" importing text files

[scottian]

im using a bit of code that loops throughthe files in set location and imports to a single table. this has worked for ages, but recently the files have changed slightly in that when i now open then in notepad i can see the carriage return characters in the middle of the record, so when i try to import them i get the above error....any ideas how i import the file or correct the carriage return before importing

"My God! It's full of stars...

 

[JerichoJ]

Importing Text files into Microsoft Access using the included ISAM driver requires that lines not exceed a certain length and that lines are terminated by both Carriage Return and LineFeed characters. I don't remember the maximum line length, possibly 255, or maybe 1024.

I have had this problem. The file I had to process had only Linefeed characters at the end of each line. So what I did to solve it was to write code that pre-processed the import file by reading it in, replacing the existance of vbLF with vbCrLf, and then writing the file back out to disk. Then, calling the docmd.transfertext method to import the file.

I use the LargeTextFile class that was provided in the Access 2000 Developer Handbook, Volume 1 by Getz, Litwin and Gilbert. It uses the "old" direct file manipulation commands (Open, Close, Line Input, Print, etc) to process very large files.

The code below assumes you have that class module. Also, I wrote this in Access 97, so I had to use the adhReplace function. But, if you are using Access 2000 or above, the Replace function is built-in to VBA.

Dim eFile As LargeTextFile
Dim oFile As LargeTextFile
Dim inFileName As String
Dim outFileName As String

'inFileName must contain the full path and filename of the
'input file.  outFileName must contain the full path
'and filename for the cleaned up output file
    
If Dir$(inFileName) <> "" Then
  Set eFile = New LargeTextFile
  eFile.Path = inFileName
  eFile.OpenMode = tfOpenReadOnly
  eFile.FileOpen
  eFile.ReadNext
  If Len(eFile.Text) > 254 Then
    Set oFile = New LargeTextFile
    If Dir$(outFileName) <> "" Then
      Kill tmpFileName
    End If
    oFile.Path = outFileName
    oFile.OpenMode = tfOpenAppend
    oFile.FileOpen
    oFile.Text = adhReplace(eFile.Text, vbLf, vbCrLf)
    Do Until eFile.EOF
      eFile.ReadNext
      oFile.Text = adhReplace(eFile.Text, vbLf, vbCrLf)
    Loop
    oFile.FileClose
    eFile.FileClose
End If

Now you can import the "fixed" file.