Replacing all but last Hex characters in a text line

Shell Programming and Scripting
1

I must remove hex characters 0A and 0D from several fields within an MS Access Table. Since I don't think it can be done in Access, I am trying here.
I am exporting a Table from Access (must be fixed length fields, I think, for my idea to work here) into a text format.
I then want to run a script that will replace all instances of hex 0A from the line .... except for the very last instance ... with some other character ... say a hex 7C
Example: If I have 20 fields within Access for a total export line length of 2000 characters, I want to replace all the hex 0A characters (which is a CR/LF character, I believe) from positions 1-1999 with hex 7C (which is shift-\).
Anybody have any creative ideas? I would very much appreciate it.
Brad

:confused:

2

You could use the tr command :-

tr '\012' '\174' < filein | tr '\015' '\012' | unix2dos > fileout

Explanation of above code:-

  • use tr on the file to convert all 0A to 7C
  • pipe result into tr to convert all 0D to 0A
  • pipe result into unix2dos to expand all 0A to 0A0D

Notes:-

  • Input lines can be of any length.
  • Transfer files in binary mode to prevent automatic conversion.
3

This is just an FYI to help anybody that might be in a similar situation. I did not have to write or execute any UNIX script to remove these troublesome CR/LF hex characters from the text in several fields in one of my MS Access tables.
First of all, the MS Access Table in question is actually linked via an ODBC connection to an MS SQL Server database.
After establishing the link and being able to view the data, I export the data using the tilde (~) as the delimiter and using the quote (") to surround text fields .... and save the file as a type .txt or.csv file. I made sure that the tilde did not exist in the Table prior to selecting it as my delimiter.
Once the export file was created, I used an awesome tool which I found and downloaded from the internet to edit out the CR/LF characters.
The hex editor tool used is called XVI32. Link provided here: Freeware Hex Editor XVI32
Since MS Access also puts out CR/LF characters as it exports the data, I had to find some unique sequence of characters either from the end of the record ... or the beginning of the record that is on either side of the hex 0D 0A that MS Access puts at the end of each exported record. I then performed a hex replace of the sequence of characters with some other unique sequence of characters.
For example: the sequence of characters I found to be unique were: 73 65 71 75 65 6E 63 65 0D 0A
The 0D 0A shown here was actually put there by Access.
The tool allowed me to change this sequence to: 73 65 71 75 65 6E 63 65 7C 7C
I replaced the 0D 0A with a 7C 7C.
The hex 7C character (shift-\) did not previously exist in the Table either.
After that completed, I am then able to delete (or replace) any other occurrence of 0D or 0A that exist in the file .... i.e. the occurrences that I want to remove/replace that exist in the free text fields of the record.
Now, once that completed, I then change back the sequence of characters from 73 65 71 75 65 6E 63 65 7C 7C to 73 65 71 75 65 6E 63 65 0D 0A.
I then save the file (with a new name ... just 2B safe). I now have a .txt file that MS Access will be able to re-import correctly ... or just link to ... as desired.