The Centers for Medicare and Medicaid (CMS) releasing the NPI database frequently.
They also made a file available for downloading.
It’s a zip file of 500MB that when unzipped expands into 5GB.
The files can be obtained from http://www.cms.gov/Regulations-and-Guidance/HIPAA-Administrative-Simplification/NationalProvIdentStand/DataDissemination.html
ZIP file contents header and data file in CSV format Header file has all the column and other csv contents all the data related to it.
These are the steps that I followed to import them in MYSQL:
— Create a table name “npidata” with below script.
[sql]
CREATE TABLE npidata (
NPI BIGINT(10) NOT NULL, PRIMARY KEY(NPI),
Entity_Type_Code TINYINT(1),
Replacement_NPI BIGINT(10),
Employer_Identification_Number_EIN VARCHAR(9),
Provider_Organization_Name_Legal_Business_Name VARCHAR(70),
Provider_Last_Name_Legal_Name VARCHAR(35),
Provider_First_Name VARCHAR(20),
Provider_Middle_Name VARCHAR(20),
Provider_Name_Prefix_Text VARCHAR(5),
Provider_Name_Suffix_Text VARCHAR(5),
Provider_Credential_Text VARCHAR(20),
Provider_Other_Organization_Name VARCHAR(70),
Provider_Other_Organization_Name_Type_Code VARCHAR(1),
Provider_Other_Last_Name VARCHAR(35),
Provider_Other_First_Name VARCHAR(20),
Provider_Other_Middle_Name VARCHAR(20),
Provider_Other_Name_Prefix_Text VARCHAR(5),
Provider_Other_Name_Suffix_Text VARCHAR(5),
Provider_Other_Credential_Text VARCHAR(20),
Provider_Other_Last_Name_Type_Code VARCHAR(1),
Provider_First_Line_Business_Mailing_Address VARCHAR(55),
Provider_Second_Line_Business_Mailing_Address VARCHAR(55),
Provider_Business_Mailing_Address_City_Name VARCHAR(40),
Provider_Business_Mailing_Address_State_Name VARCHAR(40),
Provider_Business_Mailing_Address_Postal_Code VARCHAR(20),
Provider_Business_Mailing_Address_Country_Code VARCHAR(2),
Provider_Business_Mailing_Address_Telephone_Number VARCHAR(20),
Provider_Business_Mailing_Address_Fax_Number VARCHAR(20),
Provider_First_Line_Business_Practice_Location_Address VARCHAR(55),
Provider_Second_Line_Business_Practice_Location_Address VARCHAR(55),
Provider_Business_Practice_Location_Address_City_Name VARCHAR(40),
Provider_Business_Practice_Location_Address_State_Name VARCHAR(40),
Provider_Business_Practice_Location_Address_Postal_Code VARCHAR(20),
Provider_Business_Practice_Location_Address_Country_Code VARCHAR(2),
Provider_Business_Practice_Location_Address_Telephone_Number VARCHAR(20),
Provider_Business_Practice_Location_Address_Fax_Number VARCHAR(20),
Provider_Enumeration_Date VARCHAR(10),
Last_Update_Date VARCHAR(10),
NPI_Deactivation_Reason_Code VARCHAR(2),
NPI_Deactivation_Date VARCHAR(10),
NPI_Reactivation_Date VARCHAR(10),
Provider_Gender_Code VARCHAR(1),
Authorized_Official_Last_Name VARCHAR(35),
Authorized_Official_First_Name VARCHAR(20),
Authorized_Official_Middle_Name VARCHAR(20),
Authorized_Official_Title_or_Position VARCHAR(35),
Authorized_Official_Telephone_Number VARCHAR(20),
Healthcare_Provider_Taxonomy_Code_1 VARCHAR(10),
Provider_License_Number_1 VARCHAR(20),
Provider_License_Number_State_Code_1 VARCHAR(2),
Healthcare_Provider_Primary_Taxonomy_Switch_1 VARCHAR(1),
Healthcare_Provider_Taxonomy_Code_2 VARCHAR(10),
Provider_License_Number_2 VARCHAR(20),
Provider_License_Number_State_Code_2 VARCHAR(2),
Healthcare_Provider_Primary_Taxonomy_Switch_2 VARCHAR(1),
Healthcare_Provider_Taxonomy_Code_3 VARCHAR(10),
Provider_License_Number_3 VARCHAR(20),
Provider_License_Number_State_Code_3 VARCHAR(2),
Healthcare_Provider_Primary_Taxonomy_Switch_3 VARCHAR(1),
Healthcare_Provider_Taxonomy_Code_4 VARCHAR(10),
Provider_License_Number_4 VARCHAR(20),
Provider_License_Number_State_Code_4 VARCHAR(2),
Healthcare_Provider_Primary_Taxonomy_Switch_4 VARCHAR(1),
Healthcare_Provider_Taxonomy_Code_5 VARCHAR(10),
Provider_License_Number_5 VARCHAR(20),
Provider_License_Number_State_Code_5 VARCHAR(2),
Healthcare_Provider_Primary_Taxonomy_Switch_5 VARCHAR(1),
Healthcare_Provider_Taxonomy_Code_6 VARCHAR(10),
Provider_License_Number_6 VARCHAR(20),
Provider_License_Number_State_Code_6 VARCHAR(2),
Healthcare_Provider_Primary_Taxonomy_Switch_6 VARCHAR(1),
Healthcare_Provider_Taxonomy_Code_7 VARCHAR(10),
Provider_License_Number_7 VARCHAR(20),
Provider_License_Number_State_Code_7 VARCHAR(2),
Healthcare_Provider_Primary_Taxonomy_Switch_7 VARCHAR(1),
Healthcare_Provider_Taxonomy_Code_8 VARCHAR(10),
Provider_License_Number_8 VARCHAR(20),
Provider_License_Number_State_Code_8 VARCHAR(2),
Healthcare_Provider_Primary_Taxonomy_Switch_8 VARCHAR(1),
Healthcare_Provider_Taxonomy_Code_9 VARCHAR(10),
Provider_License_Number_9 VARCHAR(20),
Provider_License_Number_State_Code_9 VARCHAR(2),
Healthcare_Provider_Primary_Taxonomy_Switch_9 VARCHAR(1),
Healthcare_Provider_Taxonomy_Code_10 VARCHAR(10),
Provider_License_Number_10 VARCHAR(20),
Provider_License_Number_State_Code_10 VARCHAR(2),
Healthcare_Provider_Primary_Taxonomy_Switch_10 VARCHAR(1),
Healthcare_Provider_Taxonomy_Code_11 VARCHAR(10),
Provider_License_Number_11 VARCHAR(20),
Provider_License_Number_State_Code_11 VARCHAR(2),
Healthcare_Provider_Primary_Taxonomy_Switch_11 VARCHAR(1),
Healthcare_Provider_Taxonomy_Code_12 VARCHAR(10),
Provider_License_Number_12 VARCHAR(20),
Provider_License_Number_State_Code_12 VARCHAR(2),
Healthcare_Provider_Primary_Taxonomy_Switch_12 VARCHAR(1),
Healthcare_Provider_Taxonomy_Code_13 VARCHAR(10),
Provider_License_Number_13 VARCHAR(20),
Provider_License_Number_State_Code_13 VARCHAR(2),
Healthcare_Provider_Primary_Taxonomy_Switch_13 VARCHAR(1),
Healthcare_Provider_Taxonomy_Code_14 VARCHAR(10),
Provider_License_Number_14 VARCHAR(20),
Provider_License_Number_State_Code_14 VARCHAR(2),
Healthcare_Provider_Primary_Taxonomy_Switch_14 VARCHAR(1),
Healthcare_Provider_Taxonomy_Code_15 VARCHAR(10),
Provider_License_Number_15 VARCHAR(20),
Provider_License_Number_State_Code_15 VARCHAR(2),
Healthcare_Provider_Primary_Taxonomy_Switch_15 VARCHAR(1),
Other_Provider_Identifier_1 VARCHAR(20),
Other_Provider_Identifier_Type_Code_1 VARCHAR(2),
Other_Provider_Identifier_State_1 VARCHAR(2),
Other_Provider_Identifier_Issuer_1 VARCHAR(80),
Other_Provider_Identifier_2 VARCHAR(20),
Other_Provider_Identifier_Type_Code_2 VARCHAR(2),
Other_Provider_Identifier_State_2 VARCHAR(2),
Other_Provider_Identifier_Issuer_2 VARCHAR(80),
Other_Provider_Identifier_3 VARCHAR(20),
Other_Provider_Identifier_Type_Code_3 VARCHAR(2),
Other_Provider_Identifier_State_3 VARCHAR(2),
Other_Provider_Identifier_Issuer_3 VARCHAR(80),
Other_Provider_Identifier_4 VARCHAR(20),
Other_Provider_Identifier_Type_Code_4 VARCHAR(2),
Other_Provider_Identifier_State_4 VARCHAR(2),
Other_Provider_Identifier_Issuer_4 VARCHAR(80),
Other_Provider_Identifier_5 VARCHAR(20),
Other_Provider_Identifier_Type_Code_5 VARCHAR(2),
Other_Provider_Identifier_State_5 VARCHAR(2),
Other_Provider_Identifier_Issuer_5 VARCHAR(80),
Other_Provider_Identifier_6 VARCHAR(20),
Other_Provider_Identifier_Type_Code_6 VARCHAR(2),
Other_Provider_Identifier_State_6 VARCHAR(2),
Other_Provider_Identifier_Issuer_6 VARCHAR(80),
Other_Provider_Identifier_7 VARCHAR(20),
Other_Provider_Identifier_Type_Code_7 VARCHAR(2),
Other_Provider_Identifier_State_7 VARCHAR(2),
Other_Provider_Identifier_Issuer_7 VARCHAR(80),
Other_Provider_Identifier_8 VARCHAR(20),
Other_Provider_Identifier_Type_Code_8 VARCHAR(2),
Other_Provider_Identifier_State_8 VARCHAR(2),
Other_Provider_Identifier_Issuer_8 VARCHAR(80),
Other_Provider_Identifier_9 VARCHAR(20),
Other_Provider_Identifier_Type_Code_9 VARCHAR(2),
Other_Provider_Identifier_State_9 VARCHAR(2),
Other_Provider_Identifier_Issuer_9 VARCHAR(80),
Other_Provider_Identifier_10 VARCHAR(20),
Other_Provider_Identifier_Type_Code_10 VARCHAR(2),
Other_Provider_Identifier_State_10 VARCHAR(2),
Other_Provider_Identifier_Issuer_10 VARCHAR(80),
Other_Provider_Identifier_11 VARCHAR(20),
Other_Provider_Identifier_Type_Code_11 VARCHAR(2),
Other_Provider_Identifier_State_11 VARCHAR(2),
Other_Provider_Identifier_Issuer_11 VARCHAR(80),
Other_Provider_Identifier_12 VARCHAR(20),
Other_Provider_Identifier_Type_Code_12 VARCHAR(2),
Other_Provider_Identifier_State_12 VARCHAR(2),
Other_Provider_Identifier_Issuer_12 VARCHAR(80),
Other_Provider_Identifier_13 VARCHAR(20),
Other_Provider_Identifier_Type_Code_13 VARCHAR(2),
Other_Provider_Identifier_State_13 VARCHAR(2),
Other_Provider_Identifier_Issuer_13 VARCHAR(80),
Other_Provider_Identifier_14 VARCHAR(20),
Other_Provider_Identifier_Type_Code_14 VARCHAR(2),
Other_Provider_Identifier_State_14 VARCHAR(2),
Other_Provider_Identifier_Issuer_14 VARCHAR(80),
Other_Provider_Identifier_15 VARCHAR(20),
Other_Provider_Identifier_Type_Code_15 VARCHAR(2),
Other_Provider_Identifier_State_15 VARCHAR(2),
Other_Provider_Identifier_Issuer_15 VARCHAR(80),
Other_Provider_Identifier_16 VARCHAR(20),
Other_Provider_Identifier_Type_Code_16 VARCHAR(2),
Other_Provider_Identifier_State_16 VARCHAR(2),
Other_Provider_Identifier_Issuer_16 VARCHAR(80),
Other_Provider_Identifier_17 VARCHAR(20),
Other_Provider_Identifier_Type_Code_17 VARCHAR(2),
Other_Provider_Identifier_State_17 VARCHAR(2),
Other_Provider_Identifier_Issuer_17 VARCHAR(80),
Other_Provider_Identifier_18 VARCHAR(20),
Other_Provider_Identifier_Type_Code_18 VARCHAR(2),
Other_Provider_Identifier_State_18 VARCHAR(2),
Other_Provider_Identifier_Issuer_18 VARCHAR(80),
Other_Provider_Identifier_19 VARCHAR(20),
Other_Provider_Identifier_Type_Code_19 VARCHAR(2),
Other_Provider_Identifier_State_19 VARCHAR(2),
Other_Provider_Identifier_Issuer_19 VARCHAR(80),
Other_Provider_Identifier_20 VARCHAR(20),
Other_Provider_Identifier_Type_Code_20 VARCHAR(2),
Other_Provider_Identifier_State_20 VARCHAR(2),
Other_Provider_Identifier_Issuer_20 VARCHAR(80)
) engine=myisam;
[/sql]
— Unzip the downloaded file to c:\Temp folder on windows.
— Here is the final SQL statement to import:
ALTER TABLE npidata DISABLE KEYS;
LOAD DATA LOCAL INFILE 'C:\\Temp\\npidata_20050523-20131013.csv'
INTO TABLE npidata FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
ALTER TABLE npidata ENABLE KEYS;
— Please wait for a while based on your machine configuration.. i.e. (5-6 minutes)
Leave a Reply