This file contains code and notes to accompany the npidatasource.com .CSV files.
Creation Date: 01/01/2022
Update Date: 12/29/2022

1. Notes
	a. File Specifications: The main .csv file is a comma delimited, text qualified ("data") file. Has header record. The additional practice locations file is a comma delimited, text qualified ("data") file. Has header record. Both Encoding UTF-8
	b. Files that contain Zipcodes or Phone Numbers that start with a zero must be imported into Excel. This is an Excel issue, please follow this link for directions for importing: https://support.microsoft.com/en-us/office/import-data-from-a-csv-html-or-text-file-b62efe49-4d5b-4429-b788-e1211b5e90f6
		
2. Create tables scripts (if you are going to import into a database).
	a. Sql-Server
	
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[NPI_CSV](
  [NPI] [bigint] NOT NULL,
  [EntityType] [int] NULL,
  [EntityDescription] [varchar](50) NULL,
  [EnumerationDate] [date] NULL,
  [UpdateDate] [date] NULL,
  [OrganizationName] [varchar](80) NULL,
  [OtherOrganizationName] [varchar](80) NULL,
  [LegalBusinessName] [varchar](80) NULL,
  [NamePrefix] [varchar](8) NULL,
  [FirstName] [varchar](32) NULL,
  [MiddleName] [varchar](32) NULL,
  [LastName] [varchar](64) NULL,
  [Suffix] [varchar](8) NULL,
  [CredentialText] [varchar](32) NULL,
  [Proper_Name] [varchar](300) NULL,
  [SoleOwner] [varchar](1) NULL,
  [SubPart] [varchar](1) NULL,
  [Office_Contact_Title] [varchar](64) NULL,
  [Office_Contact_NamePrefix] [varchar](50) NULL,
  [Office_Contact_FirstName] [varchar](50) NULL,
  [Office_Contact_MiddleName] [varchar](50) NULL,
  [Office_Contact_LastName] [varchar](64) NULL,
  [Office_Contact_NameSuffix] [varchar](50) NULL,
  [Office_Contact_Telephone] [varchar](50) NULL,
  [Office_Contact_Credential] [varchar](80) NULL,
  [GroupName] [varchar](50) NULL,
  [BUS_Address1] [varchar](64) NULL,
  [BUS_Address2] [varchar](64) NULL,
  [BUS_City] [varchar](64) NULL,
  [BUS_State] [varchar](64) NULL,
  [BUS_PostalCode] [varchar](24) NULL,
  [PRAC_Address1] [varchar](64) NULL,
  [PRAC_Address2] [varchar](64) NULL,
  [PRAC_City] [varchar](64) NULL,
  [PRAC_State] [varchar](64) NULL,
  [PRAC_PostalCode] [varchar](24) NULL,
  [BUS_Voice] [varchar](24) NULL,
  [BUS_Fax] [varchar](24) NULL,
  [PRAC_Voice] [varchar](24) NULL,
  [PRAC_Fax] [varchar](24) NULL,
  [Gender] [varchar](1) NULL,
  [PECOS_Registered] [bit] NULL,
  [PECOS_PART_B] [bit] NULL,
  [PECOS_HHA] [bit] NULL,
  [PECOS_DME] [bit] NULL,
  [PECOS_PMD] [bit] NULL,
  [TAXONOMY] [varchar](400) NOT NULL,
  [TAXONOMY_Group_Classification] [varchar](600) NOT NULL,
  [TAXONOMY_Specialization] [varchar](500) NULL,
  [TAXONOMY_CODE] [varchar](10) NULL,
  [TAXO_IS_PRIMARY] [varchar](1) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NPI_CSV_ADDITIONAL_PRAC_ADDRESSES](
  [NPI] [bigint] NOT NULL,
  [EntityType] [int] NULL,
  [PROPER_NAME] [varchar](300) NULL,
  [PRAC_Address1] [varchar](64) NULL,
  [PRAC_Address2] [varchar](64) NULL,
  [PRAC_City] [varchar](64) NULL,
  [PRAC_State] [varchar](64) NULL,
  [PRAC_PostalCode] [varchar](24) NULL,
  [PRAC_Voice] [varchar](24) NULL,
  [PRAC_Fax] [varchar](24) NULL
) ON [PRIMARY]
GO



	b. MySql
	
CREATE TABLE `npi_csv` (
  `NPI` bigint(20) NOT NULL,
  `EntityType` int(11) DEFAULT NULL,
  `EntityDescription` varchar(50) DEFAULT NULL,
  `EnumerationDate` date DEFAULT NULL,
  `UpdateDate` date DEFAULT NULL,
  `OrganizationName` varchar(80) DEFAULT NULL,
  `OtherOrganizationName` varchar(80) DEFAULT NULL,
  `LegalBusinessName` varchar(80) DEFAULT NULL,
  `NamePrefix` varchar(8) DEFAULT NULL,
  `FirstName` varchar(32) DEFAULT NULL,
  `MiddleName` varchar(32) DEFAULT NULL,
  `LastName` varchar(64) DEFAULT NULL,
  `Suffix` varchar(8) DEFAULT NULL,
  `CredentialText` varchar(32) DEFAULT NULL,
  `Proper_Name` varchar(300) DEFAULT NULL,
  `SoleOwner` varchar(1) DEFAULT NULL,
  `SubPart` varchar(1) DEFAULT NULL,
  `Office_Contact_Title` varchar(64) DEFAULT NULL,
  `Office_Contact_NamePrefix` varchar(50) DEFAULT NULL,
  `Office_Contact_FirstName` varchar(50) DEFAULT NULL,
  `Office_Contact_MiddleName` varchar(50) DEFAULT NULL,
  `Office_Contact_LastName` varchar(64) DEFAULT NULL,
  `Office_Contact_NameSuffix` varchar(50) DEFAULT NULL,
  `Office_Contact_Telephone` varchar(50) DEFAULT NULL,
  `Office_Contact_Credential` varchar(80) DEFAULT NULL,
  `GroupName` varchar(50) DEFAULT NULL,
  `BUS_Address1` varchar(64) DEFAULT NULL,
  `BUS_Address2` varchar(64) DEFAULT NULL,
  `BUS_City` varchar(64) DEFAULT NULL,
  `BUS_State` varchar(64) DEFAULT NULL,
  `BUS_PostalCode` varchar(24) DEFAULT NULL,
  `PRAC_Address1` varchar(64) DEFAULT NULL,
  `PRAC_Address2` varchar(64) DEFAULT NULL,
  `PRAC_City` varchar(64) DEFAULT NULL,
  `PRAC_State` varchar(64) DEFAULT NULL,
  `PRAC_PostalCode` varchar(24) DEFAULT NULL,
  `BUS_Voice` varchar(24) DEFAULT NULL,
  `BUS_Fax` varchar(24) DEFAULT NULL,
  `PRAC_Voice` varchar(24) DEFAULT NULL,
  `PRAC_Fax` varchar(24) DEFAULT NULL,
  `Gender` varchar(1) DEFAULT NULL,
  `PECOS_Registered` tinyint(1) DEFAULT NULL,
  `PECOS_PART_B` tinyint(1) DEFAULT NULL,
  `PECOS_HHA` tinyint(1) DEFAULT NULL,
  `PECOS_DME` tinyint(1) DEFAULT NULL,
  `PECOS_PMD` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `npi_csv_additional_prac_addresses` (
  `NPI` bigint(20) NOT NULL,
  `EntityType` int(11) DEFAULT NULL,
  `PROPER_NAME` varchar(300) DEFAULT NULL,
  `PRAC_Address1` varchar(64) DEFAULT NULL,
  `PRAC_Address2` varchar(64) DEFAULT NULL,
  `PRAC_City` varchar(64) DEFAULT NULL,
  `PRAC_State` varchar(64) DEFAULT NULL,
  `PRAC_PostalCode` varchar(24) DEFAULT NULL,
  `PRAC_Voice` varchar(24) DEFAULT NULL,
  `PRAC_Fax` varchar(24) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;