Import *.txt file with 'new line' error

[DLTaylor]

I have a DTS that imports a txt file from an FTP location.
The text file has started to arrive with errors where new lines \ records are positioning themselves correctly.
(It is a little lie the data is wrapping itself in the original extract)

I can identify new lines \ records within the text file as they arrive with the prefix;

"CC00* or "CC01*

Is there a script I could run against the *.txt file prior to import that would ensure a new line began where "CC00 or "CC01 existed in the *.txt file.

If I could do this then the import work every time.

 

[AlexCuse]

I have this script from Access that could probably be modified to suit your needs:

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)        'Specify Folder
Set fc = f.Files

For Each f1 In fc   


If Left(f1.Name, 14) = "FILE_NAME" Then
Set ots = f1.OpenAsTextStream(1) '1=ForReading
s2 = Replace(Replace(ots.ReadAll, vbCr, ""), vbLf, vbCrLf)
ots.Close
Set ots = f1.OpenAsTextStream(2) '2=ForWriting
ots.Write s2
ots.Close

I built this into a routine that grabbed files off the FTP site, renamed them for a DTS package, and returned the count of rows in each file. Some of the .DAT files we were receiving were coming in with lines delimited by a linefeed character and this was wreaking havoc with the DTS.

You will need to identify what line delimiters are used (if any) and replace them. What my script is doing is:

1.) removing any stray Carriage Return characters
2.) replacing any LF's with CrLf's

What you would want to do is replace your CC00's and CC01's with VbCrLF & CC**, like so:

s2 = Replace("CC00", vbCrLf & "CC00")

Just make sure this is not creating empty lines in your file. If you are getting text wrap then it probably won't.

You also might need to bring your file down to a local directory before doing this step.

Hope it helps,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[DLTaylor]

Thank you very much for you help.
I will have a go today using what you have provided and let you know how i'm getting on.

Thanks again,
Dan