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.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
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...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
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;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply