Import Text File with Hard Return

[EZEason]

I have a .txt file that I need to import. (Space Delt) The problem that I'm having is that the .txt file has a hard retrun in the middle of each record. So one record is on two lines. There are 83 fields and about 8k records. I have set up an import spec that names and sets all the fields, but I need to remove the return in the middle of each record before it import correctly. I'm using Access 2000. Does anyone have any suggestions?

What doesn't kill you makes you stronger.

 

[p27br]

Hi, a tricky one !

I'd try using the TextStream object (Microsoft Scripting Runtime)to rewrite the file taking out the Carriage return.
Here is a function I wrote that copies a text file to itself modifying some liness, maybe you could adapt it to your needs.
Ther's a property called .AtEndOfLine

Dim fs As FileSystemObject
Dim txsFileFrom As TextStream
Dim txsFileTo As TextStream
Dim strNewFile As String
Dim strTextLine As String
Dim arrPath() As String

On Error GoTo ErrorHandler

strNewFile = Mid$(strFile, 1, Len(strFile) - 3) & "tmp"
Set fs = New Scripting.FileSystemObject
Set txsFileFrom = fs.OpenTextFile(strFile, ForReading)
Set txsFileTo = fs.CreateTextFile(strNewFile, True)

With txsFileFrom
Do While Not .AtEndOfStream
strTextLine = .ReadLine
If Left$(strTextLine, 15) = strSearchString Then
arrPath() = Split(strTextLine, "\")
strTextLine = Left$(arrPath(UBound(arrPath)), 8) & Right$(arrPath(UBound(arrPath)), 4)
End If
txsFileTo.WriteLine strTextLine
Loop
.Close
End With
txsFileTo.Close
Set txsFileTo = Nothing
Set txsFileFrom = Nothing
fs.CopyFile strNewFile, strFile, True
fs.DeleteFile strNewFile
Set fs = Nothing
ExitHere:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " " & Err.Description
Select Case Err.Number
'case
End Select
end sub

 

[willir]

There we go again with FSO. Very cool tool.

 

[EBGreen]

You could run this changing the name of test.txt to be the name of the file to remove the returns from:

Option Explicit
Dim oFSO, oFileIn, strLine, oFileOut, nMod, i

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFileIn = oFSO.OpenTextFile("Test.txt")
Set oFileOut = oFSO.CreateTextFile("Out.Txt", True)
i = 1
While oFileIn.AtEndOfStream <> True
	
	nMod = i Mod 2
	If nMod = 0 Then
		strLine = strLine & oFileIn.ReadLine
		i = i + 1
		oFileOut.WriteLine strLine
		strLine = ""
	Else
	 	strLine = oFileIn.ReadLine
	 	i = i + 1
	End If
Wend

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[EZEason]

Let me add a twist to this. The .txt file is on a CD. When I try copy to .txt to my HD, I get a "Data_error: cyclic redundancy data" and the copy stops.

What doesn't kill you makes you stronger.

 

[EBGreen]

CRC errors usually mean that either you need a new CDROM drive, or else the CD itself is scratched.

[blue]"Well, once again my friend, we find that science is a two headed beast. One head is nice, it gives us aspirin and other modern conveniences,...but the other head of science is BAD! Oh, beware the other head of science, Arthur; it bites!!" - The Tick[/blue]

 

[willir]

EZEason

Actually CRC means trouble...
If the file has a CRC number, it means that the a "system" was used to go through the file bit-by-bit and count in binary. The end result is that a number is created.

00 + 00 + 00 -> CRC number of 00
01 + 01 + 01 -> CRC number of 01
01 + 00 + 01 -> CRC number of 00

If the CRC number for the existing file does not match the expected CRC, it means you are mostly likely missing data.

A scrath will usually mean a time out, a crash or a CRC error.
A failing drive could mean anything from eratic behaviour to not being able to read the CD.

CRC error could mean the data is bad on the CD for whatever reason, and could suggest the original source was messed too.

Richard

 

[EZEason]

I'm also getting a error on Runtime Error :
Method "ReadLine" of object "ITextStream" failed.

What does this mean???

What doesn't kill you makes you stronger.

 

[willir]

EZEason

If you are getting CRC errors, it is not surprizing that you have "Read" errors accessing the file.

You seem to have a corrupted file. If possible, get another copy it.

 

[Craig0201]

http://www.datawatch.com

Look up the Monarch software.

Craig