Importing Text file with Carriage Returns not being recongnized

[mxrider]

I have a .txt file that i'm trying to import into Access 97 and it is delimited by -|- (that's shift \ above the enter)
I have 132 fields then a carriage return at the end.
The problem being, when I import more than one line of text, access doesn't recognize the carriage return and start a new row. It just keeps going until it hits Record too Long error. @ field 255.

Why isn't Access reading my Carriage return in my text file?

jody@maynardfarms.com is my email

Thank you

 

[AlexMidd]

Are you sure each line is delimited by a carriage return? I am not sure what access looks for but carriage returns are not always what you think they are, until you check the ASCII values actually used in the text file. Some are value 9, 10, 12 or 13, or a combination. Some of these will work and others won't. Have fun! )

Alex Middleton

 

[smedvid]

Just curious if this is a Unix file... I had some strange stuff once with a Unix text/ASCII file. I needed to run a Unix program called Unix2DOS to convert the file to have the appropriate end-of-line characters. So... if the file is Unix based, that may be the problem. Of course, this was many moons ago... htwh Steve Medvid
"IT Consultant & Web Master"

http://users.erols.com/medvid/

 

[Ed2020]

A standard text file contains two characters at the end to delimit records Chr(10) and Chr(13) (a carriage return and a line feed).

Could it be that your text file contains only one of these rather than both?

Ed Metcalfe.

 

[a9]

The following code modified to your file details will tell you how Access is handling the data. If it is being read as a single line then you can modify the code in the loop to read the data upto which ever charecter is being used to delimit the lines and then write the results into a table. I have additional code if you need to use this method to get at your data.

Function CountLines() As Double
On Error GoTo Err_Countlines
Dim FileData As String, Fname As String, LineCounter As Double
Close #1
Fname = "D:\My Documents\testfile.txt" 'File to be read
Open Fname For Input As #1 ' Open to read file.
LineCounter = 0 'zero count
Do While Not EOF(1) ' Continue until end of file.
Line Input #1, FileData 'Read file a line at a time
LineCounter = LineCounter + 1 'Increment line count
Loop
Close #1
CountLines = LineCounter
Exit_Countlines:
Exit Function
Err_Countlines:
MsgBox Err.Description, vbCritical + vbOKOnly, "File Read Error"
Resume Exit_Countlines
End Function
Function DisplayFileLines()
MsgBox "The File contains " & CountLines() & " lines", vbInformation + vbOKOnly, "File Report"
End Function

 

[mxrider]

Turns out the text file was indeed saved on a UNIX machine. So Steve Medvid you were correct, even though I did have to figure this out through trial and many errors .. but all is well.

I just downloaded Textpad 4.0 and opened the text file and did an immediate F12 save as.. and changed the file format to PC from UNIX..

Thanks again for everyones help..