Embedded carriage return in PC data
Jeff Hoffman
wonicon@optushome.com.au
Wed, 20 Mar 2002 08:37:17 +1100
Thanks for your responses
however:
I have my own preferred editor WinVi.
We get the access DB twice a year from a Govt dept, we have no control over
the data entry, we extract 7 files, the files contain about 5 potentially
very long fields (containing 0 to >200 chars) that are entered free format,
the fields can contain 0 or *more* line breaks (similar to using ctrl/P
when entering a field using quick).
The extracted files with variable lenght extract comes to 3mb if we extract
fixed format (if we could) the files come out to over 15 mb.
We do not have people with the skills in VBA and are reluctant to set
something up that will require tweeking as the file layouts change, if we
use contractors we would have to find somebody twice a year to do changes
on the PC extract and hope that they understand the way the the previous
contractor did the job. We have enough trouble just doing the PH stuff.
At the moment we have the skill only to extract the data from Access,
transfer it to VMS and run the upload programs.
An analyst/programmer is using msword to edit the file, there are only
about 20 records out of over 5,000 with the problem, it is very easy to
miss removing a line break. The process is becomming iterative, the A/p
finds as many line breaks as possible, transferrs the files and tries the
upload process, it crashes and the process starts again. Frustrating and
time consuming.
By having somebody edit the files it leave the files wide open to mistakes
and lost data that we probably would not detect.
I would prefer to automate the load process as much as possible, hence the
request for any ideas.
Thanks
Jeff
At 05:10 PM 19-03-02 +0000, you wrote:
>Despite the risk of merciless flaming here goes.
>
>Since the data is in Access, why not use VBA to write it to a text file?
>VBA can read the data structure and can be made to cope automatically with
decimal places or embedded line breaks: declare dates as YYYYMMDD, numerics
as freeform, and remember to include a byte or two on the end for the line
break.
>You can also use it to write a small quiz to create the data structure, or
even a batch file to do the whole import for you.
>Saves all that fiddling about uploading and downloading.
>
>BTW, making variable length text files fixed length on UNIX is a one line
AWK script and not hard at all.
>
>Regards,
>Stephen Hulbert.
>Senior Analyst/Programmer.
>Software Division. Littlejohn Frazer.
>Direct: +44 (20) 7369 4501. Fax: +44 (20) 7987 9707
>mailto:shulbert@littlejohnfrazer.com
>
>
>
> -----Original Message-----
>From: BEdis@usbnc.org [mailto:BEdis@usbnc.org] Sent: Tuesday, March 19,
2002 4:44 PM
>To: powerh-l@lists.swau.edu
>Subject: RE: Embedded carriage return in PC data
>
>I too think TextPad is a great text editor!
>
>Blue
>
>-----Original Message-----
>From: Hoppe, Eric EP [mailto:Eric.Hoppe@steelscape-inc.com]
>Sent: Tuesday, March 19, 2002 10:15 AM
>To: 'Jeff Hoffman'; powerh-l@lists.swau.edu
>Subject: RE: Embedded carriage return in PC data
>
>
>Jeff-
>
>I am not sure exactly if this is what you are looking for... assuming this
is a 'one-off' and you can massage the file, I swear by a
>fantastic text editor called Textpad to manipulate files (textpad.com). It
>has been able to do anything and everything I have ever asked.
>
>---ehops
>eric hoppe
>
>ps I am not associated with the Textpad company at all, I just TRULY believe
>in the product... and it's only about $30US for a license.
>
>-----Original Message-----
>From: Jeff Hoffman [mailto:wonicon@optushome.com.au]
>Sent: Monday, March 18, 2002 12:51 PM
>To: powerh-l@lists.swau.edu
>Subject: Embedded carriage return in PC data
>
>
>G'Day
>
>Sorry for this non PH question butttttt....
>
>I am using VMS variable length file to read data from an Access data base.
>
>CR's are embedded in the data, they are being treated as end of record.
>
>Has anybody got some advice (politely please) of a way to detect and remove
>these before I access this file with PH?
>
>Thanks
>+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
>+
>+ Jeff Hoffman + e-mail: wonicon@optushome.com.au
>+
>+ Director +
>+
>+ WoniCon Pty Ltd (ABN 93 083 502 630)+ ,-_|\
>+
>+ 21 Prices Circuit + voice: +61 2 9542 1527 / \
>+
>+ Woronora NSW 2232 + \_,-._*
>+
>+ AUSTRALIA + mobile: 040 790 3929 v
>+
>+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
>+
>
>= = = = = = = = = = = = = = = = = = = = = = = = = = = =
>Mailing list: powerh-l@lists.swau.edu
>Subscribe: "subscribe" in message body to powerh-l-request@lists.swau.edu
>Unsubscribe: "unsubscribe" in message body to
>powerh-l-request@lists.swau.edu
>http://lists.swau.edu/mailman/listinfo/powerh-l
>This list is closed, thus to post to the list you must be a subscriber.
>
>= = = = = = = = = = = = = = = = = = = = = = = = = = = =
>Mailing list: powerh-l@lists.swau.edu
>Subscribe: "subscribe" in message body to powerh-l-request@lists.swau.edu
>Unsubscribe: "unsubscribe" in message body to powerh-l-request@lists.swau.edu
>http://lists.swau.edu/mailman/listinfo/powerh-l
>This list is closed, thus to post to the list you must be a subscriber.
>
>
>
>
>The information contained in this communication is confidential and may
>be legally privileged. It is intended solely for the use of the
>individual or entity to whom it is addressed and others authorised to
>receive it. If you are not the intended recipient you are hereby
>notified that any disclosure, copying, distribution or taking of any
>action in reliance on the contents of this information is strictly
>prohibited and may be unlawful.
>
>Littlejohn Frazer reserves the right to monitor the content of any
>message sent to or from littlejohnfrazer.com and its associate domains,
>fmi-litjon.co.uk and litjon.co.uk
>
>A list of partners may be inspected at 1 Park Place, Canary Wharf,
>London, E14 4HJ
>
>Registered to carry on audit work by the Institute of Chartered
>Accountants in England & Wales, and authorised by the Financial
>Services Authority to provide financial services
>
>= = = = = = = = = = = = = = = = = = = = = = = = = = = =
>Mailing list: powerh-l@lists.swau.edu
>Subscribe: "subscribe" in message body to powerh-l-request@lists.swau.edu
>Unsubscribe: "unsubscribe" in message body to powerh-l-request@lists.swau.edu
>http://lists.swau.edu/mailman/listinfo/powerh-l
>This list is closed, thus to post to the list you must be a subscriber.
>
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
+ Jeff Hoffman + e-mail: wonicon@optushome.com.au +
+ Director + +
+ WoniCon Pty Ltd (ABN 93 083 502 630)+ ,-_|\ +
+ 21 Prices Circuit + voice: +61 2 9542 1527 / \ +
+ Woronora NSW 2232 + \_,-._* +
+ AUSTRALIA + mobile: 040 790 3929 v +
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
+