If you work in medical databases you are likely familiar with the site CMS NPI Search
I have had a few projects that need the ability to validate NPI numbers so based on data at CMS.gov I have worked out a solution to get the data into a database that I can manage.
CSVKit is a Python based application that makes parsing the 5+GB file from CMS.
This script shows how easy it is to build a table based on the csv.
csvsql -i mysql npidata_20050523-20150412FileHeader.csv > MySQL_Table_Creation.sql csvsql -i mssql npidata_20050523-20150412FileHeader.csv > MSSQL_Table_Creation.sql
DROP TABLE IF EXISTS npidata; CREATE TABLE `npidata` ( `NPI` VARCHAR(32) NOT NULL, `Entity Type Code` VARCHAR(32) NOT NULL, `Replacement NPI` VARCHAR(32) NOT NULL, `Employer Identification Number (EIN)` VARCHAR(32) NOT NULL, `Provider Organization Name (Legal Business Name)` VARCHAR(32) NOT NULL, `Provider Last Name (Legal Name)` VARCHAR(32) NOT NULL, `Provider First Name` VARCHAR(32) NOT NULL, `Provider Middle Name` VARCHAR(32) NOT NULL, `Provider Name Prefix Text` VARCHAR(32) NOT NULL, `Provider Name Suffix Text` VARCHAR(32) NOT NULL, `Provider Credential Text` VARCHAR(32) NOT NULL, `Provider Other Organization Name` VARCHAR(32) NOT NULL, `Provider Other Organization Name Type Code` VARCHAR(32) NOT NULL, `Provider Other Last Name` VARCHAR(32) NOT NULL, `Provider Other First Name` VARCHAR(32) NOT NULL, `Provider Other Middle Name` VARCHAR(32) NOT NULL, `Provider Other Name Prefix Text` VARCHAR(32) NOT NULL, `Provider Other Name Suffix Text` VARCHAR(32) NOT NULL, `Provider Other Credential Text` VARCHAR(32) NOT NULL, `Provider Other Last Name Type Code` VARCHAR(32) NOT NULL, `Provider First Line Business Mailing Address` VARCHAR(32) NOT NULL, `Provider Second Line Business Mailing Address` VARCHAR(32) NOT NULL, `Provider Business Mailing Address City Name` VARCHAR(32) NOT NULL, `Provider Business Mailing Address State Name` VARCHAR(32) NOT NULL, `Provider Business Mailing Address Postal Code` VARCHAR(32) NOT NULL, `Provider Business Mailing Address Country Code` VARCHAR(32) NOT NULL, ••••••••••••• `Provider Business Mailing Address Telephone Number` VARCHAR(32) NOT NULL, `Provider Business Mailing Address Fax Number` VARCHAR(32) NOT NULL, `Provider First Line Business Practice Location Address` VARCHAR(32) NOT NULL, `Provider Second Line Business Practice Location Address` VARCHAR(32) NOT NULL, `Provider Business Practice Location Address City Name` VARCHAR(32) NOT NULL, `Provider Business Practice Location Address State Name` VARCHAR(32) NOT NULL, `Provider Business Practice Location Address Postal Code` VARCHAR(32) NOT NULL, `Provider Business Practice Location Address Country Code` VARCHAR(32) NOT NULL, `Provider Business Practice Location Address Telephone Number` VARCHAR(32) NOT NULL, `Provider Business Practice Location Address Fax Number` VARCHAR(32) NOT NULL, `Provider Enumeration Date` VARCHAR(32) NOT NULL, `Last Update Date` VARCHAR(32) NOT NULL, `NPI Deactivation Reason Code` VARCHAR(32) NOT NULL, `NPI Deactivation Date` VARCHAR(32) NOT NULL, `NPI Reactivation Date` VARCHAR(32) NOT NULL, `Provider Gender Code` VARCHAR(32) NOT NULL, `Authorized Official Last Name` VARCHAR(32) NOT NULL, `Authorized Official First Name` VARCHAR(32) NOT NULL, `Authorized Official Middle Name` VARCHAR(32) NOT NULL, `Authorized Official Title or Position` VARCHAR(32) NOT NULL, `Authorized Official Telephone Number` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Code_1` VARCHAR(32) NOT NULL, `Provider License Number_1` VARCHAR(32) NOT NULL, `Provider License Number State Code_1` VARCHAR(32) NOT NULL, `Healthcare Provider Primary Taxonomy Switch_1` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Code_2` VARCHAR(32) NOT NULL, `Provider License Number_2` VARCHAR(32) NOT NULL, `Provider License Number State Code_2` VARCHAR(32) NOT NULL, `Healthcare Provider Primary Taxonomy Switch_2` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Code_3` VARCHAR(32) NOT NULL, `Provider License Number_3` VARCHAR(32) NOT NULL, `Provider License Number State Code_3` VARCHAR(32) NOT NULL, `Healthcare Provider Primary Taxonomy Switch_3` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Code_4` VARCHAR(32) NOT NULL, `Provider License Number_4` VARCHAR(32) NOT NULL, `Provider License Number State Code_4` VARCHAR(32) NOT NULL, `Healthcare Provider Primary Taxonomy Switch_4` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Code_5` VARCHAR(32) NOT NULL, `Provider License Number_5` VARCHAR(32) NOT NULL, `Provider License Number State Code_5` VARCHAR(32) NOT NULL, `Healthcare Provider Primary Taxonomy Switch_5` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Code_6` VARCHAR(32) NOT NULL, `Provider License Number_6` VARCHAR(32) NOT NULL, `Provider License Number State Code_6` VARCHAR(32) NOT NULL, `Healthcare Provider Primary Taxonomy Switch_6` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Code_7` VARCHAR(32) NOT NULL, `Provider License Number_7` VARCHAR(32) NOT NULL, `Provider License Number State Code_7` VARCHAR(32) NOT NULL, `Healthcare Provider Primary Taxonomy Switch_7` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Code_8` VARCHAR(32) NOT NULL, `Provider License Number_8` VARCHAR(32) NOT NULL, `Provider License Number State Code_8` VARCHAR(32) NOT NULL, `Healthcare Provider Primary Taxonomy Switch_8` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Code_9` VARCHAR(32) NOT NULL, `Provider License Number_9` VARCHAR(32) NOT NULL, `Provider License Number State Code_9` VARCHAR(32) NOT NULL, `Healthcare Provider Primary Taxonomy Switch_9` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Code_10` VARCHAR(32) NOT NULL, `Provider License Number_10` VARCHAR(32) NOT NULL, `Provider License Number State Code_10` VARCHAR(32) NOT NULL, `Healthcare Provider Primary Taxonomy Switch_10` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Code_11` VARCHAR(32) NOT NULL, `Provider License Number_11` VARCHAR(32) NOT NULL, `Provider License Number State Code_11` VARCHAR(32) NOT NULL, `Healthcare Provider Primary Taxonomy Switch_11` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Code_12` VARCHAR(32) NOT NULL, `Provider License Number_12` VARCHAR(32) NOT NULL, `Provider License Number State Code_12` VARCHAR(32) NOT NULL, `Healthcare Provider Primary Taxonomy Switch_12` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Code_13` VARCHAR(32) NOT NULL, `Provider License Number_13` VARCHAR(32) NOT NULL, `Provider License Number State Code_13` VARCHAR(32) NOT NULL, `Healthcare Provider Primary Taxonomy Switch_13` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Code_14` VARCHAR(32) NOT NULL, `Provider License Number_14` VARCHAR(32) NOT NULL, `Provider License Number State Code_14` VARCHAR(32) NOT NULL, `Healthcare Provider Primary Taxonomy Switch_14` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Code_15` VARCHAR(32) NOT NULL, `Provider License Number_15` VARCHAR(32) NOT NULL, `Provider License Number State Code_15` VARCHAR(32) NOT NULL, `Healthcare Provider Primary Taxonomy Switch_15` VARCHAR(32) NOT NULL, `Other Provider Identifier_1` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_1` VARCHAR(32) NOT NULL, `Other Provider Identifier State_1` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_1` VARCHAR(32) NOT NULL, `Other Provider Identifier_2` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_2` VARCHAR(32) NOT NULL, `Other Provider Identifier State_2` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_2` VARCHAR(32) NOT NULL, `Other Provider Identifier_3` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_3` VARCHAR(32) NOT NULL, `Other Provider Identifier State_3` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_3` VARCHAR(32) NOT NULL, `Other Provider Identifier_4` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_4` VARCHAR(32) NOT NULL, `Other Provider Identifier State_4` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_4` VARCHAR(32) NOT NULL, `Other Provider Identifier_5` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_5` VARCHAR(32) NOT NULL, `Other Provider Identifier State_5` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_5` VARCHAR(32) NOT NULL, `Other Provider Identifier_6` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_6` VARCHAR(32) NOT NULL, `Other Provider Identifier State_6` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_6` VARCHAR(32) NOT NULL, `Other Provider Identifier_7` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_7` VARCHAR(32) NOT NULL, `Other Provider Identifier State_7` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_7` VARCHAR(32) NOT NULL, `Other Provider Identifier_8` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_8` VARCHAR(32) NOT NULL, `Other Provider Identifier State_8` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_8` VARCHAR(32) NOT NULL, `Other Provider Identifier_9` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_9` VARCHAR(32) NOT NULL, `Other Provider Identifier State_9` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_9` VARCHAR(32) NOT NULL, `Other Provider Identifier_10` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_10` VARCHAR(32) NOT NULL, `Other Provider Identifier State_10` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_10` VARCHAR(32) NOT NULL, `Other Provider Identifier_11` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_11` VARCHAR(32) NOT NULL, `Other Provider Identifier State_11` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_11` VARCHAR(32) NOT NULL, `Other Provider Identifier_12` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_12` VARCHAR(32) NOT NULL, `Other Provider Identifier State_12` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_12` VARCHAR(32) NOT NULL, `Other Provider Identifier_13` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_13` VARCHAR(32) NOT NULL, `Other Provider Identifier State_13` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_13` VARCHAR(32) NOT NULL, `Other Provider Identifier_14` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_14` VARCHAR(32) NOT NULL, `Other Provider Identifier State_14` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_14` VARCHAR(32) NOT NULL, `Other Provider Identifier_15` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_15` VARCHAR(32) NOT NULL, `Other Provider Identifier State_15` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_15` VARCHAR(32) NOT NULL, `Other Provider Identifier_16` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_16` VARCHAR(32) NOT NULL, `Other Provider Identifier State_16` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_16` VARCHAR(32) NOT NULL, `Other Provider Identifier_17` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_17` VARCHAR(32) NOT NULL, `Other Provider Identifier State_17` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_17` VARCHAR(32) NOT NULL, `Other Provider Identifier_18` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_18` VARCHAR(32) NOT NULL, `Other Provider Identifier State_18` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_18` VARCHAR(32) NOT NULL, `Other Provider Identifier_19` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_19` VARCHAR(32) NOT NULL, `Other Provider Identifier State_19` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_19` VARCHAR(32) NOT NULL, `Other Provider Identifier_20` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_20` VARCHAR(32) NOT NULL, `Other Provider Identifier State_20` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_20` VARCHAR(32) NOT NULL, `Other Provider Identifier_21` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_21` VARCHAR(32) NOT NULL, `Other Provider Identifier State_21` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_21` VARCHAR(32) NOT NULL, `Other Provider Identifier_22` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_22` VARCHAR(32) NOT NULL, `Other Provider Identifier State_22` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_22` VARCHAR(32) NOT NULL, `Other Provider Identifier_23` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_23` VARCHAR(32) NOT NULL, `Other Provider Identifier State_23` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_23` VARCHAR(32) NOT NULL, `Other Provider Identifier_24` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_24` VARCHAR(32) NOT NULL, `Other Provider Identifier State_24` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_24` VARCHAR(32) NOT NULL, `Other Provider Identifier_25` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_25` VARCHAR(32) NOT NULL, `Other Provider Identifier State_25` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_25` VARCHAR(32) NOT NULL, `Other Provider Identifier_26` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_26` VARCHAR(32) NOT NULL, `Other Provider Identifier State_26` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_26` VARCHAR(32) NOT NULL, `Other Provider Identifier_27` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_27` VARCHAR(32) NOT NULL, `Other Provider Identifier State_27` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_27` VARCHAR(32) NOT NULL, `Other Provider Identifier_28` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_28` VARCHAR(32) NOT NULL, `Other Provider Identifier State_28` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_28` VARCHAR(32) NOT NULL, `Other Provider Identifier_29` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_29` VARCHAR(32) NOT NULL, `Other Provider Identifier State_29` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_29` VARCHAR(32) NOT NULL, `Other Provider Identifier_30` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_30` VARCHAR(32) NOT NULL, `Other Provider Identifier State_30` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_30` VARCHAR(32) NOT NULL, `Other Provider Identifier_31` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_31` VARCHAR(32) NOT NULL, `Other Provider Identifier State_31` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_31` VARCHAR(32) NOT NULL, `Other Provider Identifier_32` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_32` VARCHAR(32) NOT NULL, `Other Provider Identifier State_32` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_32` VARCHAR(32) NOT NULL, `Other Provider Identifier_33` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_33` VARCHAR(32) NOT NULL, `Other Provider Identifier State_33` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_33` VARCHAR(32) NOT NULL, `Other Provider Identifier_34` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_34` VARCHAR(32) NOT NULL, `Other Provider Identifier State_34` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_34` VARCHAR(32) NOT NULL, `Other Provider Identifier_35` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_35` VARCHAR(32) NOT NULL, `Other Provider Identifier State_35` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_35` VARCHAR(32) NOT NULL, `Other Provider Identifier_36` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_36` VARCHAR(32) NOT NULL, `Other Provider Identifier State_36` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_36` VARCHAR(32) NOT NULL, `Other Provider Identifier_37` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_37` VARCHAR(32) NOT NULL, `Other Provider Identifier State_37` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_37` VARCHAR(32) NOT NULL, `Other Provider Identifier_38` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_38` VARCHAR(32) NOT NULL, `Other Provider Identifier State_38` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_38` VARCHAR(32) NOT NULL, `Other Provider Identifier_39` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_39` VARCHAR(32) NOT NULL, `Other Provider Identifier State_39` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_39` VARCHAR(32) NOT NULL, `Other Provider Identifier_40` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_40` VARCHAR(32) NOT NULL, `Other Provider Identifier State_40` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_40` VARCHAR(32) NOT NULL, `Other Provider Identifier_41` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_41` VARCHAR(32) NOT NULL, `Other Provider Identifier State_41` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_41` VARCHAR(32) NOT NULL, `Other Provider Identifier_42` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_42` VARCHAR(32) NOT NULL, `Other Provider Identifier State_42` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_42` VARCHAR(32) NOT NULL, `Other Provider Identifier_43` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_43` VARCHAR(32) NOT NULL, `Other Provider Identifier State_43` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_43` VARCHAR(32) NOT NULL, `Other Provider Identifier_44` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_44` VARCHAR(32) NOT NULL, `Other Provider Identifier State_44` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_44` VARCHAR(32) NOT NULL, `Other Provider Identifier_45` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_45` VARCHAR(32) NOT NULL, `Other Provider Identifier State_45` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_45` VARCHAR(32) NOT NULL, `Other Provider Identifier_46` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_46` VARCHAR(32) NOT NULL, `Other Provider Identifier State_46` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_46` VARCHAR(32) NOT NULL, `Other Provider Identifier_47` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_47` VARCHAR(32) NOT NULL, `Other Provider Identifier State_47` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_47` VARCHAR(32) NOT NULL, `Other Provider Identifier_48` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_48` VARCHAR(32) NOT NULL, `Other Provider Identifier State_48` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_48` VARCHAR(32) NOT NULL, `Other Provider Identifier_49` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_49` VARCHAR(32) NOT NULL, `Other Provider Identifier State_49` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_49` VARCHAR(32) NOT NULL, `Other Provider Identifier_50` VARCHAR(32) NOT NULL, `Other Provider Identifier Type Code_50` VARCHAR(32) NOT NULL, `Other Provider Identifier State_50` VARCHAR(32) NOT NULL, `Other Provider Identifier Issuer_50` VARCHAR(32) NOT NULL, `Is Sole Proprietor` VARCHAR(32) NOT NULL, `Is Organization Subpart` VARCHAR(32) NOT NULL, `Parent Organization LBN` VARCHAR(32) NOT NULL, `Parent Organization TIN` VARCHAR(32) NOT NULL, `Authorized Official Name Prefix Text` VARCHAR(32) NOT NULL, `Authorized Official Name Suffix Text` VARCHAR(32) NOT NULL, `Authorized Official Credential Text` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Group_1` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Group_2` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Group_3` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Group_4` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Group_5` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Group_6` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Group_7` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Group_8` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Group_9` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Group_10` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Group_11` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Group_12` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Group_13` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Group_14` VARCHAR(32) NOT NULL, `Healthcare Provider Taxonomy Group_15` VARCHAR(32) NOT NULL );
CREATE TABLE [npidata_20050523-20150412FileHeader] ( [NPI] VARCHAR(32) NOT NULL ,[Entity Type Code] VARCHAR(32) NULL ,[Replacement NPI] VARCHAR(32) NULL ,[Employer Identification Number (EIN)] VARCHAR(32) NULL ,[Legal Business Name] VARCHAR(32) NULL ,[Provider Last Name (Legal Name)] VARCHAR(32) NOT NULL ,[Provider First Name] VARCHAR(32) NOT NULL ,[Provider Middle Name] VARCHAR(32) NULL ,[Provider Name Prefix Text] VARCHAR(32) NULL ,[Provider Name Suffix Text] VARCHAR(32) NULL ,[Provider Credential Text] VARCHAR(32) NULL ,[Provider Other Organization Name] VARCHAR(32) NULL ,[Provider Other Organization Name Type Code] VARCHAR(32) NULL ,[Provider Other Last Name] VARCHAR(32) NULL ,[Provider Other First Name] VARCHAR(32) NULL ,[Provider Other Middle Name] VARCHAR(32) NULL ,[Provider Other Name Prefix Text] VARCHAR(32) NULL ,[Provider Other Name Suffix Text] VARCHAR(32) NULL ,[Provider Other Credential Text] VARCHAR(32) NULL ,[Provider Other Last Name Type Code] VARCHAR(32) NULL ,[Provider First Line Business Mailing Address] VARCHAR(32) NULL ,[Provider Second Line Business Mailing Address] VARCHAR(32) NULL ,[Provider Business Mailing Address City Name] VARCHAR(32) NULL ,[Provider Business Mailing Address State Name] VARCHAR(32) NULL ,[Provider Business Mailing Address Postal Code] VARCHAR(32) NULL ,[Provider Business Mailing Address Country Code] VARCHAR(32) NULL ,[Provider Business Mailing Address Telephone Number] VARCHAR(32) NULL ,[Provider Business Mailing Address Fax Number] VARCHAR(32) NULL ,[Provider First Line Business Practice Location Address] VARCHAR(32) NULL ,[Provider Second Line Business Practice Location Address] VARCHAR(32) NULL ,[Provider Business Practice Location Address City Name] VARCHAR(32) NULL ,[Provider Business Practice Location Address State Name] VARCHAR(32) NULL ,[Provider Business Practice Location Address Postal Code] VARCHAR(32) NULL ,[Provider Business Practice Location Address Country Code] VARCHAR(32) NULL ,[Provider Business Practice Location Address Telephone Number] VARCHAR(32) NULL ,[Provider Business Practice Location Address Fax Number] VARCHAR(32) NULL ,[Provider Enumeration Date] VARCHAR(32) NULL ,[Last Update Date] VARCHAR(32) NULL ,[NPI Deactivation Reason Code] VARCHAR(32) NULL ,[NPI Deactivation Date] VARCHAR(32) NULL ,[NPI Reactivation Date] VARCHAR(32) NULL ,[Provider Gender Code] VARCHAR(32) NULL ,[Authorized Official Last Name] VARCHAR(32) NULL ,[Authorized Official First Name] VARCHAR(32) NULL ,[Authorized Official Middle Name] VARCHAR(32) NULL ,[Authorized Official Title or Position] VARCHAR(32) NULL ,[Authorized Official Telephone Number] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Code_1] VARCHAR(32) NULL ,[Provider License Number_1] VARCHAR(32) NULL ,[Provider License Number State Code_1] VARCHAR(32) NULL ,[Healthcare Provider Primary Taxonomy Switch_1] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Code_2] VARCHAR(32) NULL ,[Provider License Number_2] VARCHAR(32) NULL ,[Provider License Number State Code_2] VARCHAR(32) NULL ,[Healthcare Provider Primary Taxonomy Switch_2] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Code_3] VARCHAR(32) NULL ,[Provider License Number_3] VARCHAR(32) NULL ,[Provider License Number State Code_3] VARCHAR(32) NULL ,[Healthcare Provider Primary Taxonomy Switch_3] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Code_4] VARCHAR(32) NULL ,[Provider License Number_4] VARCHAR(32) NULL ,[Provider License Number State Code_4] VARCHAR(32) NULL ,[Healthcare Provider Primary Taxonomy Switch_4] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Code_5] VARCHAR(32) NULL ,[Provider License Number_5] VARCHAR(32) NULL ,[Provider License Number State Code_5] VARCHAR(32) NULL ,[Healthcare Provider Primary Taxonomy Switch_5] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Code_6] VARCHAR(32) NULL ,[Provider License Number_6] VARCHAR(32) NULL ,[Provider License Number State Code_6] VARCHAR(32) NULL ,[Healthcare Provider Primary Taxonomy Switch_6] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Code_7] VARCHAR(32) NULL ,[Provider License Number_7] VARCHAR(32) NULL ,[Provider License Number State Code_7] VARCHAR(32) NULL ,[Healthcare Provider Primary Taxonomy Switch_7] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Code_8] VARCHAR(32) NULL ,[Provider License Number_8] VARCHAR(32) NULL ,[Provider License Number State Code_8] VARCHAR(32) NULL ,[Healthcare Provider Primary Taxonomy Switch_8] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Code_9] VARCHAR(32) NULL ,[Provider License Number_9] VARCHAR(32) NULL ,[Provider License Number State Code_9] VARCHAR(32) NULL ,[Healthcare Provider Primary Taxonomy Switch_9] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Code_10] VARCHAR(32) NULL ,[Provider License Number_10] VARCHAR(32) NULL ,[Provider License Number State Code_10] VARCHAR(32) NULL ,[Healthcare Provider Primary Taxonomy Switch_10] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Code_11] VARCHAR(32) NULL ,[Provider License Number_11] VARCHAR(32) NULL ,[Provider License Number State Code_11] VARCHAR(32) NULL ,[Healthcare Provider Primary Taxonomy Switch_11] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Code_12] VARCHAR(32) NULL ,[Provider License Number_12] VARCHAR(32) NULL ,[Provider License Number State Code_12] VARCHAR(32) NULL ,[Healthcare Provider Primary Taxonomy Switch_12] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Code_13] VARCHAR(32) NULL ,[Provider License Number_13] VARCHAR(32) NULL ,[Provider License Number State Code_13] VARCHAR(32) NULL ,[Healthcare Provider Primary Taxonomy Switch_13] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Code_14] VARCHAR(32) NULL ,[Provider License Number_14] VARCHAR(32) NULL ,[Provider License Number State Code_14] VARCHAR(32) NULL ,[Healthcare Provider Primary Taxonomy Switch_14] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Code_15] VARCHAR(32) NULL ,[Provider License Number_15] VARCHAR(32) NULL ,[Provider License Number State Code_15] VARCHAR(32) NULL ,[Healthcare Provider Primary Taxonomy Switch_15] VARCHAR(32) NULL ,[Other Provider Identifier_1] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_1] VARCHAR(32) NULL ,[Other Provider Identifier State_1] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_1] VARCHAR(32) NULL ,[Other Provider Identifier_2] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_2] VARCHAR(32) NULL ,[Other Provider Identifier State_2] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_2] VARCHAR(32) NULL ,[Other Provider Identifier_3] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_3] VARCHAR(32) NULL ,[Other Provider Identifier State_3] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_3] VARCHAR(32) NULL ,[Other Provider Identifier_4] VARCHAR(32) NULL" collapse="true"] ,[Other Provider Identifier Type Code_4] VARCHAR(32) NULL ,[Other Provider Identifier State_4] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_4] VARCHAR(32) NULL ,[Other Provider Identifier_5] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_5] VARCHAR(32) NULL ,[Other Provider Identifier State_5] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_5] VARCHAR(32) NULL ,[Other Provider Identifier_6] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_6] VARCHAR(32) NULL ,[Other Provider Identifier State_6] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_6] VARCHAR(32) NULL ,[Other Provider Identifier_7] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_7] VARCHAR(32) NULL ,[Other Provider Identifier State_7] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_7] VARCHAR(32) NULL ,[Other Provider Identifier_8] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_8] VARCHAR(32) NULL ,[Other Provider Identifier State_8] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_8] VARCHAR(32) NULL ,[Other Provider Identifier_9] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_9] VARCHAR(32) NULL ,[Other Provider Identifier State_9] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_9] VARCHAR(32) NULL ,[Other Provider Identifier_10] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_10] VARCHAR(32) NULL ,[Other Provider Identifier State_10] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_10] VARCHAR(32) NULL ,[Other Provider Identifier_11] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_11] VARCHAR(32) NULL ,[Other Provider Identifier State_11] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_11] VARCHAR(32) NULL ,[Other Provider Identifier_12] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_12] VARCHAR(32) NULL ,[Other Provider Identifier State_12] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_12] VARCHAR(32) NULL ,[Other Provider Identifier_13] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_13] VARCHAR(32) NULL ,[Other Provider Identifier State_13] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_13] VARCHAR(32) NULL ,[Other Provider Identifier_14] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_14] VARCHAR(32) NULL ,[Other Provider Identifier State_14] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_14] VARCHAR(32) NULL ,[Other Provider Identifier_15] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_15] VARCHAR(32) NULL ,[Other Provider Identifier State_15] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_15] VARCHAR(32) NULL ,[Other Provider Identifier_16] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_16] VARCHAR(32) NULL ,[Other Provider Identifier State_16] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_16] VARCHAR(32) NULL ,[Other Provider Identifier_17] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_17] VARCHAR(32) NULL ,[Other Provider Identifier State_17] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_17] VARCHAR(32) NULL ,[Other Provider Identifier_18] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_18] VARCHAR(32) NULL ,[Other Provider Identifier State_18] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_18] VARCHAR(32) NULL ,[Other Provider Identifier_19] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_19] VARCHAR(32) NULL ,[Other Provider Identifier State_19] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_19] VARCHAR(32) NULL ,[Other Provider Identifier_20] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_20] VARCHAR(32) NULL ,[Other Provider Identifier State_20] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_20] VARCHAR(32) NULL ,[Other Provider Identifier_21] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_21] VARCHAR(32) NULL ,[Other Provider Identifier State_21] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_21] VARCHAR(32) NULL ,[Other Provider Identifier_22] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_22] VARCHAR(32) NULL ,[Other Provider Identifier State_22] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_22] VARCHAR(32) NULL ,[Other Provider Identifier_23] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_23] VARCHAR(32) NULL ,[Other Provider Identifier State_23] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_23] VARCHAR(32) NULL ,[Other Provider Identifier_24] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_24] VARCHAR(32) NULL ,[Other Provider Identifier State_24] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_24] VARCHAR(32) NULL ,[Other Provider Identifier_25] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_25] VARCHAR(32) NULL ,[Other Provider Identifier State_25] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_25] VARCHAR(32) NULL ,[Other Provider Identifier_26] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_26] VARCHAR(32) NULL ,[Other Provider Identifier State_26] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_26] VARCHAR(32) NULL ,[Other Provider Identifier_27] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_27] VARCHAR(32) NULL ,[Other Provider Identifier State_27] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_27] VARCHAR(32) NULL ,[Other Provider Identifier_28] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_28] VARCHAR(32) NULL ,[Other Provider Identifier State_28] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_28] VARCHAR(32) NULL ,[Other Provider Identifier_29] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_29] VARCHAR(32) NULL ,[Other Provider Identifier State_29] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_29] VARCHAR(32) NULL ,[Other Provider Identifier_30] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_30] VARCHAR(32) NULL ,[Other Provider Identifier State_30] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_30] VARCHAR(32) NULL ,[Other Provider Identifier_31] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_31] VARCHAR(32) NULL ,[Other Provider Identifier State_31] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_31] VARCHAR(32) NULL ,[Other Provider Identifier_32] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_32] VARCHAR(32) NULL ,[Other Provider Identifier State_32] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_32] VARCHAR(32) NULL ,[Other Provider Identifier_33] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_33] VARCHAR(32) NULL ,[Other Provider Identifier State_33] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_33] VARCHAR(32) NULL ,[Other Provider Identifier_34] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_34] VARCHAR(32) NULL ,[Other Provider Identifier State_34] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_34] VARCHAR(32) NULL ,[Other Provider Identifier_35] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_35] VARCHAR(32) NULL ,[Other Provider Identifier State_35] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_35] VARCHAR(32) NULL ,[Other Provider Identifier_36] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_36] VARCHAR(32) NULL ,[Other Provider Identifier State_36] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_36] VARCHAR(32) NULL ,[Other Provider Identifier_37] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_37] VARCHAR(32) NULL ,[Other Provider Identifier State_37] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_37] VARCHAR(32) NULL ,[Other Provider Identifier_38] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_38] VARCHAR(32) NULL ,[Other Provider Identifier State_38] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_38] VARCHAR(32) NULL ,[Other Provider Identifier_39] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_39] VARCHAR(32) NULL ,[Other Provider Identifier State_39] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_39] VARCHAR(32) NULL ,[Other Provider Identifier_40] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_40] VARCHAR(32) NULL ,[Other Provider Identifier State_40] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_40] VARCHAR(32) NULL ,[Other Provider Identifier_41] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_41] VARCHAR(32) NULL ,[Other Provider Identifier State_41] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_41] VARCHAR(32) NULL ,[Other Provider Identifier_42] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_42] VARCHAR(32) NULL ,[Other Provider Identifier State_42] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_42] VARCHAR(32) NULL ,[Other Provider Identifier_43] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_43] VARCHAR(32) NULL ,[Other Provider Identifier State_43] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_43] VARCHAR(32) NULL ,[Other Provider Identifier_44] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_44] VARCHAR(32) NULL ,[Other Provider Identifier State_44] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_44] VARCHAR(32) NULL ,[Other Provider Identifier_45] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_45] VARCHAR(32) NULL ,[Other Provider Identifier State_45] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_45] VARCHAR(32) NULL ,[Other Provider Identifier_46] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_46] VARCHAR(32) NULL ,[Other Provider Identifier State_46] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_46] VARCHAR(32) NULL ,[Other Provider Identifier_47] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_47] VARCHAR(32) NULL ,[Other Provider Identifier State_47] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_47] VARCHAR(32) NULL ,[Other Provider Identifier_48] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_48] VARCHAR(32) NULL ,[Other Provider Identifier State_48] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_48] VARCHAR(32) NULL ,[Other Provider Identifier_49] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_49] VARCHAR(32) NULL ,[Other Provider Identifier State_49] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_49] VARCHAR(32) NULL ,[Other Provider Identifier_50] VARCHAR(32) NULL ,[Other Provider Identifier Type Code_50] VARCHAR(32) NULL ,[Other Provider Identifier State_50] VARCHAR(32) NULL ,[Other Provider Identifier Issuer_50] VARCHAR(32) NULL ,[Is Sole Proprietor] VARCHAR(32) NULL ,[Is Organization Subpart] VARCHAR(32) NULL ,[Parent Organization LBN] VARCHAR(32) NULL ,[Parent Organization TIN] VARCHAR(32) NULL ,[Authorized Official Name Prefix Text] VARCHAR(32) NULL ,[Authorized Official Name Suffix Text] VARCHAR(32) NULL ,[Authorized Official Credential Text] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Group_1] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Group_2] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Group_3] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Group_4] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Group_5] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Group_6] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Group_7] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Group_8] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Group_9] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Group_10] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Group_11] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Group_12] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Group_13] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Group_14] VARCHAR(32) NULL ,[Healthcare Provider Taxonomy Group_15] VARCHAR(32) NULL );
Problem with the CSVKit method is that all fields are marked as VARCHAR(32) while that allows you to work with the data still it doesn’t look/work as nicely as if you dive into the table.
Based on GitHub (https://github.com/freemed/freemed) I like this table creation script more as it is cleaner. I have removed the columns that typically are not used/needed.
DROP TABLE IF EXISTS npi;
CREATE TABLE npi (
id CHAR (12) NOT NULL UNIQUE,
entity_type_code ENUM ('1', '2') NOT NULL,
replacement_npi CHAR (12),
employer_identification_number VARCHAR (9),
provider_organization_name VARCHAR (200),
provider_last_name VARCHAR (100),
provider_first_name VARCHAR (100),
provider_middle_name VARCHAR (100),
provider_name_prefix_text VARCHAR (7),
provider_name_suffix_text VARCHAR (7),
provider_credential_text VARCHAR (100),
provider_other_organization_name VARCHAR (200),
provider_other_organization_name_type_code ENUM ('1', '2', '3', '4', '5'),
provider_other_last_name VARCHAR (100),
provider_other_first_name VARCHAR (100),
provider_other_middle_name VARCHAR (100),
provider_other_name_prefix_text VARCHAR (7),
provider_other_name_suffix_text VARCHAR (7),
provider_other_credential_text VARCHAR (100),
provider_other_last_name_type_code ENUM ('1', '2', '3', '4', '5'),
provider_first_line_business_mailing_address VARCHAR (200),
provider_second_line_business_mailing_address VARCHAR (200),
provider_business_mailing_address_city_name VARCHAR (100),
provider_business_mailing_address_state_name CHAR (2),
provider_business_mailing_address_postal_code CHAR (6),
provider_business_mailing_address_country_code CHAR(2) NOT NULL DEFAULT 'US',
provider_business_mailing_address_telephone_number VARCHAR (30),
provider_business_mailing_address_fax_number VARCHAR (30),
provider_first_line_business_practice_location_address VARCHAR (200),
provider_second_line_business_practice_location_address VARCHAR (200),
provider_business_practice_location_address_city_name VARCHAR (100),
provider_business_practice_location_address_state_name CHAR (2),
provider_business_practice_location_address_postal_code CHAR (10),
provider_business_practice_location_address_country_code CHAR(2) NOT NULL DEFAULT 'US',
provider_business_practice_location_address_telephone_number VARCHAR (30),
provider_business_practice_location_address_fax_number VARCHAR (30),
provider_enumeration_date CHAR (10),
last_update_date CHAR (10),
npi_deactivation_reason_code ENUM ('DT', 'DB', 'FR', 'OT'),
npi_deactivation_date CHAR (10),
npi_reactivation_date CHAR (10),
provider_gender_code ENUM ('M', 'F'),
authorized_official_last_name VARCHAR (100),
authorized_official_first_name VARCHAR (100),
authorized_official_middle_name VARCHAR (100),
authorized_official_title_or_position VARCHAR (100),
authorized_official_telephone_number VARCHAR (30),
PRIMARY KEY (id),
INDEX (provider_organization_name),
INDEX (
provider_last_name, provider_first_name
)
);
IF OBJECT_ID('npi', 'U') IS NOT NULL
DROP TABLE npi;
CREATE TABLE npi (
id CHAR(12) NOT NULL
,entity_type_code INT NOT NULL
,replacement_npi CHAR(12) NULL
,employer_identification_number VARCHAR(9) NULL
,provider_organization_name VARCHAR(200) NULL
,provider_last_name VARCHAR(100) NULL
,provider_first_name VARCHAR(100) NULL
,provider_middle_name VARCHAR(100) NULL
,provider_name_prefix_text VARCHAR(7) NULL
,provider_name_suffix_text VARCHAR(7) NULL
,provider_credential_text VARCHAR(100) NULL
,provider_other_organization_name VARCHAR(200) NULL
,provider_other_organization_name_type_code INT
,provider_other_last_name VARCHAR(100) NULL
,provider_other_first_name VARCHAR(100) NULL
,provider_other_middle_name VARCHAR(100) NULL
,provider_other_name_prefix_text VARCHAR(7) NULL
,provider_other_name_suffix_text VARCHAR(7) NULL
,provider_other_credential_text VARCHAR(100) NULL
,provider_other_last_name_type_code INT
,provider_first_line_business_mailing_address VARCHAR(200) NULL
,provider_second_line_business_mailing_address VARCHAR(200) NULL
,provider_business_mailing_address_city_name VARCHAR(100) NULL
,provider_business_mailing_address_state_name CHAR(2) NULL
,provider_business_mailing_address_postal_code CHAR(6) NULL
,provider_business_mailing_address_country_code CHAR(2) NOT NULL DEFAULT 'US'
,provider_business_mailing_address_telephone_number VARCHAR(30) NULL
,provider_business_mailing_address_fax_number VARCHAR(30) NULL
,provider_first_line_business_practice_location_address VARCHAR(200) NULL
,provider_second_line_business_practice_location_address VARCHAR(200) NULL
,provider_business_practice_location_address_city_name VARCHAR(100) NULL
,provider_business_practice_location_address_state_name CHAR(2) NULL
,provider_business_practice_location_address_postal_code CHAR(10) NULL
,provider_business_practice_location_address_country_code CHAR(2) NOT NULL DEFAULT 'US'
,provider_business_practice_location_address_telephone_number VARCHAR(30) NULL
,provider_business_practice_location_address_fax_number VARCHAR(30) NULL
,provider_enumeration_date CHAR(10) NULL
,last_update_date CHAR(10) NULL
,npi_deactivation_reason_code VARCHAR(2) NULL
,npi_deactivation_date CHAR(10) NULL
,npi_reactivation_date CHAR(10) NULL
,provider_gender_code VARCHAR(1) NULL
,authorized_official_last_name VARCHAR(100) NULL
,authorized_official_first_name VARCHAR(100) NULL
,authorized_official_middle_name VARCHAR(100) NULL
,authorized_official_title_or_position VARCHAR(100) NULL
,authorized_official_telephone_number VARCHAR(30) NULL
,CONSTRAINT [PK_npi] PRIMARY KEY CLUSTERED ([id] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY];
Using CSVKit, trim down the columns in the npidata csv
csvcut -c 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47 npidata_20050523-20150412.csv > npitrim.csv
Once you have trimmed the CSV run the import script below.
USE NPI; TRUNCATE TABLE npi; LOAD DATA INFILE '/host/Users/David/Desktop/npidata.csv' INTO TABLE npi FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'rn' IGNORE 1 LINES( npi, entity_type_code, replacement_npi, employer_identification_number, provider_organization_name, provider_last_name, provider_first_name, provider_middle_name, provider_name_prefix_text, provider_name_suffix_text, provider_credential_text, provider_other_organization_name, provider_other_organization_name_type_code, provider_other_last_name, provider_other_first_name, provider_other_middle_name, provider_other_name_prefix_text, provider_other_name_suffix_text, provider_other_credential_text, provider_other_last_name_type_code, provider_first_line_business_mailing_address, provider_second_line_business_mailing_address, provider_business_mailing_address_city_name, provider_business_mailing_address_state_name, provider_business_mailing_address_postal_code, provider_business_mailing_address_country_code, provider_business_mailing_address_telephone_number, provider_business_mailing_address_fax_number, provider_first_line_business_practice_location_address, provider_second_line_business_practice_location_address, provider_business_practice_location_address_city_name, provider_business_practice_location_address_state_name, provider_business_practice_location_address_postal_code, provider_business_practice_location_address_country_code, provider_business_practice_location_address_telephone_number, provider_business_practice_location_address_fax_number, provider_enumeration_date, last_update_date, npi_deactivation_reason_code, npi_deactivation_date, npi_reactivation_date, provider_gender_code, authorized_official_last_name, authorized_official_first_name, authorized_official_middle_name, authorized_official_title_or_position, authorized_official_telephone_number);
Data Cleanup
UPDATE [WebMetrics].[dbo].[npi] SET [id] = ltrim(rtrim([id])), [entity_type_code] = ltrim(rtrim([entity_type_code])), [replacement_npi] = ltrim(rtrim([replacement_npi])), [employer_identification_number] = ltrim(rtrim([employer_identification_number])), [provider_organization_name] = ltrim(rtrim([provider_organization_name])), [provider_last_name] = ltrim(rtrim([provider_last_name])), [provider_first_name] = ltrim(rtrim([provider_first_name])), [provider_middle_name] = ltrim(rtrim([provider_middle_name])), [provider_name_prefix_text] = ltrim(rtrim([provider_name_prefix_text])), [provider_name_suffix_text] = ltrim(rtrim([provider_name_suffix_text])), [provider_credential_text] = ltrim(rtrim([provider_credential_text])), [provider_other_organization_name] = ltrim(rtrim([provider_other_organization_name])), [provider_other_organization_name_type_code] = ltrim(rtrim([provider_other_organization_name_type_code])), [provider_other_last_name] = ltrim(rtrim([provider_other_last_name])), [provider_other_first_name] = ltrim(rtrim([provider_other_first_name])), [provider_other_middle_name] = ltrim(rtrim([provider_other_middle_name])), [provider_other_name_prefix_text] = ltrim(rtrim([provider_other_name_prefix_text])), [provider_other_name_suffix_text] = ltrim(rtrim([provider_other_name_suffix_text])), [provider_other_credential_text] = ltrim(rtrim([provider_other_credential_text])), [provider_other_last_name_type_code] = ltrim(rtrim([provider_other_last_name_type_code])), [provider_first_line_business_mailing_address] = ltrim(rtrim([provider_first_line_business_mailing_address])), [provider_second_line_business_mailing_address] = ltrim(rtrim([provider_second_line_business_mailing_address])), [provider_business_mailing_address_city_name] = ltrim(rtrim([provider_business_mailing_address_city_name])), [provider_business_mailing_address_state_name] = ltrim(rtrim([provider_business_mailing_address_state_name])), [provider_business_mailing_address_postal_code] = ltrim(rtrim([provider_business_mailing_address_postal_code])), [provider_business_mailing_address_country_code] = ltrim(rtrim([provider_business_mailing_address_country_code])), [provider_business_mailing_address_telephone_number] = ltrim(rtrim([provider_business_mailing_address_telephone_number])), [provider_business_mailing_address_fax_number] = ltrim(rtrim([provider_business_mailing_address_fax_number])), [provider_first_line_business_practice_location_address] = ltrim(rtrim([provider_first_line_business_practice_location_address])), [provider_second_line_business_practice_location_address] = ltrim(rtrim([provider_second_line_business_practice_location_address])), [provider_business_practice_location_address_city_name] = ltrim(rtrim([provider_business_practice_location_address_city_name])), [provider_business_practice_location_address_state_name] = ltrim(rtrim([provider_business_practice_location_address_state_name])), [provider_business_practice_location_address_postal_code] = ltrim(rtrim([provider_business_practice_location_address_postal_code])), [provider_business_practice_location_address_country_code] = ltrim(rtrim([provider_business_practice_location_address_country_code])), [provider_business_practice_location_address_telephone_number] = ltrim(rtrim([provider_business_practice_location_address_telephone_number])), [provider_business_practice_location_address_fax_number] = ltrim(rtrim([provider_business_practice_location_address_fax_number])), [provider_enumeration_date] = ltrim(rtrim([provider_enumeration_date])), [last_update_date] = ltrim(rtrim([last_update_date])), [npi_deactivation_reason_code] = ltrim(rtrim([npi_deactivation_reason_code])), [npi_deactivation_date] = ltrim(rtrim([npi_deactivation_date])), [npi_reactivation_date] = ltrim(rtrim([npi_reactivation_date])), [provider_gender_code] = ltrim(rtrim([provider_gender_code])), [authorized_official_last_name] = ltrim(rtrim([authorized_official_last_name])), [authorized_official_first_name] = ltrim(rtrim([authorized_official_first_name])), [authorized_official_middle_name] = ltrim(rtrim([authorized_official_middle_name])), [authorized_official_title_or_position] = ltrim(rtrim([authorized_official_title_or_position])), [authorized_official_telephone_number] = ltrim(rtrim([authorized_official_telephone_number]));
References:
https://raw.githubusercontent.com/freemed/freemed/master/data/schema/mysql/npi.sql
Originally Posted on May 7, 2015
Last Updated on October 26, 2015
Last Updated on October 26, 2015
All information on this site is shared with the intention to help. Before any source code or program is ran on a production (non-development) system it is suggested you test it and fully understand what it is doing not just what it appears it is doing. I accept no responsibility for any damage you may do with this code.