BCP Error
-
Hi All:
I'm trying to import data using bcp however, my I keep getting the below message. I have checked the name of the file to see if I made a typo, but I don't find anything. I checked that I have full permissions on the file. Any ideas?
ERROR
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Format file could not be opene
d. Invalid name specified or access denied.SCRIPT I'M RUNNING
"bcp FormDotCom.dbo.Outpatient_Access IN C:\Outpatient_Access_DataLoad\Outpatient_Access.csv -f C:\Outpatient_Access_DataLoad\Outpatient_Access-c.fmt -S"myservername\instance" -T" -
you running that on your machine or on the server through cmdshell or sql server agent job? if on the server the files need to be on the server C: drive, not your own local one. And on that case it is the user running the SQL instance that needs access to the files, not you.
-
I am running it running it from the server - the files are on the c:\ and I believe the service account running sql server has the permission to access the files. However, I am going to double check to ensure that the service account indeed has all of the permissions on the files.
-
I was able to identify the why I was receiving the error message: "Format file could not be opened, Invalid name specified or denied." My file extensions were hidden, as a result I could not see that the format file already had a ".fmt" extension and so I added a .fmt extension. Once I made my file extensions visible, I fixed the issue.
Unfortunately, I am still unable to load my data has I now have different error. Now I have this "I/O error while reading BCP forma file." I have edited my format file to ensure that the cursor is in the right place, it's not working. I've made sure that the files are accessible, still having the same issue. Any help will be appreciated.
-
EMtwo - Saturday, August 4, 2018 8:01 PM
Quick thought, open a CMD as Admin and use CACLS to list the ACLs in that folder, should tell you if the user has permissions on the files in question.
😎 -
So far it appears that the user has permissions on the files in the directory.
I am unfortunately still trying to load data in this table. This the current error I'm getting when try bcp ...ing into the table:
BCP Command Line error
Starting copy...0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1Bulk Insert Error
Msg 4832, Level 16, State 1, Line 13
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 13
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 13
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
See below for bulk insert script and bcp scriptBulk Insert script
BULK
INSERT Outpatient_Access
FROM 'C:\C\Outpatient_Access_DataLoad\Outpatient_Access.csv'
WITH
(
FORMATFILE = 'C;\Outpatiient_Access_DataLoad\Outpatient_Access-c.fmt',
DATAFILETYPE = 'widechar'
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\0\r\0n'
)
GOBCP commandline script
bcp MyDatabase.dbo.Outpatient_Access IN C:\Outpatient_Access_DataLoad\Outpatient_Access.csv
-f C:\Outpatient_Access_DataLoad\Outpatient_Access-c.fmt -eC:\Outpatient_Access
_Dataload\Outpatient_error.log -SInstance\namedinstance -T -
EMtwo - Monday, August 6, 2018 5:03 PM
If you're using a format file, then why are you specifying field and row terminators?
--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)
Intro to Tally Tables and Functions -
I shouldn't because the format file contains that information... Thanks.
What I have now done is to run the bulk insert script using a format file and with the field and row delimiter(without a format file) see result below:
Without a Fromat File
BULK
INSERT Outpatient_Access
FROM 'C:\Outpatient_Access_DataLoad\Outpatient_Access.csv'
WITH
(
FIELDTERMINATOR = '","',
ROWTERMINATOR = '\n'
)
GOResult
Bulk load: DataFileType was incorrectly specified as char. DataFileType will be assumed to be widechar because the data file has a Unicode signature.
Bulk load: DataFileType was incorrectly specified as char. DataFileType will be assumed to be widechar because the data file has a Unicode signature.
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
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 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".With a Format file
BULK
INSERT Outpatient_Access
FROM 'C:\Outpatient_Access_DataLoad\Outpatient_Access.csv'
WITH
(
FORMATFILE = 'C:\Outpatient_Access_DataLoad\Outpatient_Access-c.fmt'
);
GOResult
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
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 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". -
So I tried using BCP again with an error log added this is what I get:
#@ Row 1, Column 4: Invalid character value for cast specification @#
ÿþS
#@ Row 2, Column 2: Invalid character value for cast specification @#
#@ Row 3, Column 2: Invalid character value for cast specification @#
#@ Row 4, Column 2: Invalid character value for cast specification @#
#@ Row 5, Column 2: Invalid character value for cast specification @#
#@ Row 6, Column 2: Invalid character value for cast specification @#
#@ Row 7, Column 2: Invalid character value for cast specification @# -
There are a huge number of possibilities as to what is wrong. At this point, I'm going to suggest that we might not be able to help at all without a copy of the file and the DDL for the target table. Of course, if the file has any PII or other sensitive information in it, you shouldn't attach it. Instead, make a copy of the file and manually change the sensitive items on, say, the first 5 or 10 rows, delete the rest of the rows, and attach that.
--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)
Intro to Tally Tables and Functions -
I agree....
Below are the following - create table script to show my table structure, script I used to create my format file, and my bcp script to input the data:
Create table script
CREATE TABLE [dbo].[Outpatient_Access](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Submit_Date] [nvarchar](max) NULL,
[Email_Password] [nvarchar](max) NULL,
[Login_Unique_ID] [int] NOT NULL,
[Parent_Name] [nvarchar](max) NULL,
[Provider_Name] [nvarchar](max) NULL,
[Provider_ID] [int] NULL,
[Address_1] [nvarchar](max) NULL,
[Address_2] [nvarchar](max) NULL,
[City] [nvarchar](max) NULL,
[State] [nvarchar](max) NULL,
[Zip] [nvarchar](max) NULL,
[Preparer_FirstName] [nvarchar](max) NULL,
[Preparer_LastName] [nvarchar](max) NULL,
[Preparer_Title] [nvarchar](max) NULL,
[Preparer_PhoneNumber] [nvarchar](max) NULL,
[Preparer_Phone_Number_Extention] [nvarchar](max) NULL,
[Preparer_EmailAddress] [nvarchar](max) NULL,
[Coordinator_FirstName] [nvarchar](max) NULL,
[Coordinator_LastName] [nvarchar](max) NULL,
[Coordinator_Title] [nvarchar](max) NULL,
[Coordinator_PhoneNumber] [nvarchar](max) NULL,
[Coordinator_PhoneNumber_Extension] [nvarchar](max) NULL,
[Coordinator_EmailAddress] [nvarchar](max) NULL,
[Child_Treatment_Ages_0_to_18] [nvarchar](max) NULL,
[Child_Age_0_to_3] [nvarchar](max) NULL,
[Child_Age_4_to_5] [nvarchar](max) NULL,
[Child_Age_6_to_12] [nvarchar](max) NULL,
[Child_Age_13_to_18] [nvarchar](max) NULL,
[Child_Intake_Wait_Time] [nvarchar](max) NULL,
[Child_Psychotherapy_Wait_Time] [nvarchar](max) NULL,
[Child_Psychiatry_Wait_Time] [nvarchar](max) NULL,
[Child_Walk_In_Psychiatry_hours] [nvarchar](max) NULL,
[Child_Walk_In_Psychiatry_Mon_From] [nvarchar](max) NULL,
[Child_Walk_In_Psychiatry_Mon_To] [nvarchar](max) NULL,
[Child_Walk_In_Psychiatry_Tues_From] [nvarchar](max) NULL,
[Child_Walk_In_Psychiatry_Tues_To] [nvarchar](max) NULL,
[Child_Walk_In_Psychiatry_Wed_From] [nvarchar](max) NULL,
[Child_Walk_In_Psychiatry_Wed_To] [nvarchar](max) NULL,
[Child_Walk_In_Psychiatry_Thurs_From] [nvarchar](max) NULL,
[Child_Walk_In_Psychiatry_Thurs_To] [nvarchar](max) NULL,
[Child_Walk_In_Psychiatry_Fri_From] [nvarchar](max) NULL,
[Child_Walk_In_Psychiatry_Fri_To] [nvarchar](max) NULL,
[Child_Walk_In_Psychiatry_Sat_From] [nvarchar](max) NULL,
[Child_Walk_In_Psychiatry_Sat_To] [nvarchar](max) NULL,
[Child_Walk_In_Psychiatry_Sun_From] [nvarchar](max) NULL,
[Child_Walk_In_Psychiatry_Sun_To] [nvarchar](max) NULL,
[Adults_Treatment_Ages_18_and_over] [nvarchar](max) NULL,
[Adult_Intake_Wait_time] [nvarchar](max) NULL,
[Adult_Intake_Psychotherapy_wait_time] [nvarchar](max) NULL,
[Adult_Psychiatry_wait_time] [nvarchar](max) NULL,
[Medicare_Contracted] [nvarchar](max) NULL,
[Medicare_Advantage_plan] [nvarchar](max) NULL,
[Adult_walk_In_psychiatry_hours] [nvarchar](max) NULL,
[Adult_Walk_In_Psychiatry_Mon_From] [nvarchar](max) NULL,
[Adult_Walk_In_Psychiatry_Mon_To] [nvarchar](max) NULL,
[Adult_Walk_In_Psychiatry_Tues_From] [nvarchar](max) NULL,
[Adult_Walk_In_Psychiatry_Tues_To] [nvarchar](max) NULL,
[Adult_Walk_In_Psychiatry_Wed_From] [nvarchar](max) NULL,
[Adult_Walk_In_Psychiatry_Wed_To] [nvarchar](max) NULL,
[Adult_Walk_In_Psychiatry_Thurs_From] [nvarchar](max) NULL,
[Adult_Walk_In_Psychiatry_Thurs_To] [nvarchar](max) NULL,
[Adult_Walk_In_Psychiatry_Fri_From] [nvarchar](max) NULL,
[Adult_Walk_In_Psychiatry_Fri_To] [nvarchar](max) NULL,
[Adult_Walk_In_Psychiatry_Sat_From] [nvarchar](max) NULL,
[Adult_Walk_In_Psychiatry_Sat_To] [nvarchar](max) NULL,
[Adult_Walk_In_Psychiatry_Sun_From] [nvarchar](max) NULL,
[Adult_Walk_In_Psychiatry_Sun_To] [nvarchar](max) NULL,
[New_referral] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO
Format file script
bcp FormDotCom.dbo.Outpatient_Access format nul -T -c -f C:\Users\myuserprofiledirectory\Documents\Outpatient_Access-c.fmt -S myinstance\namedinstanceBcp script for importing data
bcp FormDotCom.dbo.Outpatient_Access IN C:\Outpatient_Access_DataLoad\Outpatient_Access.csv-f C:\Outpatient_Access_DataLoad\Outpatient_Access-c.fmt -eC:\Outpatient_Access_Dataload\Outpatient_error.log -Smyinstance\namedinstance -T -
-
11.0
69
1 SQLCHAR 0 12 "\t" 1 ID ""
2 SQLCHAR 0 0 "\t" 2 Submit_Date SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 "\t" 3 Email_Password SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 12 "\t" 4 Login_Unique_ID ""
5 SQLCHAR 0 0 "\t" 5 Parent_Name SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 0 "\t" 6 Provider_Name SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 12 "\t" 7 Provider_ID ""
8 SQLCHAR 0 0 "\t" 8 Address_1 SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 0 "\t" 9 Address_2 SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 0 "\t" 10 City SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 0 "\t" 11 State SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 0 "\t" 12 Zip SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 0 "\t" 13 Preparer_FirstName SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 0 "\t" 14 Preparer_LastName SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 0 "\t" 15 Preparer_Title SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 0 "\t" 16 Preparer_PhoneNumber SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 0 "\t" 17 Preparer_Phone_Number_Extention SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 0 "\t" 18 Preparer_EmailAddress SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 0 "\t" 19 Coordinator_FirstName SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 0 "\t" 20 Coordinator_LastName SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 0 "\t" 21 Coordinator_Title SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 0 "\t" 22 Coordinator_PhoneNumber SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 0 "\t" 23 Coordinator_PhoneNumber_Extension SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 0 "\t" 24 Coordinator_EmailAddress SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 0 "\t" 25 Child_Treatment_Ages_0_to_18 SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 0 "\t" 26 Child_Age_0_to_3 SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 0 "\t" 27 Child_Age_4_to_5 SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 0 0 "\t" 28 Child_Age_6_to_12 SQL_Latin1_General_CP1_CI_AS
29 SQLCHAR 0 0 "\t" 29 Child_Age_13_to_18 SQL_Latin1_General_CP1_CI_ASA
30 SQLCHAR 0 0 "\t" 30 Child_Intake_Wait_Time SQL_Latin1_General_CP1_CI_AS
31 SQLCHAR 0 0 "\t" 31 Child_Psychotherapy_Wait_Time SQL_Latin1_General_CP1_CI_AS
32 SQLCHAR 0 0 "\t" 32 Child_Psychiatry_Wait_Time SQL_Latin1_General_CP1_CI_AS
33 SQLCHAR 0 0 "\t" 33 Child_Walk_In_Psychiatry_hours SQL_Latin1_General_CP1_CI_AS
34 SQLCHAR 0 0 "\t" 34 Child_Walk_In_Psychiatry_Mon_From SQL_Latin1_General_CP1_CI_AS
35 SQLCHAR 0 0 "\t" 35 Child_Walk_In_Psychiatry_Mon_To SQL_Latin1_General_CP1_CI_AS
36 SQLCHAR 0 0 "\t" 36 Child_Walk_In_Psychiatry_Tues_From SQL_Latin1_General_CP1_CI_AS
37 SQLCHAR 0 0 "\t" 37 Child_Walk_In_Psychiatry_Tues_To SQL_Latin1_General_CP1_CI_AS
38 SQLCHAR 0 0 "\t" 38 Child_Walk_In_Psychiatry_Wed_From SQL_Latin1_General_CP1_CI_AS
39 SQLCHAR 0 0 "\t" 39 Child_Walk_In_Psychiatry_Wed_To SQL_Latin1_General_CP1_CI_AS
40 SQLCHAR 0 0 "\t" 40 Child_Walk_In_Psychiatry_Thurs_From SQL_Latin1_General_CP1_CI_AS
41 SQLCHAR 0 0 "\t" 41 Child_Walk_In_Psychiatry_Thurs_To SQL_Latin1_General_CP1_CI_AS
42 SQLCHAR 0 0 "\t" 42 Child_Walk_In_Psychiatry_Fri_From SQL_Latin1_General_CP1_CI_AS
43 SQLCHAR 0 0 "\t" 43 Child_Walk_In_Psychiatry_Fri_To SQL_Latin1_General_CP1_CI_AS
44 SQLCHAR 0 0 "\t" 44 Child_Walk_In_Psychiatry_Sat_From SQL_Latin1_General_CP1_CI_AS
45 SQLCHAR 0 0 "\t" 45 Child_Walk_In_Psychiatry_Sat_To SQL_Latin1_General_CP1_CI_AS
46 SQLCHAR 0 0 "\t" 46 Child_Walk_In_Psychiatry_Sun_From SQL_Latin1_General_CP1_CI_AS
47 SQLCHAR 0 0 "\t" 47 Child_Walk_In_Psychiatry_Sun_To SQL_Latin1_General_CP1_CI_AS
48 SQLCHAR 0 0 "\t" 48 Adults_Treatment_Ages_18_and_over SQL_Latin1_General_CP1_CI_AS
49 SQLCHAR 0 0 "\t" 49 Adult_Intake_Wait_time SQL_Latin1_General_CP1_CI_AS
50 SQLCHAR 0 0 "\t" 50 Adult_Intake_Psychotherapy_wait_time SQL_Latin1_General_CP1_CI_AS
51 SQLCHAR 0 0 "\t" 51 Adult_Psychiatry_wait_time SQL_Latin1_General_CP1_CI_AS
52 SQLCHAR 0 0 "\t" 52 Medicare_Contracted SQL_Latin1_General_CP1_CI_AS
53 SQLCHAR 0 0 "\t" 53 Medicare_Advantage_plan SQL_Latin1_General_CP1_CI_AS
54 SQLCHAR 0 0 "\t" 54 Adult_walk_In_psychiatry_hours SQL_Latin1_General_CP1_CI_AS
55 SQLCHAR 0 0 "\t" 55 Adult_Walk_In_Psychiatry_Mon_From SQL_Latin1_General_CP1_CI_AS
56 SQLCHAR 0 0 "\t" 56 Adult_Walk_In_Psychiatry_Mon_To SQL_Latin1_General_CP1_CI_AS
57 SQLCHAR 0 0 "\t" 57 Adult_Walk_In_Psychiatry_Tues_From SQL_Latin1_General_CP1_CI_AS
58 SQLCHAR 0 0 "\t" 58 Adult_Walk_In_Psychiatry_Tues_To SQL_Latin1_General_CP1_CI_AS
59 SQLCHAR 0 0 "\t" 59 Adult_Walk_In_Psychiatry_Wed_From SQL_Latin1_General_CP1_CI_AS
60 SQLCHAR 0 0 "\t" 60 Adult_Walk_In_Psychiatry_Wed_To SQL_Latin1_General_CP1_CI_AS
61 SQLCHAR 0 0 "\t" 61 Adult_Walk_In_Psychiatry_Thurs_From SQL_Latin1_General_CP1_CI_AS
62 SQLCHAR 0 0 "\t" 62 Adult_Walk_In_Psychiatry_Thurs_To SQL_Latin1_General_CP1_CI_AS
63 SQLCHAR 0 0 "\t" 63 Adult_Walk_In_Psychiatry_Fri_From SQL_Latin1_General_CP1_CI_AS
64 SQLCHAR 0 0 "\t" 64 Adult_Walk_In_Psychiatry_Fri_To SQL_Latin1_General_CP1_CI_AS
65 SQLCHAR 0 0 "\t" 65 Adult_Walk_In_Psychiatry_Sat_From SQL_Latin1_General_CP1_CI_AS
66 SQLCHAR 0 0 "\t" 66 Adult_Walk_In_Psychiatry_Sat_To SQL_Latin1_General_CP1_CI_AS
67 SQLCHAR 0 0 "\t" 67 Adult_Walk_In_Psychiatry_Sun_From SQL_Latin1_General_CP1_CI_AS
68 SQLCHAR 0 0 "\t" 68 Adult_Walk_In_Psychiatry_Sun_To SQL_Latin1_General_CP1_CI_AS
69 SQLCHAR 0 0 "\r\n" 69 New_referral SQL_Latin1_General_CP1_CI_AS -
At this point when I run my bcp script, I get the following in my error log file:Invalid character value for cast specification @#
ÿþI
#@ Row 3, Column 4: Invalid character value for cast specification @#
#@ Row 4, Column 4: Invalid character value for cast specification @#
#@ Row 6, Column 4: Invalid character value for cast specification @#The table is loaded with these rows and they are zeros although the datafile contains data that is not zero:
-
First of all, I've only ever seen one other table consisting of mostly NVARCHAR(MAX) columns and it was horribly slow because of it. I know you're just trying to get some data into the table and might try to "right size" the data types later but thought it would be worth mentioning.
Second, your ID column is an IDENTITY column. You're not using the -E switch to preserve the imported values for that column. Are you sure that's what you want?
:Third, I'm under the impression that the import file is a Unicode file but you're not using the -w switch to specify that.
Fourth, the first error you have posted in your last post above has me concerned. IIRC, SQL Server 12 does not support imports of UTF-8 files and that's the kind of error I remember seeing a couple of years back when someone else tried. 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.
Fifth, you might also have something a bit wonky going on in your BCP command because you have a "-c" in one of your file names. I recommend always using double quotes around the full path of all file names. You also have spaces missing before things like the -f switch. I don't know if that's going to hurt anything (probably not because it didn't give you an error) but I don't trust things and so will always include a space before any of the switches, dashed or not.
If you don't want to import the values for the ID column, then here's what I think your BCP command needs to look like. I'd also be tempted to put braces around the full instance name to bullet proof it for future "illegal: characters.
bcp FormDotCom.dbo.Outpatient_Access IN "C:\Outpatient_Access_DataLoad\Outpatient_Access.csv" -f"C:\Outpatient_Access_DataLoad\Outpatient_Access-c.fmt" -e"C:\Outpatient_Access_Dataload\Outpatient_error.log" -Smyinstance\namedinstance -T -w--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)
Intro to Tally Tables and Functions
At this point when I run my bcp script, I get the following in my error log file:Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply