Post reply

bcp format file and import fixed width text file

  • April 9, 2020 at 4:50 pm

    #3741830

    This is unedited other than deleting lines so it is small enough.

    Attachments:
    You must be logged in to view attached files.

  • April 9, 2020 at 5:33 pm

    #3741841

    I think the record layout is correct - its just that column "provider name" should be further divided into LastName, FirstName, Middle Initial (probably)

    if object_id('tempdb..#data') is not null
    drop table #data
    create table #data
    (recordtype varchar(3)
    ,record varchar(500)
    )

    insert into #data
    select case
           when record like 'HDR%' then 'HDR'
           when record like 'TRL%' then 'TRL'
           when record like '1%' then '1'
           when record like '2%' then '2'
           else 'UNK'
           end as Recordtype
          , record

       
    from ( values
     ('HDRPRVP414M 20200303')
    ,('100107065400011987012922991231PEACHMAN                 FREDERICK    A           056442487S            1044 BELMONT AVE                                            YOUNGSTOWN        OH4450410063304803658AP2658613      0107065401')
    ,('2001070654000131311200507102299123135049435  22991231VMD A')
    ,('2001070654000131318198701292008092535049435  22991231VMD A')
    ,('100167693700032002020120160925ESTRADA                  SUSAN        V           110823270S          433339 PERRY HIGHWAY                                          SHEAKLEYVILLE     PA16151    7242533428BE5508900G619930167693706')
    ,('20016769370003313181998013020080925MD062293L 20201231VMD C')
    ,('20016769370003313222005071020160925MD062293L 20201231VMD C')
    ,('TRL000387588000463366')

         ) t(record)


    select rtrim(substring(record, 1,1)) as [Record Type]
         , rtrim(substring(record, 2,9)) as [Promise ID]
         , rtrim(substring(record, 11,4)) as [Location]
         , rtrim(substring(record, 15,2)) as [Provider Type]
         , rtrim(substring(record, 17,3)) as [Specialty]
         , rtrim(substring(record, 20,8)) as [Specialty Begin]
         , rtrim(substring(record, 28,8)) as [Specialty End]
         , rtrim(substring(record, 36,10)) as [Licens Number]
         , rtrim(substring(record, 46,8)) as [License End]
         , rtrim(substring(record, 54,1)) as [Practice Type]
         , rtrim(substring(record, 55,3)) as [Medica Degree]
         , rtrim(substring(record, 58,1)) as [Status]
         , rtrim(substring(record, 59,168)) as [Field8]
    from #data
    where recordtype = '2'


    select rtrim(substring(record, 1,1)) as [Record Type]
         , rtrim(substring(record, 2,9)) as [Promise Number]
         , rtrim(substring(record, 11,4)) as [Location]
         , rtrim(substring(record, 15,8)) as [Start 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]
    from #data
    where recordtype = '1'

    --possible split of proviner name
    select rtrim(substring(record, 1,1)) as [Record Type]
         , rtrim(substring(record, 2,9)) as [Promise Number]
         , rtrim(substring(record, 11,4)) as [Location]
         , rtrim(substring(record, 15,8)) as [Start Date]
         , rtrim(substring(record, 23,8)) as [End Date]
         , rtrim(substring(record, 31,25)) as [Provider Last Name]
         , rtrim(substring(record, 56,13)) as [Provider First Name]
         , rtrim(substring(record, 69,12)) as [Provider Middle Name] -- potentially even more divided) as it is 12 in size
         , 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]
    from #data
    where recordtype = '1'

  • April 9, 2020 at 6:01 pm

    #3741847

    frederico_fonseca wrote:

    I think the record layout is correct - its just that column "provider name" should be further divided into LastName, FirstName, Middle Initial (probably)

    if object_id('tempdb..#data') is not null
    drop table #data
    create table #data
    (recordtype varchar(3)
    ,record varchar(500)
    )

    insert into #data
    select case
           when record like 'HDR%' then 'HDR'
           when record like 'TRL%' then 'TRL'
           when record like '1%' then '1'
           when record like '2%' then '2'
           else 'UNK'
           end as Recordtype
          , record

       
    from ( values
     ('HDRPRVP414M 20200303')
    ,('100107065400011987012922991231PEACHMAN                 FREDERICK    A           056442487S            1044 BELMONT AVE                                            YOUNGSTOWN        OH4450410063304803658AP2658613      0107065401')
    ,('2001070654000131311200507102299123135049435  22991231VMD A')
    ,('2001070654000131318198701292008092535049435  22991231VMD A')
    ,('100167693700032002020120160925ESTRADA                  SUSAN        V           110823270S          433339 PERRY HIGHWAY                                          SHEAKLEYVILLE     PA16151    7242533428BE5508900G619930167693706')
    ,('20016769370003313181998013020080925MD062293L 20201231VMD C')
    ,('20016769370003313222005071020160925MD062293L 20201231VMD C')
    ,('TRL000387588000463366')

         ) t(record)


    select rtrim(substring(record, 1,1)) as [Record Type]
         , rtrim(substring(record, 2,9)) as [Promise ID]
         , rtrim(substring(record, 11,4)) as [Location]
         , rtrim(substring(record, 15,2)) as [Provider Type]
         , rtrim(substring(record, 17,3)) as [Specialty]
         , rtrim(substring(record, 20,8)) as [Specialty Begin]
         , rtrim(substring(record, 28,8)) as [Specialty End]
         , rtrim(substring(record, 36,10)) as [Licens Number]
         , rtrim(substring(record, 46,8)) as [License End]
         , rtrim(substring(record, 54,1)) as [Practice Type]
         , rtrim(substring(record, 55,3)) as [Medica Degree]
         , rtrim(substring(record, 58,1)) as [Status]
         , rtrim(substring(record, 59,168)) as [Field8]
    from #data
    where recordtype = '2'


    select rtrim(substring(record, 1,1)) as [Record Type]
         , rtrim(substring(record, 2,9)) as [Promise Number]
         , rtrim(substring(record, 11,4)) as [Location]
         , rtrim(substring(record, 15,8)) as [Start 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]
    from #data
    where recordtype = '1'

    --possible split of proviner name
    select rtrim(substring(record, 1,1)) as [Record Type]
         , rtrim(substring(record, 2,9)) as [Promise Number]
         , rtrim(substring(record, 11,4)) as [Location]
         , rtrim(substring(record, 15,8)) as [Start Date]
         , rtrim(substring(record, 23,8)) as [End Date]
         , rtrim(substring(record, 31,25)) as [Provider Last Name]
         , rtrim(substring(record, 56,13)) as [Provider First Name]
         , rtrim(substring(record, 69,12)) as [Provider Middle Name] -- potentially even more divided) as it is 12 in size
         , 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]
    from #data
    where recordtype = '1'

    The data the op provided doesn't align as nicely as you have in your test data.  See my previous post.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • April 9, 2020 at 6:03 pm

    #3741849

    Jeff Moden wrote:

    frederico_fonseca wrote:

    I think the record layout is correct - its just that column "provider name" should be further divided into LastName, FirstName, Middle Initial (probably)

    if object_id('tempdb..#data') is not null
    drop table #data
    create table #data
    (recordtype varchar(3)
    ,record varchar(500)
    )

    insert into #data
    select case
           when record like 'HDR%' then 'HDR'
           when record like 'TRL%' then 'TRL'
           when record like '1%' then '1'
           when record like '2%' then '2'
           else 'UNK'
           end as Recordtype
          , record

       
    from ( values
     ('HDRPRVP414M 20200303')
    ,('100107065400011987012922991231PEACHMAN                 FREDERICK    A           056442487S            1044 BELMONT AVE                                            YOUNGSTOWN        OH4450410063304803658AP2658613      0107065401')
    ,('2001070654000131311200507102299123135049435  22991231VMD A')
    ,('2001070654000131318198701292008092535049435  22991231VMD A')
    ,('100167693700032002020120160925ESTRADA                  SUSAN        V           110823270S          433339 PERRY HIGHWAY                                          SHEAKLEYVILLE     PA16151    7242533428BE5508900G619930167693706')
    ,('20016769370003313181998013020080925MD062293L 20201231VMD C')
    ,('20016769370003313222005071020160925MD062293L 20201231VMD C')
    ,('TRL000387588000463366')

         ) t(record)


    select rtrim(substring(record, 1,1)) as [Record Type]
         , rtrim(substring(record, 2,9)) as [Promise ID]
         , rtrim(substring(record, 11,4)) as [Location]
         , rtrim(substring(record, 15,2)) as [Provider Type]
         , rtrim(substring(record, 17,3)) as [Specialty]
         , rtrim(substring(record, 20,8)) as [Specialty Begin]
         , rtrim(substring(record, 28,8)) as [Specialty End]
         , rtrim(substring(record, 36,10)) as [Licens Number]
         , rtrim(substring(record, 46,8)) as [License End]
         , rtrim(substring(record, 54,1)) as [Practice Type]
         , rtrim(substring(record, 55,3)) as [Medica Degree]
         , rtrim(substring(record, 58,1)) as [Status]
         , rtrim(substring(record, 59,168)) as [Field8]
    from #data
    where recordtype = '2'


    select rtrim(substring(record, 1,1)) as [Record Type]
         , rtrim(substring(record, 2,9)) as [Promise Number]
         , rtrim(substring(record, 11,4)) as [Location]
         , rtrim(substring(record, 15,8)) as [Start 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]
    from #data
    where recordtype = '1'

    --possible split of proviner name
    select rtrim(substring(record, 1,1)) as [Record Type]
         , rtrim(substring(record, 2,9)) as [Promise Number]
         , rtrim(substring(record, 11,4)) as [Location]
         , rtrim(substring(record, 15,8)) as [Start Date]
         , rtrim(substring(record, 23,8)) as [End Date]
         , rtrim(substring(record, 31,25)) as [Provider Last Name]
         , rtrim(substring(record, 56,13)) as [Provider First Name]
         , rtrim(substring(record, 69,12)) as [Provider Middle Name] -- potentially even more divided) as it is 12 in size
         , 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]
    from #data
    where recordtype = '1'

    The data the op provided doesn't align as nicely as you have in your test data.  See my previous post.

    this is based on the file he supplied on his last post. https://qa.sqlservercentral.com/wp-content/uploads/2020/04/PRV414WSW_2.txt

  • April 9, 2020 at 6:11 pm

    #3741850

    frederico_fonseca wrote:

    this is based on the file he supplied on his last post. https://qa.sqlservercentral.com/wp-content/uploads/2020/04/PRV414WSW_2.txt

    Ah... gotcha... I missed that he reposted.  Thank you.  I'll take a look at this after work tonight just to see if I'd do anything different than what you've done.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • April 12, 2020 at 2:30 am

    #3742256

    Looking at what Frederico has done in code, I'd probably do it the same way IF the Promise Number and Location are unique within a row set group and that row set group is unique in the file.  If it's not, post back... I have a fix for that.

    The only other thing I might add to it is to use CONVERT to convert things like 8 digit dates to the actual DATE datatype.

    The next question is, are you having any difficulty in importing the rows into a single column (1 line per row)?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • April 13, 2020 at 3:03 pm

    #3742432

    Do I just replace the part he has the select and from with a bulk insert?

  • April 13, 2020 at 3:12 pm

    #3742433

    smattiko83 wrote:

    Do I just replace the part he has the select and from with a bulk insert?

    yes that would be a way - where I do "insert into #data .... " you could do this from a bulk insert.

    or if you prefer using SSIS you can create a perm table and use it instead - but layout of table could remain as is

    as Jeff mentioned you would also do further converts of datatypes so final table has dates, chars and numerics set as they should instead of all chars - that can be done as part of the selects + sub-strings I've shown

     

  • April 13, 2020 at 3:28 pm

    #3742434

    I get the following error:

     

    Msg 4866, Level 16, State 1, Line 8

    The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.

    Msg 7399, Level 16, State 1, Line 8

    The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

    Msg 7330, Level 16, State 2, Line 8

    Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

  • April 13, 2020 at 3:45 pm

    #3742441

    can you post here the bulk command you used please.

  • April 13, 2020 at 3:50 pm

    #3742445

    create table de_identified.dbo.test

    (recordtype varchar(3)

    ,record varchar(500)

    )

    BULK INSERT de_identified.dbo.test FROM 'C:\dident\PRV414WSW\PRV414SW.txt'

  • April 13, 2020 at 4:12 pm

    #3742455

    apologies.

    Bulk insert (done this way) requires a slightly different setup

    working example below - note that i have removed the record type from the temp table as bulk would fail because of it

    and I also added the terminator that the sample file you supplied contains (0x0A)

    and just as an example I've converted one of the fields to a date

    if object_id('tempdb..#data') is not null
    drop table #data

    create table #data
    (record varchar(500)
    )

    bulk insert #data
    from 'c:\downloads\PRV414WSW_2.txt' 
    with (ROWTERMINATOR  = '0x0a')

    select rtrim(substring(record, 1,1)) as [Record Type]
         , rtrim(substring(record, 2,9)) as [Promise ID]
         , rtrim(substring(record, 11,4)) as [Location]
         , rtrim(substring(record, 15,2)) as [Provider Type]
         , rtrim(substring(record, 17,3)) as [Specialty]
         , convert(date, rtrim(substring(record, 20,8)), 112) as [Specialty Begin] -- note that I have converted to date - may fail if not a valid date and if so a case statement or replace required
         --, rtrim(substring(record, 20,8)) as [Specialty Begin]
         , rtrim(substring(record, 28,8)) as [Specialty End]
         , rtrim(substring(record, 36,10)) as [Licens Number]
         , rtrim(substring(record, 46,8)) as [License End]
         , rtrim(substring(record, 54,1)) as [Practice Type]
         , rtrim(substring(record, 55,3)) as [Medica Degree]
         , rtrim(substring(record, 58,1)) as [Status]
         , rtrim(substring(record, 59,168)) as [Field8]
    from #data
    where record like '2%'


    select rtrim(substring(record, 1,1)) as [Record Type]
         , rtrim(substring(record, 2,9)) as [Promise Number]
         , rtrim(substring(record, 11,4)) as [Location]
         , rtrim(substring(record, 15,8)) as [Start 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]
    from #data
    where record like '1%'

  • April 13, 2020 at 4:13 pm

    #3742456

    If you're going to split the data on the way in, even if it's just two columns, you're going to need a "Format File" to do it.  If no one beats me to it, I'll try to pump out an example for you tonight after work.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • April 13, 2020 at 4:26 pm

    #3742461

    Jeff Moden wrote:

    If you're going to split the data on the way in, even if it's just two columns, you're going to need a "Format File" to do it.  If no one beats me to it, I'll try to pump out an example for you tonight after work.

     

    no need for a format file if it is a single column - and unless the file is very big (few million rows) using a temp table will work fine for most cases.(see example above)

  • April 13, 2020 at 4:57 pm

    #3742475

    Cool, it seems to work well. Two questions.

    1. How do you know to use ROWTERMINATOR = '0x0a'? I've been thrown into creating tables, dba role and have seen others but I'm not sure how to tell what to use.
    2. I've created below to join the different record types, is there a way to have the results inserted into a new table? 
      with two as
      (
      select rtrim(substring(record, 1,1)) as [Record Type]
      , rtrim(substring(record, 2,9)) as [Promise ID]
      , rtrim(substring(record, 11,4)) as [Location]
      , rtrim(substring(record, 15,2)) as [Provider Type]
      , rtrim(substring(record, 17,3)) as [Specialty]
      --, convert(date, rtrim(substring(record, 20,8)), 112) as [Specialty Begin] -- note that I have converted to date - may fail if not a valid date and if so a case statement or replace required
      , rtrim(substring(record, 20,8)) as [Specialty Begin]
      , rtrim(substring(record, 28,8)) as [Specialty End]
      , rtrim(substring(record, 36,10)) as [Licens Number]
      , rtrim(substring(record, 46,8)) as [License End]
      , rtrim(substring(record, 54,1)) as [Practice Type]
      , rtrim(substring(record, 55,3)) as [Medica Degree]
      , rtrim(substring(record, 58,1)) as [Status]
      , rtrim(substring(record, 59,168)) as [Field8]
      from DE_IDENTIFIED.dbo.test
      where record like '2%'
      )
      ,one as
      (
      select rtrim(substring(record, 1,1)) as [Record Type]
      , rtrim(substring(record, 2,9)) as [Promise Number]
      , rtrim(substring(record, 11,4)) as [Location]
      , rtrim(substring(record, 15,8)) as [Start 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]
      from DE_IDENTIFIED.dbo.test
      where record like '1%'
      )

      select one.[Record Type]
      ,one.[Promise Number]
      ,one.[Location]
      ,one.[Start Date]
      ,one.[End Date]
      ,one.[Provider Name]
      ,one.[IRS]
      ,one.[IRS Indicator]
      ,one.[Medicare Number]
      ,one.[County Code]
      ,one.[Address]
      ,one.[City]
      ,one.[State]
      ,one.[Zip]
      ,one.[Box Number]
      ,one.[Phone]
      ,one.[DEA Number]
      ,one.[UPIN]
      ,one.[NPI-MAMIS]

      ,two.[Record Type]
      ,two.[Promise ID]
      ,two.[Location]
      ,two.[Provider Type]
      ,two.[Specialty]
      --, convert(date, rtrim(substring(record, 20,8)), 112) as [Specialty Begin] -- note that I have converted to date - may fail if not a valid date and if so a case statement or replace required
      ,two.[Specialty Begin]
      ,two.[Specialty End]
      ,two.[Licens Number]
      ,two.[License End]
      ,two.[Practice Type]
      ,two.[Medica Degree]
      ,two.[Status]
      ,two.[Field8]

      from one
      left join two on one.[Promise Number] = two.[Promise ID]?

Viewing 15 posts - 16 through 30 (of 31 total)

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