在Windows中使用命令dos格式化文件的bcp [英] bcp with format file using command dos in windows

问题描述

我正在尝试使用格式文件将数据从文件导入sql server表中.

I'am trying to import data into sql server table from a file using a format file.

实际上我有2个数据库:生产数据库和本地数据库

In fact I have 2 databases: a production database and a local database

我想在本地数据库中插入生产数据库的表发送器的某些行.在这两个数据库中，表发件人既没有相同的列，也没有相同的列顺序.

I want to insert some row of the table shipper of the production database in the local one. The table shipper don't have neither the same columns nor the same order of column in the 2 databases.

这就是为什么我使用文件格式来进行bcp的原因.

That's why I used a file format to do my bcp.

我使用以下命令生成包含要插入到本地数据库中的行的文件

I generate file containing the rows I want to insert in my local database with the following commande

bcp "SELECT  shipper_id,Shipper_name FROM ProductionDatabase.dbo.shipper where shipper_id >5" queryout shipper.txt -c -T

有效！

然后使用以下命令生成具有本地表模式的格式文件

I generate then the format file with the schema of my local table with the following commande

bcp LocalDatabase.dbo.shipper nul -T -n -f shipper-n.fmt

有效！

不幸的是，当我尝试在本地表中插入文件数据时 使用以下命令:

Unfortunately when I tried to insert the file data in my local table with the following commande:

bcp LocalDatabase.dbo.shipper in shipper.txt -T -f shipper-n.fmt

它会产生以下错误(翻译为法文)

it generates the following error (translated from french)

任何人都可以知道问题出在哪里，如何解决这个问题.

Can anyone know what is the problem and how can I get arround it.

预先感谢

unexpected end of file encountered in the bcp data file 

推荐答案

您的格式文件与数据不匹配.您正在使用-c

Your format file does not match the data. You are exporting using text using -c

bcp "SELECT  shipper_id,Shipper_name FROM ProductionDatabase.dbo.shipper where shipper_id >5" queryout shipper.txt -c -T

但是您的格式文件是使用-n

But your format file is made for native (binary) data using -n

bcp LocalDatabase.dbo.shipper nul -T -n -f shipper-n.fmt

既可以将其导出为本机(我的建议)，也可以将两者都导出为文本.为防止出现此错误，只需同时导出数据文件和格式文件，只需在导出中添加-f shipper.fmt

Either export both as native (my recommendation), or both as text. To prevent this error, export the data file and the format file at the same time, simply add -f shipper.fmt to your export

文本版本:

bcp "SELECT  shipper_id,Shipper_name FROM ProductionDatabase.dbo.shipper where shipper_id >5" queryout shipper.txt -c -T -f shipper.fmt

或

本地版本:

bcp "SELECT  shipper_id,Shipper_name FROM ProductionDatabase.dbo.shipper where shipper_id >5" queryout shipper.txt -n -T -f shipper.fmt

PS.由于您可能会遇到记录或行分隔符存在于数据中的情况，因此应选择数据中不存在的字符序列作为分隔符，例如字段-t"\t|\t"(Tab-Pipe-Tab)和-r"\t|\n"的分隔符(Tab-Pipe-Newline)行.如果将format语句与export结合使用，则数据和格式文件将匹配，并且可以在单个命令行上更改分隔符.

PS. Since you can run into scenarios where your record or row delimiters exist in the data you should pick a character sequence that does not exist in your data as a separator for instance -t"\t|\t" (Tab-Pipe-Tab) for fields and -r"\t|\n" (Tab-Pipe-Newline) for rows. If you combine the format statement with the export the data and the format file will match and you have the freedom to change the separators on a single command line.

在命令行的-n或-c之后指定分隔符

Specify separators after the -n or -c on the command line

这篇关于在Windows中使用命令dos格式化文件的bcp的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！