TycheHowTo:ImportDataIntoMicrosoftExcel

Miami-Dade County's open data site

Background

This document presents a process that is similar to a guide that demonstrates importing and using data within Google Sheets. The scenario background is presented here. The concise version is that this document will demonstrate how to import a dataset from the Miami-Dade County open data portal into Microsoft Excel and then utilize this dataset to conduct some illustrative analysis.

Our task is:

1. export the data from the Miami-Dade open data system
2. import the data into the spreadsheet Microsoft Excel
3. start some basic tests on the data

For step #1 of this task the reader can use the Exporting the data section of the document that describes Google Sheets utilization. The Exporting the data process is the same for both downstream Google Sheets and Microsoft Excel usage.

Importing the data into Microsoft Excel

After you download the 2025 procurement data from Miami-Dade you can view the data in a text editor. In the example below we have opened the file in Notepad. The highlighted text is the first row with the data or field descriptions; the text below that is the actual data:

Viewing the open data .csv file in a text editor

You are now ready to import the data into Microsoft Excel. You can place the data in its own folder in Windows:

The following explanation makes use of Microsoft Excel, Microsoft 365 Apps for business, version 2507. Similar functionality exists in other versions of Excel.

There are two methods for importing csv data into Microsoft Excel:

1. Double click the csv and, if .csv file formats are associated with Excel on your computer, the file will open in Excel
2. Perform a formal import process within Excel that allows you to transform the data for your needs, and to handle various issues that may occur. These issues could include not recognizing a separator character (e.g. if the import file uses "|", ";" or similar as a separator), data types not being handled well (e.g. a ZIPCode of "01234" is converted to numeric 1234), and other issues.

We will attempt method #1 and see how well it works.

In the folder above we double-click on the .csv file which opens up Excel. We are first presented with a screen that provides an option to convert or not perform data conversions.

MS Excel provides an option to convert on import

We will choose "Convert".

Data in Excel after importation

The data types or formats of the different fields of data look appropriate. Numeric fields are right-justified and are of format General. PO_DATE's format is Date. Text fields are left-justified and General. If you read the companion article that speaks to the importation process in Google Sheets you will note that we commented on the ZIPCode field and a record for New Jersey that has a ZIP that starts with 0. With the "Convert" option abov e we have retained the preceding 0 in the data.

We can do some basic configuration on the data to make it easier to use:

• Bold the top row with the column names
• Freeze the top row - View > Freeze Panes > Freeze Top Row
• Add a filter - select all data, Data > Filter

Lastly, we want to move the data into Excel format. Save a copy of the data as MiamiDate_PurchaseOrders_2025.xlsx, Excel format.

Introductory Data Exploration

Sorting fields in Excel using a Filter

Purchase orders sorted by descending cost

All line items associated with purchase order 5883

Testing with pivot tables

Create pivot table

Excel pivot table options

Excel Pivot suppliers by Sum Amount

Sorting supplier list by total amount, descending

Top suppliers in a formatted table

Purchase Order Value by Month