NPI Data

  • smattiko83

    SSC-Addicted

    Points: 407

    Has anyone ever imported the NPI data from the CMS website? I can't seem to get it to work.

     

    https://download.cms.gov/nppes/NPI_Files.html

    • This topic was modified 1 week, 4 days ago by  smattiko83.
    Attachments:
    You must be logged in to view attached files.
  • Jeffrey Williams

    SSC Guru

    Points: 88549

    I have built an SSIS package to load the NPI data - the only issue I had was the size.  To handle that I compressed the table...

    The process is simple...truncate a staging table and load the data from the file to the staging table.  Once that is done - start a transaction, truncate the destination and load from stage to destination.

    What issues are you having?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • smattiko83

    SSC-Addicted

    Points: 407

    It won't load all of the data. Get an error. I'm trying to do a bulk insert.

    • This reply was modified 1 week, 4 days ago by  smattiko83.
  • This was removed by the editor as SPAM

  • smattiko83

    SSC-Addicted

    Points: 407

    I've uploaded my code? Did you read it?

  • Jeffrey Williams

    SSC Guru

    Points: 88549

    I don't see any code that was uploaded - it looks like one of your posts was marked as spam.

    I have no idea what

    It won't load all of the data. Get an error. I'm trying to do a bulk insert.

    means.  What is the error you are getting?

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Phil Parkin

    SSC Guru

    Points: 244589

    This reply has been reported for inappropriate content.

    Jeffrey Williams wrote:

    I don't see any code that was uploaded - it looks like one of your posts was marked as spam.

    I have no idea what

    It won't load all of the data. Get an error. I'm trying to do a bulk insert.

    means.  What is the error you are getting?

    It was my post which was marked as spam, presumably because I questioned the lack of detail being fed to us by the OP and he/she didn't like it. I imagine this one will go the same way.

    If you check the very first post, you will see a text file attachment which contains some code.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Jeffrey Williams

    SSC Guru

    Points: 88549

    Phil Parkin wrote:

    Jeffrey Williams wrote:

    I don't see any code that was uploaded - it looks like one of your posts was marked as spam.

    I have no idea what

    It won't load all of the data. Get an error. I'm trying to do a bulk insert.

    means.  What is the error you are getting?

    It was my post which was marked as spam, presumably because I questioned the lack of detail being fed to us by the OP and he/she didn't like it. I imagine this one will go the same way.

    If you check the very first post, you will see a text file attachment which contains some code.

    I missed that...but it still doesn't answer the question of what error is occurring.  My guess is probably filling a drive - either the transaction log or tempdb, but of course that is just a guess.  It could just as well be a problem with BULK INSERT itself - and how it is reading the file.

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • smattiko83

    SSC-Addicted

    Points: 407

    Sorry that guy had one of my post removed. He is a freelancer and well known on sql central

    I'm getting:

    • data conversion error(truncation)
    • OLE db provider "Bulk" for linked server "(null)"
    • Cannot fetch a row from OLE DB provider "Bulk" for linked server "(null)"
  • Jeffrey Williams

    SSC Guru

    Points: 88549

    smattiko83 wrote:

    Sorry that guy had one of my post removed. He is a freelancer and well known on sql central

    I'm getting:

     

      <li style="list-style-type: none;">

    • data conversion error(truncation)

     

      <li style="list-style-type: none;">

    • OLE db provider "Bulk" for linked server "(null)"

     

      <li style="list-style-type: none;">

    • Cannot fetch a row from OLE DB provider "Bulk" for linked server "(null)"

     

    The first error indicates an issue with the length of one of the fields - but it looks like you created your table with all varchar(255) columns except for the NPI and REPLACEMENT_NPI.  Both of these should be defined as char(10) and not integer since they are a fixed 10 digit code but shouldn't necessarily be an issue.

    This leads me to believe the issues is related to FIELDQUOTE - which was introduced in SQL Server 2017.  If you are not using 2017 or later then that parameter would not work - and if your database is not in the correct compatibility it could also be a problem.

    With that said - I have seen a lot of posts where BULK INSERT just does not handle the field quote appropriately.  Not sure what the solution to that is...since I used SSIS to load the file and it just works.

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeff Moden

    SSC Guru

    Points: 996661

    According to the documentation, several of the "fields" in the data represent up to 10 digits of numeric information.  While the max number of digits for an INT is, indeed, 10, it's not a full 10 at the most significant place.  You should use DECIMAL(10,0) for those, instead.

    Several of the columns are date fields but you're not using them as dates and you probably should.

    The documentation also clearly lays out the maximum number of characters for each field.   I strongly recommend that you get away from the VARCHAR(255) stuff that so many people end up doing because you actually do WANT import failures to occur if they include wonky data that doesn't meet their own spec.

    Also, change the VARCHAR columns that have a max length of 2 to CHAR(2).  In fact, my general recommendation is that if something is 10 or fewer characters, consider using CHAR instead of VARCHAR.

    Also, if things keep blowing up on you for length, perhaps try \ r \ n (without the spaces) for a line terminator.

    And, thank you for the awesome link for the NPI data... There are sooooooooo many awesome things I can teach folks with such a readily available data set.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996661

    p.s.  As Jeffrey Williams pointed out, if you don't have 2017 or better, the FIELDQUOTE  option isn't going to work.  Post back if that's a part of your problem... we can generate a BCP format file that will take  care of that in earlier versions.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • stephen.aa

    SSChasing Mays

    Points: 601

    OK, THIS IS RIDICULOUS!  I NEVER SIGNED UP TO FOLLOW THIS POST, YET I HAVE HUNDREDS OF EMAILS ON IT.  GET ME OFF THIS POST PLEASE!

    Steve Anderson

  • Phil Parkin

    SSC Guru

    Points: 244589

    stephen.aa wrote:

    OK, THIS IS RIDICULOUS!  I NEVER SIGNED UP TO FOLLOW THIS POST, YET I HAVE HUNDREDS OF EMAILS ON IT.  GET ME OFF THIS POST PLEASE!

    Just hit the Unsubscribe button at the top of the page.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 14 posts - 1 through 14 (of 14 total)

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