-
Notifications
You must be signed in to change notification settings - Fork 6
/
create_npi_db.sh
executable file
·364 lines (356 loc) · 16.3 KB
/
create_npi_db.sh
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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
#!/bin/sh
# ./create_npi_db.sh [USERNAME] [DATABASE] [NPI-DATA] [TAXONOMY-DATA]
psql -U $1 -d $2 << EOF
DROP TABLE IF EXISTS npi;
CREATE TABLE npi
(
NPI integer,
Entity_Type_Code integer,
Replacement_NPI integer,
EIN varchar(9),
Provider_Organization_Name varchar(70),
Provider_Last_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 integer,
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 date,
Last_Update_Date date,
NPI_Deactivation_Reason_Code varchar(2),
NPI_Deactivation_Date date,
NPI_Reactivation_Date date,
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),
Other_Provider_Identifier_21 varchar(20),
Other_Provider_Identifier_Type_Code_21 varchar(2),
Other_Provider_Identifier_State_21 varchar(2),
Other_Provider_Identifier_Issuer_21 varchar(80),
Other_Provider_Identifier_22 varchar(20),
Other_Provider_Identifier_Type_Code_22 varchar(2),
Other_Provider_Identifier_State_22 varchar(2),
Other_Provider_Identifier_Issuer_22 varchar(80),
Other_Provider_Identifier_23 varchar(20),
Other_Provider_Identifier_Type_Code_23 varchar(2),
Other_Provider_Identifier_State_23 varchar(2),
Other_Provider_Identifier_Issuer_23 varchar(80),
Other_Provider_Identifier_24 varchar(20),
Other_Provider_Identifier_Type_Code_24 varchar(2),
Other_Provider_Identifier_State_24 varchar(2),
Other_Provider_Identifier_Issuer_24 varchar(80),
Other_Provider_Identifier_25 varchar(20),
Other_Provider_Identifier_Type_Code_25 varchar(2),
Other_Provider_Identifier_State_25 varchar(2),
Other_Provider_Identifier_Issuer_25 varchar(80),
Other_Provider_Identifier_26 varchar(20),
Other_Provider_Identifier_Type_Code_26 varchar(2),
Other_Provider_Identifier_State_26 varchar(2),
Other_Provider_Identifier_Issuer_26 varchar(80),
Other_Provider_Identifier_27 varchar(20),
Other_Provider_Identifier_Type_Code_27 varchar(2),
Other_Provider_Identifier_State_27 varchar(2),
Other_Provider_Identifier_Issuer_27 varchar(80),
Other_Provider_Identifier_28 varchar(20),
Other_Provider_Identifier_Type_Code_28 varchar(2),
Other_Provider_Identifier_State_28 varchar(2),
Other_Provider_Identifier_Issuer_28 varchar(80),
Other_Provider_Identifier_29 varchar(20),
Other_Provider_Identifier_Type_Code_29 varchar(2),
Other_Provider_Identifier_State_29 varchar(2),
Other_Provider_Identifier_Issuer_29 varchar(80),
Other_Provider_Identifier_30 varchar(20),
Other_Provider_Identifier_Type_Code_30 varchar(2),
Other_Provider_Identifier_State_30 varchar(2),
Other_Provider_Identifier_Issuer_30 varchar(80),
Other_Provider_Identifier_31 varchar(20),
Other_Provider_Identifier_Type_Code_31 varchar(2),
Other_Provider_Identifier_State_31 varchar(2),
Other_Provider_Identifier_Issuer_31 varchar(80),
Other_Provider_Identifier_32 varchar(20),
Other_Provider_Identifier_Type_Code_32 varchar(2),
Other_Provider_Identifier_State_32 varchar(2),
Other_Provider_Identifier_Issuer_32 varchar(80),
Other_Provider_Identifier_33 varchar(20),
Other_Provider_Identifier_Type_Code_33 varchar(2),
Other_Provider_Identifier_State_33 varchar(2),
Other_Provider_Identifier_Issuer_33 varchar(80),
Other_Provider_Identifier_34 varchar(20),
Other_Provider_Identifier_Type_Code_34 varchar(2),
Other_Provider_Identifier_State_34 varchar(2),
Other_Provider_Identifier_Issuer_34 varchar(80),
Other_Provider_Identifier_35 varchar(20),
Other_Provider_Identifier_Type_Code_35 varchar(2),
Other_Provider_Identifier_State_35 varchar(2),
Other_Provider_Identifier_Issuer_35 varchar(80),
Other_Provider_Identifier_36 varchar(20),
Other_Provider_Identifier_Type_Code_36 varchar(2),
Other_Provider_Identifier_State_36 varchar(2),
Other_Provider_Identifier_Issuer_36 varchar(80),
Other_Provider_Identifier_37 varchar(20),
Other_Provider_Identifier_Type_Code_37 varchar(2),
Other_Provider_Identifier_State_37 varchar(2),
Other_Provider_Identifier_Issuer_37 varchar(80),
Other_Provider_Identifier_38 varchar(20),
Other_Provider_Identifier_Type_Code_38 varchar(2),
Other_Provider_Identifier_State_38 varchar(2),
Other_Provider_Identifier_Issuer_38 varchar(80),
Other_Provider_Identifier_39 varchar(20),
Other_Provider_Identifier_Type_Code_39 varchar(2),
Other_Provider_Identifier_State_39 varchar(2),
Other_Provider_Identifier_Issuer_39 varchar(80),
Other_Provider_Identifier_40 varchar(20),
Other_Provider_Identifier_Type_Code_40 varchar(2),
Other_Provider_Identifier_State_40 varchar(2),
Other_Provider_Identifier_Issuer_40 varchar(80),
Other_Provider_Identifier_41 varchar(20),
Other_Provider_Identifier_Type_Code_41 varchar(2),
Other_Provider_Identifier_State_41 varchar(2),
Other_Provider_Identifier_Issuer_41 varchar(80),
Other_Provider_Identifier_42 varchar(20),
Other_Provider_Identifier_Type_Code_42 varchar(2),
Other_Provider_Identifier_State_42 varchar(2),
Other_Provider_Identifier_Issuer_42 varchar(80),
Other_Provider_Identifier_43 varchar(20),
Other_Provider_Identifier_Type_Code_43 varchar(2),
Other_Provider_Identifier_State_43 varchar(2),
Other_Provider_Identifier_Issuer_43 varchar(80),
Other_Provider_Identifier_44 varchar(20),
Other_Provider_Identifier_Type_Code_44 varchar(2),
Other_Provider_Identifier_State_44 varchar(2),
Other_Provider_Identifier_Issuer_44 varchar(80),
Other_Provider_Identifier_45 varchar(20),
Other_Provider_Identifier_Type_Code_45 varchar(2),
Other_Provider_Identifier_State_45 varchar(2),
Other_Provider_Identifier_Issuer_45 varchar(80),
Other_Provider_Identifier_46 varchar(20),
Other_Provider_Identifier_Type_Code_46 varchar(2),
Other_Provider_Identifier_State_46 varchar(2),
Other_Provider_Identifier_Issuer_46 varchar(80),
Other_Provider_Identifier_47 varchar(20),
Other_Provider_Identifier_Type_Code_47 varchar(2),
Other_Provider_Identifier_State_47 varchar(2),
Other_Provider_Identifier_Issuer_47 varchar(80),
Other_Provider_Identifier_48 varchar(20),
Other_Provider_Identifier_Type_Code_48 varchar(2),
Other_Provider_Identifier_State_48 varchar(2),
Other_Provider_Identifier_Issuer_48 varchar(80),
Other_Provider_Identifier_49 varchar(20),
Other_Provider_Identifier_Type_Code_49 varchar(2),
Other_Provider_Identifier_State_49 varchar(2),
Other_Provider_Identifier_Issuer_49 varchar(80),
Other_Provider_Identifier_50 varchar(20),
Other_Provider_Identifier_Type_Code_50 varchar(2),
Other_Provider_Identifier_State_50 varchar(2),
Other_Provider_Identifier_Issuer_50 varchar(80),
Is_Sole_Proprietor varchar(1),
Is_Organization_Subpart varchar(1),
Parent_Organization_LBN varchar(70),
Parent_Organization_TIN varchar(9),
Authorized_Official_Name_Prefix_Text varchar(5),
Authorized_Official_Name_Suffix_Text varchar(5),
Authorized_Official_Credential_Text varchar(20),
Healthcare_Provider_Taxonomy_Group_1 varchar(70),
Healthcare_Provider_Taxonomy_Group_2 varchar(70),
Healthcare_Provider_Taxonomy_Group_3 varchar(70),
Healthcare_Provider_Taxonomy_Group_4 varchar(70),
Healthcare_Provider_Taxonomy_Group_5 varchar(70),
Healthcare_Provider_Taxonomy_Group_6 varchar(70),
Healthcare_Provider_Taxonomy_Group_7 varchar(70),
Healthcare_Provider_Taxonomy_Group_8 varchar(70),
Healthcare_Provider_Taxonomy_Group_9 varchar(70),
Healthcare_Provider_Taxonomy_Group_10 varchar(70),
Healthcare_Provider_Taxonomy_Group_11 varchar(70),
Healthcare_Provider_Taxonomy_Group_12 varchar(70),
Healthcare_Provider_Taxonomy_Group_13 varchar(70),
Healthcare_Provider_Taxonomy_Group_14 varchar(70),
Healthcare_Provider_Taxonomy_Group_15 varchar(70)
);
DROP TABLE IF EXISTS taxonomy;
CREATE TABLE taxonomy
(
Code varchar(10),
Grouping varchar,
Classification varchar,
Specialization varchar,
Definition text,
Notes text
);
COPY npi
FROM '$3'
WITH CSV HEADER
DELIMITER AS ','
NULL AS '';
COPY taxonomy
FROM '$4'
WITH CSV HEADER
DELIMITER AS E'\t'
EOF