How to Open ARFF Files in Excel

Step 1: Open the ARFF File in Notepad

1. Locate your ARFF file on your computer
2. Right-click the file
3. Select “Open With” → “Notepad” (Windows) or “TextEdit” (Mac)
4. The file opens showing all the text including headers and data

Step 2: Identify the Column Names

Look at the @ATTRIBUTE lines. These are your Excel column headers:

@ATTRIBUTE customer_id NUMERIC
@ATTRIBUTE name STRING
@ATTRIBUTE age NUMERIC

Your Excel columns will be: customer_id, name, age

Step 3: Select and Copy Data Only

1. Find the line that says @DATA
2. Click at the start of the line after @DATA
3. Select all remaining lines (your actual data)
4. Press Ctrl+C to copy
5. Do NOT include the @DATA line itself or any header lines

Step 4: Create Excel Spreadsheet

1. Open Microsoft Excel
2. In cell A1, type your first column header (from @ATTRIBUTE)
3. In cells B1, C1, etc., type the remaining column headers

Example headers to type: customer_id, name, age, department, salary

Step 5: Paste Data

1. Click on cell A2 (first data cell below headers)
2. Press Ctrl+V to paste the copied data
3. Excel automatically separates the comma-delimited values into columns

Step 6: Clean Up

• Remove any quote marks around text values (if present)
• Check that each row’s data aligns with correct columns
• Delete any extra blank rows at the bottom
• Format numeric columns appropriately (currency, decimals, etc.)
• Save as Excel file (.xlsx)

Pros: Quick, no software needed, works for small files (100-1000 rows)

Cons: Manual and error-prone for large files, time-consuming for datasets with many rows

Step 1: Prepare ARFF File as Text

1. Open the ARFF file in Notepad (right-click → Open With → Notepad)
2. Select all content (Ctrl+A)
3. Copy (Ctrl+C)

Step 2: Create Temporary Text File

1. Create a new Notepad document
2. Paste the ARFF content (Ctrl+V)
3. Save as “temp_data.txt”

Step 3: Open Text File in Excel

1. In Excel, click File → Open
2. Change file type filter to “All Files (*.*)”
3. Navigate to and select “temp_data.txt”
4. Click Open

Step 4: Use Text Import Wizard

Excel’s Text Import Wizard opens automatically:

1. Step 1: Select “Delimited” (not Fixed Width)
2. Step 2: Check “Comma” as the delimiter
3. Step 3: Click “Finish” to complete import

The data will import with ARFF headers mixed in. You’ll need to clean this up in the next step.

Step 5: Clean ARFF Headers

1. Delete all rows containing @RELATION, @ATTRIBUTE, and @DATA text
2. Replace the header row with proper column names
3. Example proper headers: customer_id, name, age, department, salary

Step 6: Save as CSV

1. File → Save As
2. Change “Save as type” to “CSV (Comma delimited) (*.csv)”
3. Choose filename and location
4. Click Save

Pros: No external software needed, uses only Excel

Cons: Requires manual cleanup of ARFF headers, multi-step process

Step 1: Open ARFF File in Notepad

1. Right-click ARFF file → Open With → Notepad

Step 2: Delete ARFF Header Section

1. Locate the @DATA line
2. Select and delete everything above @DATA (all @RELATION and @ATTRIBUTE lines)
3. Also delete the @DATA line itself
4. Keep only the pure data rows (the comma-separated values)

Your file should now show only data like:

1,"John Smith",35,Sales,65000
2,"Sarah Johnson",28,Marketing,52000
3,"Mike Chen",42,IT,75000

Step 3: Save as CSV

1. File → Save As
2. Change filename extension to “.csv” (e.g., “data.csv”)
3. Change “Save as type” to “All Files (*.*)”
4. Click Save

Step 4: Open CSV in Excel

1. Open Excel
2. File → Open
3. Select your .csv file
4. Click Open
5. Excel imports the data with automatic column separation

Step 5: Add Headers (If Missing)

1. Insert a new row at the top (Right-click row 1 → Insert)
2. In the first row, type column names from the original ARFF file
3. Save the Excel file

Pros: Fastest method, creates reusable CSV file, only text editor needed

Cons: Requires careful deletion to avoid data loss, need to track column names from original ARFF

Step 1: Prepare CSV from ARFF

First convert ARFF to CSV using Method 3 above. Power Query works with CSV files, not ARFF directly.

Step 2: Open Power Query

1. In Excel, go to Data tab
2. Click “From Text/CSV” (or “Get Data” → “From File” → “From Text/CSV”)

Step 3: Select CSV File

1. Browse to your CSV file (converted from ARFF)
2. Click Open
3. Power Query Preview opens showing your data

Step 4: Configure Import Settings

1. Verify delimiter is set to “Comma”
2. Check data preview looks correct
3. Select data type for each column if needed
4. Click “Load” to import into Excel

Step 5: Format in Excel

Once loaded, format columns as needed:

• Apply currency formatting to salary columns
• Set date formats for date columns
• Apply number formatting (decimals, thousands separator)
• Adjust column widths for readability

Pros: Professional import with formatting options, can refresh data easily, suitable for regular updates

Cons: Requires Excel 2016+, additional learning curve

Issue 1: Data in One Column Only

Problem: All data appears in column A instead of separated into columns.

Solution:

1. Select the column containing all data
2. Go to Data tab → “Text to Columns”
3. Choose “Delimited”
4. Select “Comma” as delimiter
5. Click Finish

Excel will now separate the data into proper columns.

Issue 2: Missing Values Display as Question Marks

Problem: ARFF uses ? to represent missing/empty values.

Solution:

1. Select the column with question marks
2. Press Ctrl+H (Find & Replace)
3. Find: ?
4. Replace with: (leave blank) or type “N/A”
5. Click “Replace All”

Issue 3: Numbers with Leading Zeros Lose Zeros

Problem: Excel converts “0123” to “123”.

Solution:

1. Select the affected column
2. Right-click → Format Cells
3. Select “Text” category
4. Click OK

For existing data, format column as Text BEFORE pasting data to preserve leading zeros.

Issue 4: Text Surrounded by Quotation Marks

Problem: Text values show quotes: “John Smith” instead of John Smith

Solution:

1. Select column with quoted text
2. Press Ctrl+H
3. Find: " (one quotation mark)
4. Replace with: (leave blank)
5. Click “Replace All”

Issue 5: Dates Not Recognized

Problem: Date values stored as text, not as date format.

Solution:

1. Select the date column
2. Data tab → “Text to Columns”
3. Choose “Delimited” → Next
4. In Step 3, select the date column and set “Column data format” to “Date”
5. Choose date format matching your data
6. Click Finish

Issue 6: Special Characters Display Incorrectly

Problem: Accented characters or symbols show garbled.

Solution:

1. When importing CSV, use Data → “From Text/CSV”
2. In import dialog, look for “File Origin” dropdown
3. Change from “1252” to “65001 (UTF-8)”
4. Click Load

Issue 7: Categorical Data with Commas

Problem: Categories contain commas, breaking column separation (e.g., “Smith, John”)

Solution:

In ARFF, values with commas are enclosed in quotes. Excel should handle this automatically during import. If not:

1. Open CSV in Notepad
2. Find values with commas inside quotes: “Smith, John”
3. Replace commas inside quotes with a different character (e.g., semicolon)
4. Save and re-import into Excel