Skip to main content
MediaBeacon University

Opening CSV files with Excel's Data Import Function

Step by step sequence to importing CSV in Excel.

Our recommendation is to NOT open a CSV file directly in Microsoft Excel, but to use Data Import. This ensures data remains consistent.

Data Import of a CSV file In Excel

Instructions consistent with Microsoft Excel for Mac, version 16.18 (181014), but is general for other versions.

  1. Create a new Workbook: File > New Workbook
    exceldataimport-01.png
  2. Import file: Data Tab > From Text
    exceldataimport-02.png
  3. Choose File
  4. Import Text Wizard: Step 1 of 3
    exceldataimport-03.png
    1. Choose: "Delimited"
    2. Start Import at row: "1"
    3. Choose: "File Origin: Unicode UTF-8"
      1. Other versions may have a slightly slightly different label, use the one with 'UTF-8' in it.
      2. Note: This is the correct setting for files that are assured to be in UTF-8, such as files generated by MediaBeacon. Other encodings require this setting to match the file's encoding.
    4. Click "Next >"
  5. Import Text Wizard: Step 2 of 3
    exceldataimport-04.png
    1. Delimiters, only: "Comma"
      1. If file is in tab delimited format, use the Tab Setting
    2. "Consecutive Delimiters as one": Disabled
    3. Text Qualifier to: " (double quote, this is the default)
    4. Click "Next >"
  6. Import Text Wizard: Step 2 of 3
    exceldataimport-05.png
    1. This screen shows a preview of the import, and the columns will all be marked as "General". This setting leads to Date values getting altered.
    2. This step requires selecting the headers (not the cells) in the Data Preview, and setting them all to "Text"
      1. Easiest method is to:
      2. Click the leftmost header. The area below it will select, but be careful, this is NOT the same as selecting the area itself, you must click the header which does not display a highlight.
      3. Drag the horizontal scrollbar (if present) all the way to the right and SHIFT-click the rightmost header
    3. Click "Text" under Column Data Format.
    4. Ensure that EVERY column lists "Text" in the header.
      exceldataimport-06.png
    5. Click Finish
  7. Import Data Dialog
    exceldataimport-07.png
    1. Click Properties…
  8. External Data Range Properties Dialog
    exceldataimport-08.png
    1. Turn OFF Save query definition...
    2. Click "OK"
  9. Import Data Dialog
    1. Existing Sheet: "=Sheet1!$A$1"
      exceldataimport-07.png
    2. Click "OK"
    3. Data from CSV is now imported.
      exceldataimport-09.png
  10. Save as an Excel file.
  • Was this article helpful?