Open CSV in Excel

From IMSMA Wiki
Revision as of 13:39, 5 April 2015 by Alnaucler (talk | contribs)
Jump to: navigation, search
Warning.jpg When you open the CSV file in Excel, Excel will automatically interpret data like 1-10 as 10-Jan.

Therefore check carefully the result in Excel before sharing the export result with others. This page describes a word around for this Excel behaviour.

  1. Export Search Result.
  2. Open a new Excel file.
  3. Open the CSV file with a text editor e.g. Notepad++.
  4. Csv copy text.png

    Copy all text

  5. Copy all rows with Ctrl+A.
  6. Switch back to Excel and click in cell A1.
  7. Open the Text import wizard by one of following methods:
    Csv paste method.png

    Paste method

    Paste method
    1. Click on lower part of the Paste button.
    2. Select Use Text Import Wizard in the drop-down menu.
    Text to Columns method
    1. Paste into Excel with Ctrl+V.
    2. Select column A.
    3. Csv text to columns method.png

      Text to Columns method

    4. Use function Text to columns on Data ribbon.
  8. The wizard starts.
  9. Csv wizard1.png

    The first panel

  10. Click Next.
  11. Csv wizard2.png

    The second panel

  12. Change Delimiters to Comma.
  13. Click Next.
  14. Csv wizard3.png

    The third panel

  15. Change Column data format to Text and verify that the format is set to Text for the columns that Excel otherwise will change to the wrong data type or data format.
  16. Click Finish.
  17. Save as xlsx.
  18. Close and open the file in order to verify that the data still has the correct format.