Q5.3: How do I exclude a column?
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:
<![CDATA[
--- 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 <--- Replace <**> 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:
<![CDATA[
--- 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 <--- Change number items to 2
3 SYBCHAR 1 512 "\n" 4 text3 <--- Change 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:
<![CDATA[
--- bcpin.fmt
10.0
3
1 SYBINT4 0 4 "<**>" 1 counter
2 SYBCHAR 1 512 "<**>" 2 text2 <-- Change column id to 2
3 SYBCHAR 1 512 "\n" 3 text3 <-- Change column id to 3
---
]]>