bcp

5.1    How do I bcp null dates?
5.2    Can I use a named pipe to bcp/dump data out or in?
5.3    How do I exclude a column?

5.1: How do I bcp null dates?

As long as there is nothing between the field delimiters in your data, a null will be entered. If there's a space, the value will be Jan 1, 1900.

You can use sed(1) to squeeze blanks out of fields:

sed -e 's/|[ ]*|/||/g' old_file > new_file 

Back to top

5.2: Can I use a named pipe to bcp/dump data out or in?

System 10 and above.

If you would like to bcp copy from one table to a named pipe and compress:

1. %mknod bcp.pipe p
2. %compress sysobjects.Z &
3. %bcp master..sysobjects out bcp.pipe -c -U .. > bcp.pipe
4. Use ps(1) to determine when the compress finishes.

To bcp from my1db..dummy_table_1 to my2db..dummy_table_2:

1. %mknod bcp.pipe p
2. %bcp my2db..dummy_table_2 in bcp.pipe -c -U .. &

   To avoid confusion between the above bcp and the next, you may choose to either use a separate window or redirect the output to a file.

3. %bcp my1db..dummy_table_1 out bcp.pipe -c -U ..

Back to top

5.3: How do I exclude a column?

Open/Client 11.1.1

Create a view based on the table that you want to exclude a column from and then bcp out from the view.

Open/Client Versions Older Than 11.1.1

The documentation Utility programs for Unix describes the use of format files, including the field Server Column Order. Server Column Order must equal the colid of the column, or 0 if the host file field will not be loaded into any table column.

I don't know if anyone has got this feature to work. So, here is another way of removing the column. In your example, you want to remove the last column. I am going to include another example to remove the second column and include a fourth column. Why? Because it is harder. First example will deal with removing the last column.

Removing the Last Column

Edit your bcpout.fmt file and look for the changes I made below. Using the following bcpout.fmt file to dump the data:

--- bcpout.fmt
10.0
2 <------------------ Changed number of columns to BCP to two
1 SYBINT4 0 4 "<**>" 1 counter
2 SYBCHAR 1 512 "\n" 2 text1 <--- Replaced <**> with \n
3 SYBCHAR 1 512 "\n" 3 text2 <--- DELETE THIS LINE

Now recreate the table with the last column removed and use the same bcpout.fmt file to BCP back in the data.

Now let's try removing the second column out four columns on a table.

Removing the Second out of Four Columns

Edit the bcpout.fmt file and look for the changes I made below. Using the following bcpout.fmt file to dump the data:

--- bcpout.fmt
10.0
3 <------------------ Changed number of columns to BCP to three
1 SYBINT4 0 4   "<**>" 1 counter
2 SYBCHAR 1 512 "<**>" 2 text1 <--- DELETE THIS LINE
2 SYBCHAR 1 512 "<**>" 3 text2 <--- Changed number items to 2
3 SYBCHAR 1 512 "\n"   4 text3 <--- Changed number items to 3

Including the Fourth Column

Now copy the bcpout.fmt to bcpin.fmt, recreate table with col 2 removed, and edit bcpin.fmt file:

--- bcpin.fmt
10.0
3
1 SYBINT4 0 4   "<**>" 1 counter
2 SYBCHAR 1 512 "<**>" 2 text2 <-- Changed column id to 2
3 SYBCHAR 1 512 "\n"   3 text3 <-- Changed column id to 3

Back to top