Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I'm using the Informix DBEXPORT to dump my database however it uses | as its default delimiter.
I know I can specify the delimiter I'd like to use using the DBDELIMITER environment variable but how do I set it to use tab
I tried using \t but that didn't seem to work
(It's running on a CentOS 5 Server btw)
UPDATE
checking through the documentation it says that you can specify tab using CTRL-I but when I set it to CTRL-I it comes out in the unl file as ^@
In theory, you would need to specify an actual tab, possibly using Bash's ANSI C quoting:
$ DBDELIMITER=$'\t' dbexport stores
Invalid delimiter; Don't use '\\', SPACE or HEX or Multibyte chars.
$
Unfortunately, that looks like you're not allowed to use tab either. (Test: Informix 12.10.FC5 on Mac OS X 10.11.6.)
Incidentally, I tried some alternatives: $'\a' (alert, aka Control-G) and $'\b' (backspace, aka Control-H) are both accepted, but none of $'\f' (formfeed, aka Control-L), $'\v' (vertical tab, aka Control-K), $'\r' (carriage return, aka Control-M) of $'\n' (newline, aka Control-J) are allowed. Any other control character from $'\0' through $'\037' (octal numbers, all except 9-13 decimal, 011-015 octal) seems to be OK (and NUL or 0 being OK did surprise me!). Do you have any tabs in your main data? If not, you could export with one of the acceptable alternative characters and then map that to tab. If you have tabs, life is harder because you'd need to protect any tabs that are in a data field with a backslash.
DB-Access allows DBDELIMITER=$'\t' without problem and produces an unload file with tab delimiters, so the problem is not in the shell code I'm using.
If you're certain you must have tabs, then
investigate Art Kagel's myexport which is part of his utils_ak package from the IIUG Software Repository, which also uses my SQLCMD, available from the same source. Using DBDELIMITER=$'\t' sqlunload -d stores -t customers (or sqlcmd -U -D $'\t' -d stores -t customers, or various other twists of the command line) produces output in a tab-delimited format. (On its own, SQLCMD does not handle a full export; it will cheerfully unload single tables, though. It does not yet handle NUL as a field separator or line separator — a design decision or artefact that I need to review.)
