How to Open the Medicare Hospital Service Area File in Excel

The Medicare Hospital Service Area File is sent as a .txt file and not as a .xls file. Users will need to use the text import tool and provide Excel with information on how the file should be imported. The file cannot simply be renamed with an .xls extension.  Below are some general instructions on how to open the Hospital Service Area File in Excel. These instructions are based on Excel 2016, so users of different versions will need to modify the process.

1)      It will be helpful to save the Hospital Service Area File in an easily accessible location such as your Desktop. You will also need the Record Layout that was provided with your data.

2)      Open Excel and click on the “Data” Ribbon at the top.

3)      Next, click “From Text” in the “Get External Data” section.

4)      Select and open the Hospital Service Area File from the location where you saved it on your computer.

5)      The Text Import Wizard box will now appear and will walk you through three steps.

Step 1: Select “Fixed width” as the Original data type. Click “Next.”

Step 2: Using the record layout that came with your data, insert break lines to indicate variable positions. For example, according to the record layout, the first variable is “Medicare Provider Number” and it is 6 bytes. You will insert a break line at position 6. The second variable is “Zip Code of Residence,” it is 5 bytes and ends at position 11. You will insert a break line at position 11. Continue for other variables and click “Next.”

It is important to check the record layout that came with the year of data you are using.

Step 3: Assign the Data Format for each variable.  We suggesting assigning the first two variables, “Medicare Provider Number” and “Zip Code of Residence” to “text,” otherwise any leading 0 will be dropped. The remaining numerical variables can be assigned to “general.” Click “Finish.”

6)      To finish the import process, click “OK.” Excel gives you the option of importing the data into a different worksheet, if needed. This make take several minutes.

7)      The data should now appear in Excel. You can now insert column titles based on the record layout. You may need to widen the columns to see the full length of values.

Note: The 2017 text file contains more data points than Excel can fit. Excel will give you an option to import as much data as will fit in one worksheet. Click "Ok". Follow the same procedure in another worksheet to download the remaining data points. In the Text Import Wizard, enter the starting row number in "Start Import Row" box.