bcp format file and import fixed width text file

• frederico_fonseca

  SSCoach

  Points: 16155

  More actions

  April 13, 2020 at 5:18 pm

  #3742488

  using https://mh-nexus.de/en/hxd/ - its a must have tool for a developer that deals with multiple type of files. with it you can see if the row delim is "0d", "0A" or both (other exist but very uncommon)

   

  regarding inserting into another table - with the sql you supplied it is just a question of doing an insert into your desired table

  something like

  cte...
  
  insert into final table
  
  select one.[Record Type]
  
  ...
  
  
  

  but not advisable - record type 2 has multiple entries and as such should be on its own table

  One other thing - I have kept the field names as they are on the file - but really you should change them so that they do not have spaces on their names neither do they require the use of [] around them - this is best practice.

• smattiko83

  SSC-Addicted

  Points: 487

  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 4 years, 7 months ago by  smattiko83.