BCP SQL Server into a table

Question

I am trying to BCP a file into a table that has the same columns as the file being brought in

BCP command

EXEC xp_cmdshell 'BCP [CMS_Data].[dat].[SNP_2019_02_Special Needs Plan Report_RAW] in "\\FileShare\datafeeds\CMS_ResearchStatistics\Temp\SNP-2019-02\SNP_2019_02_Special Needs Plan Report.csv" -t"|" -T -c -F2 -S MSSQLSERVER'

I am getting this error

Copy direction must be either 'in', 'out' or 'format'.

I am using F2 to skip the header row the error states I need a in, out or format but the in is clearly there. This must be some other issue I am not seeing. This code worked prior. I changed the F2 to F18 since that is where the data started on the file. Since that seemed like an issue I moved data to line 2 but now it does not work there either.

Simplify your problem. Start by using a command prompt and running bcp directly so you can avoid the tsql issues and you can better see the information bcp generates. Does that work? And perhaps reconsider the use of xp_cmdshell in this fashion. — SMor
This shouldn't be caused by any contents of the file but by the fact that bcp can't parse its command line (it's extremely finicky that way). If necessary retype the command to ensure no invisible spacing characters or strange encoding has crept in. Also try -F 2 (with an explicit space), even though that should not matter, and try changing the order of the parameters. — Jeroen Mostert

vvvv4d vvvv4d · Accepted Answer · 2020-08-17T16:13:05

I downloaded the data set from https://www.cms.gov/Research-Statistics-Data-and-Systems/Statistics-Trends-and-Reports/MCRAdvPartDEnrolData/Downloads/2019/Feb/SNP-2019-02.zip

I converted the xls to csv and deleted the first 16 rows to get to the "main table"

I replaced the , with | to match your file setup

I created your table in my DBA database in the dbo schema

CREATE TABLE [dbo].[SNP_2019_02_Special Needs Plan Report_RAW]( [Contract Number] [varchar](max) NULL, [Contract Name] [varchar](max) NULL, [Organization Type] [varchar](max) NULL, [Plan ID] [varchar](max) NULL, [Segment ID] [varchar](max) NULL, [Plan Name] [varchar](max) NULL, [Plan Type] [varchar](max) NULL, [Plan Geographic Name] [varchar](max) NULL, [State(s)] [varchar](max) NULL, [Plan Enrollment] [varchar](max) NULL, [Special Needs Plan Type] [varchar](max) NULL, [Specialty Diseases] [varchar](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

I ran BCP to copy the data from the csv I created from the zip contents and it successfully imported the data to the table created in DBA

bcp "[DBA].[dbo].[SNP_2019_02_Special Needs Plan Report_RAW]" in  "C:\Users\someuser\Desktop\SNP_2019_02.csv" -c -t"|" -T -S "bi-server"

BCP SQL Server into a table

1 Answers