bcp format file and import fixed width text file

• smattiko83

  SSC-Addicted

  Points: 489

  More actions

  April 13, 2020 at 6:28 pm

  #3742511

  Would this work to only have to hit run and it would import and create the table?

   

  if object_id('DE_IDENTIFIED.dbo.test') is not null
  drop table DE_IDENTIFIED.dbo.test
  
  if object_id('DE_IDENTIFIED.dbo.PROVIDER_INFO_1') is not null
  drop table DE_IDENTIFIED.dbo.PROVIDER_INFO_1
  
  
  create table DE_IDENTIFIED.dbo.test
  (record varchar(500)
  )
  
  bulk insert DE_IDENTIFIED.dbo.test
  from 'C:\dident\PRV414WSW\PRV414SW.txt' 
  with (ROWTERMINATOR = '0x0a')
  
  select rtrim(substring(record, 1,1)) AS RECORD_TYPE
  , rtrim(substring(record, 2,9)) AS PROMISE_NUMBER
  , rtrim(substring(record, 11,4)) AS LOCATION 
  , convert(date, rtrim(substring(record, 15,8))) AS START_DATE
  , convert(date, rtrim(substring(record, 23,8))) AS END_DATE
  , rtrim(substring(record, 31,50)) AS PROVIDER_NAME
  , rtrim(substring(record, 81,9)) AS IRS
  , rtrim(substring(record, 90,1)) AS IRS_INDICATOR
  , rtrim(substring(record, 91,10)) AS MEDICARE_NUMBER
  , rtrim(substring(record, 101,2)) AS COUNTY_CODE
  , rtrim(substring(record, 103,60)) AS ADDRESS
  , rtrim(substring(record, 163,18)) AS CITY
  , rtrim(substring(record, 181,2)) AS STATE
  , rtrim(substring(record, 183,5)) AS ZIP
  , rtrim(substring(record, 188,4)) AS BOX_NUMBER
  , rtrim(substring(record, 192,10)) AS PHONE
  , rtrim(substring(record, 202,9)) AS DEA_NUMBER
  , rtrim(substring(record, 211,6)) AS UPIN
  , rtrim(substring(record, 217,10)) AS NPI_MAMIS
  INTO DE_IDENTIFIED.DBO.PROVIDER_INFO_1
  from DE_IDENTIFIED.dbo.test
  where record like '1%'?

   

  • This reply was modified 5 years, 2 months ago by smattiko83.