How to Open DAT Files in Excel?

by

A file with the .dat extension is referred to as a DAT file.

DAT files commonly contain binary text data and may be used to store large amounts of data in a table/database form, just like we do in Excel.

As an example, take a look at the following information that has been stored in a DAT file as plain text.

DAT file example

The above data is for the members of a hiking club. The first line contains the column headings separated by a comma.

Successive lines contain the actual data with individual column entries separated by a comma.

For large amounts of data like the one above, a DAT file is excellent for storing or transferring data as the file size is relatively small.

As an Excel user, you may need to open this DAT file in Excel so that you may manipulate the data even further. Maybe you need to plot a chart. Or you may want to sort the data. Either way, opening the DAT file in Excel comes first.

So in this article, I will show you different ways of opening this DAT file in Excel. This file will be available to download as an example to follow along with this article.

Method 1: Open DAT File in Excel Using the File Browser

In this method, we will open a DAT file directly in Excel.

As an example, we will use the DAT file shown below, which contains the data for members of a hiking club.

DAT file example

Note that the column entries are separated by a comma. This is known as a delimiter used by Excel to insert the data into their respective columns properly.

Below are the steps to open a DAT file in Excel:

  1. Open a new Excel file and go to the File tab.
  1. A new panel window will appear as shown.
file tab backend opens up
  1. Click on the Browse button.
  1. The file explorer window will open as shown.
file explorer window
  1. From the file filter drop-down menu, select the All Files option.
select all files option
  1. Locate the DAT file. (In my case, it is located on the desktop with the name Club_Members.dat)
locate the dat file
  1. Select the file and click on Open.
open the file
  1. The Text Import Wizard window will open as shown.
text Import Wizard Window
  1. A preview of the data contained in the file will appear in the preview panel below.
preview panel in the text import wizard
  1.  Under the Original Data Type section, select the Delimited option.
select delimited
  1.  Check the My data has headers checkbox. (If your data has no headers, keep this option unchecked. To check whether the data has headers or not, take a look at the data preview that is shown in the preview panel below)
check my data as headers option
  1.  Click on the Next button.
  1.  The next window will appear as shown.
step two of three in the text report wizard
  1.  Under the Delimiters section, select the comma option. Uncheck all other options.
select the comma option
  1.  In the data preview panel, you will see that Excel has recognized the data and split it into their respective columns accordingly based on the comma which was the delimiter.
data preview would separate the columns
  1.  Click on Next.
click on next
  1.  The following window will open, which is the last step of the Text Import Wizard.
step three of text import wizard

You will see that the first column is highlighted in black. This is the column that is selected by default.

You can select individual columns by clicking on them. The data in each column can be formatted as either General, Text or Date. Alternatively, the column may be skipped entirely to be not included in Excel.

These options appear under the Column data format section. For this example, we will keep the default General format.

keep general default data format
  1.  Click on Finish.
  1.  The end result will be as shown below.
DAT file opened in Excel

As you can see, the data contained in the DAT file has now been inserted into Excel columns and rows based on the criteria that was defined in the file import wizard.

So in this method, we have seen how to open DAT files in Excel by simply opening the file through the file browser.

Also read: How to Open XML Files in Excel?

Method 2: Open DAT File in Excel Using Notepad and Copying Data

In this method, we will look at an indirect approach to opening a DAT file in Excel.

We will first open the DAT file in Notepad and then copy the data in Excel.

As an example, we will use the same DAT file that we have used in the previous method containing the data for members of a hiking club.

DAT file example
  1. Open the Notepad application on your windows pc.
notepad application opens
  1. From the Menu bar, select the File option.
click the file option in the menu
  1. From the File Menu, click on Open.
  1. The file explorer window will open as shown.
  1. From the file filter drop-down menu, select the All Files option.
select all files option
  1. Locate the DAT file. (In my case, it is located on the desktop with the name Club_Members.dat)
locate the DAT file
  1. Select the file and click on Open.
  1. The DAT file will open in Notepad and the data in the file will appear as shown.
DAT file opens in notepad
  1. Select the entire data by pressing ‘Ctrl + A’ on your keyboard.
select the entire data in the notepad
  1.  After the data has been selected, press ‘Ctrl + C’ on your keyboard to copy the data.
  2.  Open a new Excel worksheet.
  3.  Select cell A1 and press ‘Ctrl + V’ on your keyboard to paste the copied data.
  4.  The pasted data will appear as shown. As you can see, the data is not properly inserted into the respective columns.
copy the data from notepad to Excel
  1.  Click on the paste options iconthat appears at the bottom.
click on the paste options icon
  1.  The following menu will appear.
  1.  Select the Use Text Import Wizard option.
select use text import wizard
  1.  The Text Import Wizard window will open as shown.
text import wizard

At this point, we have reached where we did in Step 8 of Method 1.

  1.  From this point onwards, follow Steps 9 to 18 of Method 1.
  2.  The end result will look like the one shown below.
DAT file data in Excel

As you can see, the data contained in the DAT file has now been inserted into Excel columns and rows based on the criteria that were defined in the file import wizard.

So in this method, we have seen how to open DAT files in Excel by first opening the file in Notepad and then copying the data into Excel.

In this article, we have seen two methods of opening a DAT file and inserting the data in Excel.

Both methods use the Text Import Wizard to insert the data in Excel.

Method 1 is the most common way of opening DAT files directly in Excel. If, for some reason, the DAT file is not opening directly in Excel, use Method 2 to open the DAT file in Notepad and then copy the data over in Excel.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment