June 1, 2010 at 8:29 am
Hi,
I have a problem creating unicode file from BCP, I have to create fixed width file for which I have to use format file and then I also need end file to be unicode.
I tried many different options but could not work.
I used -w switch but then it overrides -f and don't use format file....
Thanks for helping me out...
June 1, 2010 at 9:59 am
Use SQLNCHAR instead of SQLCHAR in the format file.
June 1, 2010 at 1:15 pm
Thanks!
I tried already SQLNCHAR then it puts spaces in between characters but the file still is not unicode..
Regards
June 2, 2010 at 12:13 am
How about making it clear exactly what you want to see?
Some sample data and expected output would probably make this a lot easier...
June 2, 2010 at 1:03 am
Hi,,
This is the command which I am using...with -C switch..which creates
EXEC master..xp_cmdshell 'bcp "select LastName,FirstName,FullName,CareOf,StreetAddress,City,ZipCode,Country from tstDB..tmpperson" queryout F:\Temp\SPAR.dat -f F:\Temp\FormatFileName.txt -C RAW -T'
and this is the format file
10.0
8
1 SQLCHAR 0 70 "" 1 LastName Finnish_Swedish_CI_AS
2 SQLCHAR 0 70 "" 2 FirstName Finnish_Swedish_CI_AS
3 SQLCHAR 0 200 "" 3 FullName
4 SQLCHAR 0 70 "" 4 CareOf
5 SQLCHAR 0 70 "" 5 StreetAddress
6 SQLCHAR 0 60 "" 6 City Finnish_Swedish_CI_AS
7 SQLCHAR 0 10 "" 7 ZipCode
8 SQLCHAR 0 40 "\r" 8 Country Finnish_Swedish_CI_AS
---------------------
but the thing is when I changed SQLCHAR to SQLNCHAR then it creates problem...it puts the name like 'A N A M E' because of nvarchar I think.. but the encoding of file is still ANSI its not UNICODE...and if I use the switch -w, which creates the UNICODE file but then it ignores the format file....
Thanks!
June 2, 2010 at 4:12 am
Ok, so still no sample data (or a source table definition). No matter, I will try to answer as best I can given the information provided.
The easiest way to achieve a fixed-length Unicode-encoded file is to use the -w option together with the queryout option, using CONVERT to make the source data appear as fixed-length Unicode data. For example:
bcp "SELECT CONVERT(NCHAR(70), Name) AS Name FROM Sandpit.dbo.UnicodeData" queryout FixedLengthUnicode.bcp -w -S .\SQL2008 -T
...would produce a Unicode-encoded file with a single column of data with a fixed length of 70 Unicode characters. The example uses the following sample data, created in my test database (called 'Sandpit'):
CREATE TABLE dbo.UnicodeData
(
Name NVARCHAR(20) NOT NULL,
);
INSERT dbo.UnicodeData (Name) VALUES (N'Hammarskjöld');
INSERT dbo.UnicodeData (Name) VALUES (N'Hämäläinen');
INSERT dbo.UnicodeData (Name) VALUES (N'??????');
You should not be using the code page option -C if your intention is to produce a file in Unicode format.
In your specific case, the following might be what you need:
EXECUTE master..xp_cmdshell 'bcp "SELECT LastName = CONVERT(NCHAR(70), LastName), FirstName = CONVERT(NCHAR(70), FirstName), FullName = CONVERT(NCHAR(200), FullName), CareOf = CONVERT(NCHAR(70), CareOf), StreetAddress = CONVERT(NCHAR(70), StreetAddress), City = CONVERT(NCHAR(60), City), ZipCode = CONVERT(NCHAR(10), ZipCode), Country = CONVERT(NCHAR(40), Country) FROM tstDB..tmpperson;" queryout F:\Temp\SPAR.dat -t -w -T';
You could also create a view, and bcp out directly from that:
CREATE VIEW dbo.FixedLengthView
AS
SELECT LastName = CONVERT(NCHAR(70), LastName),
FirstName = CONVERT(NCHAR(70), FirstName),
FullName = CONVERT(NCHAR(200), FullName),
CareOf = CONVERT(NCHAR(70), CareOf),
StreetAddress = CONVERT(NCHAR(70), StreetAddress),
City = CONVERT(NCHAR(60), City),
ZipCode = CONVERT(NCHAR(10), ZipCode),
Country = CONVERT(NCHAR(40), Country)
FROM tstDB..tmpperson;
June 2, 2010 at 6:23 am
thanks very much,, it did solve the problem,,,I was actually trying only format file to create fixed-width....
Thankyou again....!
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy