473,883 Members | 2,106 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

BCP - help with import/export using collation Chinese_PRC_CI_ AS


I've been trying to export/import a table from a DB with
Chinese_PRC_CI_ AS collation, and I can't seem to get it working at all
- the export seems rather simple, it is the import that is not working
at all. I am using MSSQL 2000:

The table (under a Chinese_PRC_CI_ AS collation DB) is as follows:
CREATE TABLE [dbo].[test_table] (
[id] [int] NOT NULL ,
[first_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_ AS NOT NULL

Then I insert a set of rows using "Enterprise Manager", and then
export the table contents using the following command:

BCP "select * from testdb.dbo.test _table" queryout test_table.txt -c -
t -S" 0" -U"sa" -P"mypassword "

(This is how I must to run the export - I can not change the way of
the statement is run - the reason is because I am using someone elses

No I try import the data back into the same table, test_table, but
first I take the following steps:

from iSQL: "DELETE FROM testdb.dbo.test _table"

Then I create a format file, test_table.fmt, which looks like this:

1 SQLINT 0 12 "" 1 id Chinese_PRC_CI_ AS
2 SQLNCHAR 0 100 "\r\n" 2 first_name Chinese_PRC_CI_ AS

So here is the problem:

When I import the data using the command

BCP "testdb.dbo.tes t_table" in "test_table.txt " -f"test_table.fm t" -
S" 0" -U"sa" -P"mypassword "

The rows get imported - but they are nothing like the data that I

test_table.txt contents:


Imported results:

12800 爀漀戀
13056 猀愀洀
13312 挀愀爀氀
3276543 瀀攀琀攀

I've tried changing the format file line's separator to be as
follows: "\n", "\n\0", "\r\0\n\0" - but the results did not differ
much from each other.

Any help is greatly appreciated.

Thank you,


May 3 '07 #1
1 5603
InvestorTrade (sh*****@gmail. com) writes:
Then I insert a set of rows using "Enterprise Manager", and then
export the table contents using the following command:

BCP "select * from testdb.dbo.test _table" queryout test_table.txt -c -
t -S" 0" -U"sa" -P"mypassword "
Then I create a format file, test_table.fmt, which looks like this:

1 SQLINT 0 12 "" 1 id Chinese_PRC_CI_ AS
2 SQLNCHAR 0 100 "\r\n" 2 first_name Chinese_PRC_CI_ AS
The format file does not match the command that generates the file.
The export command is for a tab-delimited text file, your format file
is for a binary file. A format file for a text file uses only
SQLCHAR for the data type (or SQLNCHAR if it's a Unicode file).

Thus the correct format file would be:

1 SQLCHAR 0 0 "\r\n" 1 id Chinese_PRC_CI_ AS
2 SQLCHAR 0 0 "\r\n" 2 first_name Chinese_PRC_CI_ AS

Although I suspect that you could simply use -c to import the file.
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
Books Online for SQL Server 2000 at
May 3 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: steve | last post by:
Hi, When I copy tables in a database from one server to another using enterprise manager, everything copies ok, except for field defaults. Has anyone seen this, and what is the solution? -- http://www.dbForumz.com/ This article was posted by author's request Articles individually checked for conformance to usenet standards Topic URL:...
by: Ivan Zuzak | last post by:
Hello. Sorry for crossposting, but i didn't have a better idea as to where to post the question. I'm using certificates to sign and encrypt SOAP messages, using WSE in .Net. I want to do the following: 1) Generate a certificate to the system store. 2) Export the certificate from 1) to a file with only the public key, and then import the file as a certificate into the system store, but on another computer. 3) Export the certificate from...
by: Alok | last post by:
Hi, I recently upsized my access database to an ADP project with SQL backend. However when I did it the import/export specs were not ported to the new database. Also in the ADP files I am unable to create new import/export specs. The option is greyed out. I need to use the specs with the TransferText function. How can I get around this issue? Alok
by: N. Graves | last post by:
Thanks for taking the time to read this note. I have a Access inventory collection tool that I have automated to build and Export to a CSV file. In the database I have several fields that are large text fields for comments. When I'm collecting data I have set the <Enter> button to move to next line instead of the default. We are trying to import this CSV data file in to a Remedy SQL server using the Remedy Import tool. This import...
by: Iavor Raytchev | last post by:
Hello, We a situation with a central database that contains the data that needs to be presented at N off-line terminals (N can be 5 000 can be 15 000). Each terminal presents unique data. The central database is used for data preparation. Then the data for each terminal is exported as separate SQL file. The terminals run the same application that is used to prepare the data - only in user mode. So what we do is - we export the full database...
by: phillip.s.powell | last post by:
We're looking at a GUI interface for our MySQL DB and I am interested in MySQL Administrator, however, one of our requirements is to be able to import/export databases. Is this possible or do I need to know else (e.g. Navicat)? Thanks Phil
by: Niklas | last post by:
Hi Before I start coding I need to be sure that nobody else has not done it yet and that I can use it. I need an import utility which import data from Excel to a database or some object in .NET. The data in Excel is not in structured columns but can exist everywhere in the workbook. For example if I am supposed to import a person and all his/her cars which exist in a workbook I want to be able to create an import protocol and specify...
by: Jobs | last post by:
Download the JAVA , .NET and SQL Server interview with answers Download the JAVA , .NET and SQL Server interview sheet and rate yourself. This will help you judge yourself are you really worth of attending interviews. If you own a company best way to judge if the candidate is worth of it. http://www.questpond.com/InterviewRatingSheet.zip
by: msansari | last post by:
I am planning to migrate my oracle database from 8.1.7 to 9i. I done the full database export using the following command. ---------------------------------------------------------------------------- set ORACLE_SID=pp01 set NLS_LANG=american_america.us7ascii exp db_export/createdmp file=f:\oracle\export\pp01_A.dmp,f:\oracle\export\pp01_B.dmp,f:\oracle\export\pp01_C.dmp,f:\oracle\export\pp01_D.dmp full=y grants=Y indexes=Y rows=y...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main usage, and What is the difference between ONU and Router. Lets take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.