Export to a flat file

[Paul Hammond]

An outside vendor has requested a fixed width file that we have provided them
carefully matching the requested field widths etc. They have come back to
us and asked us to remove all the cr/lf s. I found one previous post on
this subject that to me would be difficult to implement

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

Is there any less code intense way of going about this?

Thanks

Paul Hammond
Richmond VA

 

[Klatuu]

I don't think I would call that code intensive. It looks like a very elegant
solution to me.

 

[Paul Hammond]

Ah, maybe so, but there are myriad users out there that don't have advanced
or even basic coding skills and they are out of luck. Given a little extra
time I could adapt this code and use it, but I'm asking on behalf of someone
who can't.

It's surprising how few people there are in many large companies that have
even modest Access and VBA skills.

Paul

 

[John Nurick]

Hi Paul,

How about exporting the file the way Access likes to, and then filtering
out the CRLFs?

Using Perl you can do it from the command line. This takes the file
xxx.txt and removes the line breaks, leaving the original as
xxx.txt.bak:

perl -i.bak -pe "chomp" xxx.txt

In VBScript it takes a bit of code but the users can just drag and drop
the file to be processed onto the icon for the script file:

'START OF VBScript------------------------------------------
'DumpCRLF.vbs
'Script to remove CRLF linebreaks from a text file (e.g. to convert
'a fixed-width file exported from Access to a file of records.

Option Explicit
Const BAK_EXT = ".bak"
Dim fso 'FileSystemObject
Dim fF 'File
Dim fIn, fOut 'Textstreams
Dim strL 'String


If WScript.Arguments.Count = 1 Then
Set fso = CreateObject("Scripting.FileSystemObject")
Set fF = fso.GetFile(WScript.Arguments(0))

'If backup file aready exists, delete it
If fso.FileExists(fF.Path & BAK_EXT) Then
fso.DeleteFile fF.Path & BAK_EXT
End If
fF.Name = fF.Name & BAK_EXT
Set fIn = fF.OpenAsTextStream(1) 'ForReading
Set fOut = fso.CreateTextFile(WScript.Arguments(0))

Do 'Read line by line
strL = fIn.ReadLine
fOut.Write strL ' normally one would append a vbCRLF for the
linebreak
Loop Until fIn.AtEndOfStream

'Tidy up
fIn.Close
fOut.Close

Else
MsgBox "Drag a file onto the icon to remove all linebreaks. " _
& vbcrlf & "Original file will be renamed with " _
& ".bak extension"
End If
'END OF VBScript-----------------------------------------

 

[Paul Hammond]

This looks promising. Initial testing hasn't been worked precisely.
I'm off till next week. Perhaps I can get back to you then.

Paul
--

 

[Paul Hammond]

I stand corrected. Not sure what happened the first time, but the second
attemp worked like a charm. We will give this a go. Thank you for this
elegant and easy solution.

Paul
--