January 7, 2015 at 2:57 pm
I am bulk loading data from a comma delimited file, using BCP. Where all of the fields have quotes.
Sample data
"test me","Rick Smith","11111111","Rick","Smith","Phone1","(559) 298-6411","1","01/06/2015 3pm","answered","","Edison"
"test me","Dave Jones","11111111","Dave","Jones","Phone1","(559) 298-6411","1","01/06/2015 3pm","answered","","Edison"
"test me","Jim Dandy","11111111","Jim","Dandy","Phone1","(559) 298-6411","1","01/06/2015 3pm","answered","","Edison"
"test me","Jack Russell","11111111","Jack","Russell","Phone1","(559) 298-6411","1","01/06/2015 3pm","answered","","Edison"
I am using the BCP command to bulk load the table using the following FORMATFILE. The bulk table load works. But, it inserts garbage characters? Any help would be appreciated.
FORMATFILE
10.0
14
1 SQLNCHAR 0 0 "\"" 0 FirstQuoteSQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 50 "\",\"" 1 BroadcastName SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 50 "\",\"" 2 SubmittedBy SQL_Latin1_General_CP1_CI_AS
4 SQLINT 0 7 "\",\"" 3 StudentID SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 50 "\",\"" 4 FirstName SQL_Latin1_General_CP1_CI_AS
6 SQLNCHAR 0 50 "\",\"" 5 LastName SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 0 50 "\",\"" 6 PhoneSource SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 15 "\",\"" 7 NumberCalled SQL_Latin1_General_CP1_CI_AS
9 SQLINT 0 1 "\",\"" 8 CallAttempts SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 50 "\",\"" 9 LastAttempt SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 0 50 "\",\"" 10 Results SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 0 50 "\",\"" 11 Response SQL_Latin1_General_CP1_CI_AS
13 SQLINT 0 3 "\",\"" 12 SchoolID SQL_Latin1_General_CP1_CI_AS
14 SQLNCHAR 0 50 "\"\r" 13 SchoolName SQL_Latin1_General_CP1_CI_AS
BroadcastNameSubmittedByStudentIDFirstNameLastNamePhoneSourceNumberCalledCallAttemptsLastAttemptResultsResponseSchoolIDSchoolNameModifiedByModifiedDate
整瑳洠≥∬慒摮⁹潐敷汬ㄱㄱㄱㄱ1684955474潐敷汬桐湯ㅥ㔨㤵 㤲ⴸ㐶ㄱ∱∬〯⼶〲㔱㌠浰2004053601NULL摅獩湯ഢ∊整瑳洠≥∬慒摮⁹潐敷汬ㄱㄱㄱㄱ1684955474潐敷汬Ⱒ倢潨敮∱∬㔨㤵 㤲ⴸ㐶ㄱⰢㄢⰢ〢⼱㘰㈯‵瀳≭∬湡睳牥摥Ⱒ∢∬摅獩湯NULLNULL
整瑳洠≥∬慒摮⁹潐敷汬ㄱㄱㄱㄱ1684955474潐敷汬桐湯ㅥ㔨㤵 㤲ⴸ㐶ㄱ∱∬〯⼶〲㔱㌠浰2004053601NULL摅獩湯ഢ∊整瑳洠≥∬慒摮⁹潐敷汬ㄱㄱㄱㄱ1684955474潐敷汬Ⱒ倢潨敮∱∬㔨㤵 㤲ⴸ㐶ㄱⰢㄢⰢ〢⼱㘰㈯‵瀳≭∬湡睳牥摥Ⱒ∢∬摅獩湯NULLNULL
整瑳洠≥∬慒摮⁹潐敷汬ㄱㄱㄱㄱ1684955474潐敷汬桐湯ㅥ㔨㤵 㤲ⴸ㐶ㄱ∱∬〯⼶〲㔱㌠浰2004053601NULL摅獩湯ഢ∊整瑳洠≥∬慒摮⁹潐敷汬ㄱㄱㄱㄱ1684955474潐敷汬Ⱒ倢潨敮∱∬㔨㤵 㤲ⴸ㐶ㄱⰢㄢⰢ〢⼱㘰㈯‵瀳≭∬湡睳牥摥Ⱒ∢∬摅獩湯NULLNULL
整瑳洠≥∬慒摮⁹潐敷汬ㄱㄱㄱㄱ1684955474潐敷汬桐湯ㅥ㔨㤵 㤲ⴸ㐶ㄱ∱∬〯⼶〲㔱㌠浰2004053601NULL摅獩湯ഢ∊整瑳洠≥∬慒摮⁹潐敷汬ㄱㄱㄱㄱ1684955474潐敷汬Ⱒ倢潨敮∱∬㔨㤵 㤲ⴸ㐶ㄱⰢㄢⰢ〢⼱㘰㈯‵瀳≭∬湡睳牥摥Ⱒ∢∬摅獩湯NULLNULL
整瑳洠≥∬慒摮⁹潐敷汬ㄱㄱㄱㄱ1684955474潐敷汬桐湯ㅥ㔨㤵 㤲ⴸ㐶ㄱ∱∬〯⼶〲㔱㌠浰2004053601NULL摅獩湯ഢ∊整瑳洠≥∬慒摮⁹潐敷汬ㄱㄱㄱㄱ1684955474潐敷汬Ⱒ倢潨敮∱∬㔨㤵 㤲ⴸ㐶ㄱⰢㄢⰢ〢⼱㘰㈯‵瀳≭∬湡睳牥摥Ⱒ∢∬摅獩湯NULLNULL
January 7, 2015 at 3:35 pm
Can you post the bcp command that you're using?
January 7, 2015 at 4:12 pm
Here is my bcp command (see below). I fashioned my own FORMAT file and bcp command based on what I have read. And of course I used some examples I found. And what I am doing is testing what I have put together.
Obviously, If I understood all if it. I really would not have a need to post my questions.
'bcp Table IN FileName -T -f "PhoneLog.fmt"'
January 7, 2015 at 6:00 pm
Could you include the following parameters?
-c -C RAW
bcp Table IN FileName -T -c -C RAW -f "PhoneLog.fmt"
I'm glad that you read the questions below my name. Those questions are part of my signature and are intended as a warning for people that are used to copy code and use it in production without testing and understanding it.
January 8, 2015 at 9:30 am
Ahh, my bad. I do see that as your tag line :).
I added the switches you suggested to the BCP command. But I get the same results?
January 8, 2015 at 11:05 am
I'm not sure where the problem can be. Basically is because the codepages change in the process. I'm not sure if it's because you're using unicode types and mixing them with ansi types. With my limited experience with bcp, I usually have to play a little bit to be sure I'm getting the correct results.
I hope this gives you something to help you get on track.
January 8, 2015 at 1:37 pm
The problem could be that the file is actually a Unicode file and you might need to use the "-w" option. Look at the first 3 bytes of the file with a hex editor. If they look like "EF BB BF", there's a pretty strong chance it's a Unicode file. XML files can be different in that they can be Unicode without that particular leader.
When looking at the file in hex and even without the "EF BB BF" thing, if every other byte is the same ("00" for the US.English language), then it's probably Unicode.
For more info on this subject, please see the following...
http://en.wikipedia.org/wiki/Byte_order_mark#UTF-8
It could also be that someone selected some screwball code page. That's a bit more difficult to determine. The use of the RAW option (which you have) will, many times, correct that problem but you may have to use a particular code page option.
My bet is that it's a Unicode file, though.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply