FAQ - editing Informix unload files

Floyd Wellershaus wrote: > Hi, > This is on ids10.0 and aix5.3 > > We're trying to convert some data over to another database. When > unloading certain fields that have encrypted data inserted into them ( > not informix encryption ), we noticed that the data is getting converted > a bit. > For instance, an \\F in the character string in the database, comes out > as a \\\\F in the unload file. > The delimiter doesn't matter. It is still putting some extra escape > quotes in certain places. > > We can't use the HP load internal format, because the other database > wouldn't know what to do with it. > > Are there any ideas as to how to prevent this, or why it happens ? > > Thank you, > Floyd Several posts recently have been concerned with the formats of Informix unload files not being quite what they wanted. As a one-off the required changes can be made with vi. If the file is too big for vi or a scripted solution is required sed can be used instead. The general format of the vi command is: :%s/this/that/g : - puts vi into a mode to accept ex commands % - part of the ex command. Tells the editor to apply the command to all lines in the file, not just the current line s - substitute the string between the first and second slash with the string between the second and third g - global - apply to all occurrences of the first string; without it only the first occurrence on each line would be substituted Lines without the target string will be unaffected. The usual regular expression tricks apply so that, for instance :%s/^[Aa]/1/ would replace either A or a at the start of a line by 1 which means that special characters need to be escaped with a backslash so that :%s/\\[/{/g replaces all square brackets by curlies. This means that the backslash itself needs to be escapes so in Floyd's case the command :%S/\\\\\\\\/\\\\/g will substitute a single backslash for each pair. The other issue which has been raised recently is removal of trailing spaces before field delimiters without removing non-trailing spaces. The trick here is to substitute the combination of space and delimiter by the delimiter e.g. :%s/ |/|/g My general approach is to issue a series of commands to remove decreasing numbers of spaces. For instance if I expect no more than 15 trailing spaces the first command would be: :%s/ |/|/g For those reading in proportional spaced fonts that was 8 spaces. The next command would be for 4 spaces, then 2, then 1. A similar approach can be used to remove leading spaces. Having worked out the series of commands needed to hack a sample file they can be assembled into a file and handed to sed as a script using the -f option of sed. As sed doesn't need to be put into command mode and as it applies each command in turn to each line neither the : nor the % are needed so a file to apply the fix that Floyd needs, remove up to 3 trailing spaces, remove a single space at the start of a line and add another delimiter at the end of each line would be: s/\\\\\\\\/\\\\/g s/ |/|/g s/ |/|/g s/^ //g s/$/|/g One advantage of sed is that it can be used as a filter. I've used it when converting fixed width files to Informix load files - a C program adds the delimiters as required and sed takes its standard out and removes the trailing spaces. Another is that it can handle arbitrarily large files whilst vi may well fail on large files. -- Ian Hotmail is for spammers. Real mail address is igoddard at nildram co uk