Problem to use the 'bcp in' command on unix.

starskynhutch
starskynhutch used Ask the Experts™
on
Hi.
Im having problems to use the 'bcp in' command on unix. I need to load a file with the following format:

#26/08/02#0001000010#000#000#EUR#0.000000#0.000000#
#26/08/02#0001000010#000#001#EUR#0.000000#0.000000#

using following generic command:

bcp ${dbase}..{tab_name}in /file.txt -c -e file.err -t'#' -S${server} -U${user} -P${password}

But bcp cant handle the first and last '#' delimiters, or the dd/mm/yy format. Is there any way to do this within the bcp command, or do i have to reformat the file first, before using 'bcp in' - reformatting first is time consuming as theres over 100,000 lines in the file..

Any help is appreciated!
Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
You can use a Unix command, like "cut" to cut the file or "awk" program to format the file correctly.
Commented:
You can use -r option to define "#" as row terminator (just add -r"#" ) to ignore last "#"

However, you need to create a format file for the first one. You can define all field and row delimiters in format file. To ignore the first character, you just need to define a dummy field with char(1) datatype and no field delimiter as the first field. You can write "ignore" as table column name.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial