Post reply

bcp format file and import fixed width text file

  • 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, 5 months ago by smattiko83.

Viewing post 31 (of 31 total)

You must be logged in to reply to this topic. Login to reply