Search code examples
sql-serverbcp

BCP SQL Server into a table

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.

Solution

  • 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 results