February 11, 2004 at 3:53 pm
I have some data that comes with quoted fields. Eg. "name","phone". I havent found how to insert the data without quotes. What am I missing?
February 12, 2004 at 2:36 am
Have you tried to play with quoted identifier stuff (to use both " and [], instead of just [] and treating " as plain character)?
Or, the simply solution, try to replace " with nothing or blanks (before the insert or after - in which case you should use a cursor to check each record and remove " if it was inserted as a plain character)
***
February 12, 2004 at 3:47 am
I ran into this problem just a few days ago, and found the answer. You need to specify quotes in the 5th column of the BCP format file. Here is an example of my file:
8.0
2
1 SQLCHAR 0 0 "\"" 0 Quote SQL_LATIN1_GENERAL_CP1_CI_AS
2 SQLCHAR 0 0 "\"\r\n" 1 Col1 SQL_LATIN1_GENERAL_CP1_CI_AS
(note that in this reply, I am only using double-quotes, never a single quote)
This will load in one column of quoted data. You need the first line in the BCP format file to get rid of the very first quote in the source file, and the 2nd line loads the data between the quotes.
For line 1, the column delimeter is "\"" which looks confusing. The BCP format file requires the column delimeter itself to be surrponded by quotes (for example, a comma is represented as ",") so what we're really looking at is \" which represents to BCP one quote. The effect of this row is for BCP to skip over the very first quote in the source file.
For line 2, we need to specify the delimiter of the first column. This will depend on what columns follow. If this is the last column in the file, then we can use "\"\r\n" which means that the column ends with a quote, a carrage-return and a newline.
Here is an example that I have tested just now and works.
create table BCPtst (QuotedStr1 varchar(10), QuotedStr2 varchar(10), UnquotedStr1 varchar(10), QuotedStr3 varchar(10))
BULK INSERT BCPtst from 'c:\BCPtstdata.txt' with (formatfile='c:\BCPfmt.txt')
The BCPfmt.txt file contains
8.0
5
1 SQLCHAR 0 0 "\"" 0 Quote SQL_LATIN1_GENERAL_CP1_CI_AS
2 SQLCHAR 0 0 "\",\"" 1 QuotedStr1 SQL_LATIN1_GENERAL_CP1_CI_AS
3 SQLCHAR 0 0 "\"," 2 QuotedStr2 SQL_LATIN1_GENERAL_CP1_CI_AS
4 SQLCHAR 0 0 ",\"" 3 Unquotedstr1 SQL_LATIN1_GENERAL_CP1_CI_AS
5 SQLCHAR 0 0 "\"\r\n" 4 Quotedstr3 SQL_LATIN1_GENERAL_CP1_CI_AS
And the BCPtstdata.txt file is
"Str1A","Str2A",NoQuotesA,"Str3A"
"Str1B","Str2B",NoQuotesB,"Str3B"
Sorry its so long-winded. Hope this helps. Let me know how you get on.
-Steve
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply