在将bcp与格式文件一起使用时如何指定行定界符 [英] how to specify row delimiter when using bcp with a format file

问题描述

我正在使用bcp实用程序将数据导入到Solaris系统上的Sybase数据库中

I am using bcp utility to import data into Sybase database on Solaris system

由于bcp文件是由另一个xml解析脚本生成的，因此我无法对其进行控制以使其按所需顺序生成字段.现在，bcp文件中字段的顺序与数据库表中的顺序有所不同.

Since the bcp file is generated by another xml parsing script, I have no control on that to make it generate the fields in the order I want. And now the the order of fields in the bcp file is a little different with the order in the database table.

我想使用bcp工具的格式文件来控制加载到数据库中的字段的顺序，因此我有以下示例bcp文件，并相应地构造了一个格式文件:

I want to use a format file for bcp tool to control the order of fields loaded into the database, so I have the sample bcp file as below and constructed a format file accordingly:

bcp文件:

603289|Aug 20 2011 12:00AM|YYY aaa OVD|KLYYP8
603284|Aug 22 2011 12:00AM|XXX bbb OVD|KLPK06

格式文件:

10.0
4
1  SYBCHAR   0  12   "|" 3 ver
2  SYBCHAR   0  26   "|" 2 first_dt
3  SYBCHAR   0  60   "|" 4 name1
4  SYBCHAR   0  10   "|" 1 name2

尽管我仍然遇到以下错误:

Although I am stuck on the following error:

$bcp my_db..my_tbl in test.bcp -e error -f format.fmt -r\\n -S Sever -U user -P pw

Starting copy...
CSLIB Message:  - L0/O0/S0/N24/1/0:
cs_convert: cslib user api layer: common library error: The conversion/operation was stopped due to a syntax error in the source field.
Unexpected EOF encountered in BCP data-file.
bcp copy in partially failed

1 rows copied.

我怀疑错误的原因是bcp实用程序无法识别为'\ n'字符的行定界符(我已经使用od -c test.bcp进行了检查)，尽管命令中的-r\\n选项似乎不起作用，应将行定界符指定为"\ n"字符.

I suspect the cause for the error is the bcp utility can't recognize the row delimiter which is '\n' character (I have used od -c test.bcp to check this), although the -r\\n option in the command seems doesn't work, which should specify the row delimiter as '\n' character.

有人知道吗?

我对格式文件进行了更改，现在它可以正常工作，我将最后一个字段的字段定界符从'|'更改了更改为"\ n"，如下所示:

I did a change on the format file and it works fine now, I changed the field delimiter for the last field from '|' to '\n' as below:

新格式文件:

10.0
4
1  SYBCHAR   0  12   "|" 3 ver
2  SYBCHAR   0  26   "|" 2 first_dt
3  SYBCHAR   0  60   "|" 4 name1
4  SYBCHAR   0  10   "\n" 1 name2

正如Michael在评论中提到的那样，使用格式文件非常棘手.使用原始格式文件时，我尝试添加"|"在每个记录的末尾，尽管文件未正确处理.

As Michael mentioned in comments, it's tricky to use format files. When using the original format file I have tried to add '|' at end of each records, although the file were not processed correctly.

虽然我不确定bcp实用程序将什么用作行定界符，但现在可以正确处理文件了

Now the file can be processed correctly, although I am not sure what is used as row delimiter by bcp utility

推荐答案

Sybase格式文件使用起来很棘手，我还没有让它们重新排序列.如果没有格式文件，并且由于无法控制输入文件的输入，您可以选择以下选项:

Sybase format files can be tricky to use, and I have yet to get them to work to redorder columns. Without a format file, and since you can not control the input file you're given, your options are:

1. 使用正确顺序的列重建表
2. 将临时表或工作表用于bcp，并将数据从此处移到生产表中.
3. 如果出于某种原因存在逻辑，要求表保持其现有列顺序(这不太可能)，则可以使用正确的列顺序重建表，然后创建具有以下内容的表视图:按旧顺序排列的列.

1. Rebuild the table with the columns in the correct order
2. Use a temp table, or work table for the bcp in, and move the data into your production table from there.
3. If for some reason there is logic in place the requires the table to keep it's existing column order (which should be unlikely), you can rebuild the table with the correct column order, then create a view of the table that has the columns in the old order.

鉴于您的数据格式，下面的bcp命令行应该可以使用.

Given your data format, the following bcp command line should work.

bcp DBNAME..TABLENAME in MY_FILE.bcp -U username -P password -S SERVERNAME -c -t\| -r\\n

这篇关于在将bcp与格式文件一起使用时如何指定行定界符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！