BCP Error
-
Thank you for the information - I'm still having trouble loading the data into this table. I am soooo frustrated.
So, what I've done is begin with a fresh start:
1. I created a new format file using the following script:
bcp FormDotCom.dbo.Outpatient_Access format nul -w -f C:\Outpatient_Access_Dataload\Outpatient_Access-c.fmt -S myinstance\namedinstance -T
2. I Edited my format file to ensure that the column delimiter matched that of my data9.0
69
1 SQLNCHAR 0 24 "," 1 ID SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 0 "," 2 Submit_Date SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 0 "," 3 Email_Password SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 0 "," 4 Login_Unique_ID SQL_Latin1_General_CP1_CI_AS
5 SQLNCHAR 0 0 "," 5 Parent_Name SQL_Latin1_General_CP1_CI_AS
6 SQLNCHAR 0 0 "," 6 Provider_Name SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 0 0 "," 7 Provider_ID SQL_Latin1_General_CP1_CI_AS
8 SQLNCHAR 0 0 "," 8 Address_1 SQL_Latin1_General_CP1_CI_AS
9 SQLNCHAR 0 0 "," 9 Address_2 SQL_Latin1_General_CP1_CI_AS
10 SQLNCHAR 0 0 "," 10 City SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 0 0 "," 11 State SQL_Latin1_General_CP1_CI_AS
12 SQLNCHAR 0 0 "," 12 Zip SQL_Latin1_General_CP1_CI_AS
13 SQLNCHAR 0 0 "," 13 Preparer_FirstName SQL_Latin1_General_CP1_CI_AS
14 SQLNCHAR 0 0 "," 14 Preparer_LastName SQL_Latin1_General_CP1_CI_AS
15 SQLNCHAR 0 0 "," 15 Preparer_Title SQL_Latin1_General_CP1_CI_AS
16 SQLNCHAR 0 0 "," 16 Preparer_PhoneNumber SQL_Latin1_General_CP1_CI_AS
17 SQLNCHAR 0 0 "," 17 Preparer_Phone_Number_Extention SQL_Latin1_General_CP1_CI_AS
18 SQLNCHAR 0 0 "," 18 Preparer_EmailAddress SQL_Latin1_General_CP1_CI_AS
19 SQLNCHAR 0 0 "," 19 Coordinator_FirstName SQL_Latin1_General_CP1_CI_AS
20 SQLNCHAR 0 0 "," 20 Coordinator_LastName SQL_Latin1_General_CP1_CI_AS
21 SQLNCHAR 0 0 "," 21 Coordinator_Title SQL_Latin1_General_CP1_CI_AS
22 SQLNCHAR 0 0 "," 22 Coordinator_PhoneNumber SQL_Latin1_General_CP1_CI_AS
23 SQLNCHAR 0 0 "," 23 Coordinator_PhoneNumber_Extension SQL_Latin1_General_CP1_CI_AS
24 SQLNCHAR 0 0 "," 24 Coordinator_EmailAddress SQL_Latin1_General_CP1_CI_AS
25 SQLNCHAR 0 0 "," 25 Child_Treatment_Ages_0_to_18 SQL_Latin1_General_CP1_CI_AS
26 SQLNCHAR 0 0 "," 26 Child_Age_0_to_3 SQL_Latin1_General_CP1_CI_AS
27 SQLNCHAR 0 0 "," 27 Child_Age_4_to_5 SQL_Latin1_General_CP1_CI_AS
28 SQLNCHAR 0 0 "," 28 Child_Age_6_to_12 SQL_Latin1_General_CP1_CI_AS
29 SQLNCHAR 0 0 "," 29 Child_Age_13_to_18 SQL_Latin1_General_CP1_CI_AS
30 SQLNCHAR 0 0 "," 30 Child_Intake_Wait_Time SQL_Latin1_General_CP1_CI_AS
31 SQLNCHAR 0 0 "," 31 Child_Psychotherapy_Wait_Time SQL_Latin1_General_CP1_CI_AS
32 SQLNCHAR 0 0 "," 32 Child_Psychiatry_Wait_Time SQL_Latin1_General_CP1_CI_AS
33 SQLNCHAR 0 0 "," 33 Child_Walk_In_Psychiatry_hours SQL_Latin1_General_CP1_CI_AS
34 SQLNCHAR 0 0 "," 34 Child_Walk_In_Psychiatry_Mon_From SQL_Latin1_General_CP1_CI_AS
35 SQLNCHAR 0 0 "," 35 Child_Walk_In_Psychiatry_Mon_To SQL_Latin1_General_CP1_CI_AS
36 SQLNCHAR 0 0 "," 36 Child_Walk_In_Psychiatry_Tues_From SQL_Latin1_General_CP1_CI_AS
37 SQLNCHAR 0 0 "," 37 Child_Walk_In_Psychiatry_Tues_To SQL_Latin1_General_CP1_CI_AS
38 SQLNCHAR 0 0 "," 38 Child_Walk_In_Psychiatry_Wed_From SQL_Latin1_General_CP1_CI_AS
39 SQLNCHAR 0 0 "," 39 Child_Walk_In_Psychiatry_Wed_To SQL_Latin1_General_CP1_CI_AS
40 SQLNCHAR 0 0 "," 40 Child_Walk_In_Psychiatry_Thurs_From SQL_Latin1_General_CP1_CI_AS
41 SQLNCHAR 0 0 "," 41 Child_Walk_In_Psychiatry_Thurs_To SQL_Latin1_General_CP1_CI_AS
42 SQLNCHAR 0 0 "," 42 Child_Walk_In_Psychiatry_Fri_From SQL_Latin1_General_CP1_CI_AS
43 SQLNCHAR 0 0 "," 43 Child_Walk_In_Psychiatry_Fri_To SQL_Latin1_General_CP1_CI_AS
44 SQLNCHAR 0 0 "," 44 Child_Walk_In_Psychiatry_Sat_From SQL_Latin1_General_CP1_CI_AS
45 SQLNCHAR 0 0 "," 45 Child_Walk_In_Psychiatry_Sat_To SQL_Latin1_General_CP1_CI_AS
46 SQLNCHAR 0 0 "," 46 Child_Walk_In_Psychiatry_Sun_From SQL_Latin1_General_CP1_CI_AS
47 SQLNCHAR 0 0 "," 47 Child_Walk_In_Psychiatry_Sun_To SQL_Latin1_General_CP1_CI_AS
48 SQLNCHAR 0 0 "," 48 Adults_Treatment_Ages_18_and_over SQL_Latin1_General_CP1_CI_AS
49 SQLNCHAR 0 0 "," 49 Adult_Intake_Wait_time SQL_Latin1_General_CP1_CI_AS
50 SQLNCHAR 0 0 "," 50 Adult_Intake_Psychotherapy_wait_time SQL_Latin1_General_CP1_CI_AS
51 SQLNCHAR 0 0 "," 51 Adult_Psychiatry_wait_time SQL_Latin1_General_CP1_CI_AS
52 SQLNCHAR 0 0 "," 52 Medicare_Contracted SQL_Latin1_General_CP1_CI_AS
53 SQLNCHAR 0 0 "," 53 Medicare_Advantage_plan SQL_Latin1_General_CP1_CI_AS
54 SQLNCHAR 0 0 "," 54 Adult_walk_In_psychiatry_hours SQL_Latin1_General_CP1_CI_AS
55 SQLNCHAR 0 0 "," 55 Adult_Walk_In_Psychiatry_Mon_From SQL_Latin1_General_CP1_CI_AS
56 SQLNCHAR 0 0 "," 56 Adult_Walk_In_Psychiatry_Mon_To SQL_Latin1_General_CP1_CI_AS
57 SQLNCHAR 0 0 "," 57 Adult_Walk_In_Psychiatry_Tues_From SQL_Latin1_General_CP1_CI_AS
58 SQLNCHAR 0 0 "," 58 Adult_Walk_In_Psychiatry_Tues_To SQL_Latin1_General_CP1_CI_AS
59 SQLNCHAR 0 0 "," 59 Adult_Walk_In_Psychiatry_Wed_From SQL_Latin1_General_CP1_CI_AS
60 SQLNCHAR 0 0 "," 60 Adult_Walk_In_Psychiatry_Wed_To SQL_Latin1_General_CP1_CI_AS
61 SQLNCHAR 0 0 "," 61 Adult_Walk_In_Psychiatry_Thurs_From SQL_Latin1_General_CP1_CI_AS
62 SQLNCHAR 0 0 "," 62 Adult_Walk_In_Psychiatry_Thurs_To SQL_Latin1_General_CP1_CI_AS
63 SQLNCHAR 0 0 "," 63 Adult_Walk_In_Psychiatry_Fri_From SQL_Latin1_General_CP1_CI_AS
64 SQLNCHAR 0 0 "," 64 Adult_Walk_In_Psychiatry_Fri_To SQL_Latin1_General_CP1_CI_AS
65 SQLNCHAR 0 0 "," 65 Adult_Walk_In_Psychiatry_Sat_From SQL_Latin1_General_CP1_CI_AS
66 SQLNCHAR 0 0 "," 66 Adult_Walk_In_Psychiatry_Sat_To SQL_Latin1_General_CP1_CI_AS
67 SQLNCHAR 0 0 "," 67 Adult_Walk_In_Psychiatry_Sun_From SQL_Latin1_General_CP1_CI_AS
68 SQLNCHAR 0 0 "," 68 Adult_Walk_In_Psychiatry_Sun_To SQL_Latin1_General_CP1_CI_AS
69 SQLNCHAR 0 0 "\r\0\n\0" 69 New_referral SQL_Latin1_General_CP1_CI_AS3. I tried importing the data using the following bcp script:
bcp FormDotCom.dbo.Outpatient_Access IN C:\Outpatient_Access_DataLoad\Outpatient_Access.csv -E -w -f C:\Outpatient_Access_DataLoad\Outpatient_Access-c.fmt -SMyinstance\namedinstance -TWhen I run this script I get a warning followed by the error message:
Warning: -f overrides -w.
Starting copy...
SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file4. I then edited the script and ran it with - w and with -f - see below the results:
With - w:
bcp FormDotCom.dbo.Outpatient_Access IN C:\Outpatient_Access_DataLoad\Outpatient_Access.csv -E -w -SMyinstance\namedinstance -TStarting copy...
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1With - f:
bcp FormDotCom.dbo.Outpatient_Access IN C:\Outpatient_Access_DataLoad\Outpatient_Access.csv -E -f C:\Outpatient_Access_DataLoad\Outpatient_Access-c.fmt -SMyinstance\namedinstance -TStarting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1Lastly, I am not sure where to begin with this one: "If the first 2 bytes of the file aren't 0xFFEE, then you might not be able to import this file using Bulk Insert or BCP"
Thanks so much. -
One thing i did find in my data was that the bcp was reading a comma in my data as a terminator, although I had it in a double quote. I also saw that I had an extra column in my data file which I removed and thought that would fix my problem, however it hasn't. when I run my bcp script I keep getting the following:
Starting copy...
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1I tried Bulk Insert with format file and still no luck. I get the following:
Msg 4863, Level 16, State 1, Line 14
Bulk load data conversion error (truncation) for row 1, column 1 (ID).
Msg 7399, Level 16, State 1, Line 14
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 14
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".Bulk Insert without a format file I get the following:
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (ID).At this point I have no clue what the issue is.
-
So, I saved my data file in notepad in ANSI format and was able to load the data using Bulk Insert - however, when I load the data, only one row is inserted. Apparently all 14 rows of data in my data file is loaded as one row. See below for image:
-
Again, what are the first 4 bytes of the file? Data or 0xFFEE or ??? Also, your row termination is wonky... what do you expect "\r\0\n\0" to actually do for you?
Also, it's real difficult for us to trouble shoot without a copy of the file.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, thank you for your questions - helpful
I was able to load my data successfully using Bulk Insert.
What I did:1. I saved my file in Unicode encoding with out the header
2.changed my row(""\r\0\n\0"" to "<>") and column("," to "|") delimiters.
3.I added my delimiter at the end of each row in my datafile - one thing that I didn't do previouslyBULK
INSERT Outpatient_Access
FROM 'C:\Outpatient_Access_DataLoad\Outpatient_Access.csv'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '<>',
DATAFILETYPE = 'widechar'
);
GO
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply